So, looking at the WHERE clause, you would then want to be looking for
opportunities to restrict the set of rows coming from the T11 alias. Fou=
r
such opportunities present themselves:
1. joining to T11 from the alias T5 (i.e. S_ACCNT_POSTN table)
via the join-predicates "T11.PR_POSTN_ID =3D T5.POSITION_ID "
and "T11.ROW_ID =3D T5.OU_EXT_ID ". However, since there are no
filtering/search predicates against the T5 alias at all, this
would only work if the S_ACCNT_POSTN table is really small
2. joining to T11 from the alias T3 (i.e. S_POSTN table) via the
join-predicate "T11.PR_POSTN_ID =3D T3.ROW_ID ". However, just
as with the T5 alias, there are no filtering/search predicates
on the T3 alias so this would only work if the S_POSTN table
were really small
3. using the filtering/search predicate on the T11.CUST_STAT_CD
column, which would work well if the column were indexed and
the value of "Active " is relatively unpopular
4. using the OR 'd group of filtering/search predicates on the
T11.LOC column
Options #1, #2, and #3 should be relatively easy to verify, I think?
Option #4 is the complicated one, and it presents another damning argumen=
t
against continued use of the rule-based optimizer (RBO). In this situati=
on
(i.e. multiple binding filtering/search predicates against the same colum=
n
glued together with OR clauses), the RBO cannot use an index, so it doesn=
't.
However, if the cost-based optimizer (CBO) were in use, then the
functionality exposed by the USE_CONCAT hint (MetaLink note #17214.1) wou=
ld
enable the CBO to transparently transform the query to expand the OR 'ed
predicates into multiple UNION 'd query blocks. So in effect, the query
which is currently written as:
SELECT ...
FROM ...
WHERE ...
AND (T11.LOC =3D xxx OR T11.LOC =3D yyy OR T11.LOC like '%zzz ' ..=
.)
AND ...
Could be transparently transformed to operate as:
SELECT ...
FROM ...
WHERE ...
AND T11.LOC =3D xxx
AND ...
UNION
SELECT ...
FROM ...
WHERE ...
AND T11.LOC =3D yyy
AND ...
UNION
SELECT ...
FROM ...
WHERE ...
AND T11.LOC like '%zzz '
AND ...
UNION
...
And, if the CBO didn 't choose to do this transparently, it could be easil=
y
forced using the USE_CONCAT hint.
The possible advantage of this expansion is that, if the T11.LOC column i=
s
indexed, then each UNION 'd subquery can utilize that index as appropriate=
.
However, since you are using RBO and the CBO-based "USE_CONCAT " hint is n=
ot
available for use, then your only option is test an explicit rewrite of t=
he
query to perform the expansion. If the query cannot be explicitly
rewritten, then the story ends right there.
Not to fret, as there is a very good chance that this expansion will not
help, as it is possible that a FULL table scan of the T11 alias (i.e.
S_ORG_EXT table) is appropriate if the data values used in the predicates
are very "popular ". Also, it is likely that the predicate of "T11.LOC LI=
KE
'%COUNCIL ' " would not be able to use any index on T11.LOC anyway, so
expansion of the OR clauses would result in at least multiple FULL table
scans of the S_ORG_EXT table anyway, but you ought to test it to find out=
.
Anyway, I 'd advise investigating each of these four options presented abo=
ve.
Hope this helps...
-Tim
on 4/30/04 1:51 PM, David Green at thump@(protected) wrote:
> Does anyone have any general recommendations for how to tune this query=
or
> improve how it does what it does in 8174 in RBO mode?
> SELECT
> T5.POSITION_ID,
> T3.ROW_ID,
> T11.PR_POSTN_ID,
> T11.ASGN_USR_EXCLD_FLG,
> ...
> ...(list of columns edited for brevity)...
> ...
> T5.ASGN_MANL_FLG,
> T3.NAME
> FROM
> SIEBEL.S_ADDR_ORG T1,
> SIEBEL.S_ORG_EXT T2,
> SIEBEL.S_POSTN T3,
> SIEBEL.S_ORG_SYN T4,
> SIEBEL.S_ACCNT_POSTN T5,
> SIEBEL.S_ASGN_GRP T6,
> SIEBEL.S_ORG_INT T7,
> SIEBEL.S_INDUST T8,
> SIEBEL.S_EMPLOYEE T9,
> SIEBEL.S_ORG_EXT_X T10,
> SIEBEL.S_ORG_EXT T11
> WHERE
> T11.BU_ID =3D T7.ROW_ID (+) AND
> T11.PAR_OU_ID =3D T2.ROW_ID (+) AND
> T11.ROW_ID =3D T10.PAR_ROW_ID (+) AND
> T11.PR_INDUST_ID =3D T8.ROW_ID (+) AND
> T11.PR_SYN_ID =3D T4.ROW_ID (+) AND
> T11.PR_ADDR_ID =3D T1.ROW_ID (+) AND
> T11.PR_TERR_ID =3D T6.ROW_ID (+) AND
> T11.PR_POSTN_ID =3D T3.ROW_ID AND
> T11.PR_POSTN_ID =3D T5.POSITION_ID AND T11.ROW_ID =3D T5.OU_EXT_ID =
AND
> T3.PR_EMP_ID =3D T9.ROW_ID (+) AND
> ((T11.LOC =3D 'WGQIWG ' OR T11.LOC =3D 'WGAIWG ' OR T11.LOC =3D 'WGFI=
WG ' OR
> T11.LOC LIKE '%COUNCIL ') AND T11.CUST_STAT_CD =3D 'Active ')
> ORDER BY
> T11.NAME, T11.LOC
>=20
> 16 rows selected
>=20
> Execution Plan
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
> 0 SELECT STATEMENT Optimizer=3DRULE
> 1 0 NESTED LOOPS (OUTER)
> 2 1 NESTED LOOPS
> 3 2 NESTED LOOPS (OUTER)
> 4 3 NESTED LOOPS (OUTER)
> 5 4 NESTED LOOPS (OUTER)
> 6 5 NESTED LOOPS
> 7 6 NESTED LOOPS (OUTER)
> 8 7 NESTED LOOPS (OUTER)
> 9 8 NESTED LOOPS (OUTER)
> 10 9 NESTED LOOPS (OUTER)
> 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_E=
XT '
> 12 11 INDEX (FULL SCAN) OF 'S_ORG_EXT_U1 '(UNI=
QUE)
> 13 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_INDUS=
T '
> 14 13 INDEX (UNIQUE SCAN) OF 'S_INDUST_P1 '(UN=
IQUE)
> 15 9 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_INT=
'
> 16 15 INDEX (UNIQUE SCAN) OF 'S_ORG_INT_P1 '(UNI=
QUE)
> 17 8 TABLE ACCESS (BY INDEX ROWID) OF 'S_ASGN_GRP '
> 18 17 INDEX (UNIQUE SCAN) OF 'S_ASGN_GRP_P1 ' (UNI=
QUE)
> 19 7 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_SYN '
> 20 19 INDEX (UNIQUE SCAN) OF 'S_ORG_SYN_P1 ' (UNIQUE=
)
> 21 6 TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN '
> 22 21 INDEX (UNIQUE SCAN) OF 'S_POSTN_P1 ' (UNIQUE)
> 23 5 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE '
> 24 23 INDEX (UNIQUE SCAN) OF 'S_EMPLOYEE_P1 ' (UNIQUE)
> 25 4 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT '
> 26 25 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_P1 ' (UNIQUE)
> 27 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR_ORG '
> 28 27 INDEX (UNIQUE SCAN) OF 'S_ADDR_ORG_P1 ' (UNIQUE)
> 29 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ACCNT_POSTN '
> 30 29 INDEX (RANGE SCAN) OF 'S_ACCNT_POSTN_U1 ' (UNIQUE)
> 31 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT_X '
> 32 31 INDEX (RANGE SCAN) OF 'S_ORG_EXT_X_U1 ' (UNIQUE)
> Statistics
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
> 0 recursive calls
> 0 db block gets
> 731716 consistent gets
> 5505 physical reads
> 0 redo size
> 12214 bytes sent via SQL*Net to client
> 1468 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 16 rows processed
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
