Re: questions about cbo explain plan

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:1133315889.038925.79830_at_z14g2000cwz.googlegroups.com...
> Thanks for your answer, it is very good,
>
> Yes, It is not the run text. I just replace the table and column name.
>
> I add the hint nest loop and re-run the sql, could you please give some
> suggestion to tune the sql?
>
> =====================
> PARSING IN CURSOR #1 len=1143 dep=0 uid=42 oct=3 lid=42 tim=4207437406
> hv=2068472713 ad='23d323c4'
> SELECT /*+ USE_NL(T1 T2) */ * FROM (SELECT DISTINCT
> T1.ID1, T1.ID2, T1.ID3
> T2.COL1, T2.COL2
> FROM T1, T2
> WHERE T1.ID1 = T2.ID1(+)
> AND T1.ID2 = T2.ID2(+)
> AND T1.ID3 = T2.ID3(+)
> ORDER BY T1.CREATED_DATE DESC, T1.ID2 ASC, T1.ID3 ASC
> )
> WHERE ROWNUM < 101
> END OF STMT
> PARSE #1:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4207437401
> BINDS #1:
> EXEC #1:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=4207437779
> WAIT #1: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
> FETCH #1:c=0,e=136,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=2,tim=4216708009
> *** 2005-11-30 09:40:28.624
> WAIT #1: nam='SQL*Net message from client' ela= 57922214 p1=1413697536
> p2=1 p3=0
> STAT #1 id=1 cnt=100 pid=0 pos=1 obj=0 op='COUNT STOPKEY '
> STAT #1 id=2 cnt=100 pid=1 pos=1 obj=0 op='VIEW '
> STAT #1 id=3 cnt=100 pid=2 pos=1 obj=0 op='SORT UNIQUE STOPKEY '
> STAT #1 id=4 cnt=291462 pid=3 pos=1 obj=0 op='HASH JOIN OUTER '
> STAT #1 id=5 cnt=289518 pid=4 pos=1 obj=29837 op='TABLE ACCESS BY INDEX
> ROWID T1 '
> STAT #1 id=6 cnt=290076 pid=5 pos=1 obj=29838 op='INDEX RANGE SCAN
> T1_ALTID_IX '
> STAT #1 id=7 cnt=39267 pid=4 pos=2 obj=29848 op='TABLE ACCESS BY INDEX
> ROWID T2 '
> STAT #1 id=8 cnt=39903 pid=7 pos=1 obj=29850 op='INDEX RANGE SCAN T2_PK
> '
>

Your hint is in the wrong place.
Unless you use the 'global hint' format
(9i feature), or include query block names (10g feature) you have to get hints into the block that own the objects being
referenced, hence:

> SELECT * FROM (
> SELECT /*+
> ordered
> use_nl(t2)
> index (t1 {whatever it was})
> index (t2 {whatever it was})
> */
> DISTINCT
> T1.ID1, T1.ID2, T1.ID3
> T2.COL1, T2.COL2
> FROM T1, T2
> WHERE T1.ID1 = T2.ID1(+)
> AND T1.ID2 = T2.ID2(+)
> AND T1.ID3 = T2.ID3(+)
> ORDER BY T1.CREATED_DATE DESC, T1.ID2 ASC, T1.ID3 ASC
> )
> WHERE ROWNUM < 101

I've included two index hints, for which you have to supply the name, as they may be needed to make the CBO match the execution plan of the RBO. In the absence of those hints the CBO might find something else to do that it thinks is cheaper than using those indexes when doing a nested loop.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005