Hi Jonathan,
I try it on 10g, it use the HASH JOIN RIGHT OUTER execute plan. It is
faster than 9i.
Thanks
Jacky
PARSING IN CURSOR #4 len=1112 dep=0 uid=54 oct=3 lid=54 tim=81031072111
hv=1496394931 ad='2104a8b8'
SELECT * FROM (SELECT DISTINCT
B1PERMIT.B1_PER_ID1, B1PERMIT.B1_PER_ID2, B1PERMIT.B1_PER_ID3,
B1_ALT_ID,
B1PERMIT.B1_PER_GROUP, B1PERMIT.B1_PER_TYPE,
B1PERMIT.B1_PER_SUB_TYPE,B1PERMIT.B1_PER_CATEGORY,
B1PERMIT.B1_FILE_DD, B1PERMIT.B1_SPECIAL_TEXT,
B3ADDRES.B1_HSE_NBR_START,B3ADDRES.B1_HSE_NBR_END,
B3ADDRES.B1_HSE_FRAC_NBR_START,
B3ADDRES.B1_HSE_FRAC_NBR_END,B3ADDRES.B1_STR_DIR,
B3ADDRES.B1_STR_NAME, B3ADDRES.B1_STR_SUFFIX,
B3ADDRES.B1_STR_SUFFIX_DIR,
B3ADDRES.B1_UNIT_START,B3ADDRES.B1_UNIT_END, B3ADDRES.B1_UNIT_TYPE,
B3ADDRES.B1_SITUS_CITY, B3ADDRES.B1_SITUS_STATE, B3ADDRES.B1_SITUS_ZIP
FROM B1PERMIT, B3ADDRES
WHERE B1PERMIT.SERV_PROV_CODE = 'NOLA'
AND B1PERMIT.REC_STATUS = 'A'
AND B1PERMIT.SERV_PROV_CODE = B3ADDRES.SERV_PROV_CODE(+)
AND B1PERMIT.B1_PER_ID1 = B3ADDRES.B1_PER_ID1(+)
AND B1PERMIT.B1_PER_ID2 = B3ADDRES.B1_PER_ID2(+)
AND B1PERMIT.B1_PER_ID3 = B3ADDRES.B1_PER_ID3(+)
AND B1PERMIT.REC_STATUS = B3ADDRES.REC_STATUS(+)
ORDER BY B1PERMIT.B1_FILE_DD DESC, B1PERMIT.B1_PER_ID2 ASC,
B1PERMIT.B1_PER_ID3 ASC
)
WHERE ROWNUM < 101
END OF STMT
PARSE #4:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=81031072107
BINDS #4:
EXEC #4:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=81031072331
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81031072371
FETCH
#4:c=1484375,e=1491862,p=0,cr=62545,cu=0,mis=0,r=1,dep=0,og=2,tim=81032564270
WAIT #4: nam='SQL*Net message from client' ela= 703 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81032565069
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81032565136
FETCH #4:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81032565193
WAIT #4: nam='SQL*Net message from client' ela= 1496238 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81034061471
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81034061593
FETCH #4:c=0,e=109,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81034061666
WAIT #4: nam='SQL*Net message from client' ela= 316855 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81034378575
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81034378694
FETCH #4:c=0,e=95,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81034378753
WAIT #4: nam='SQL*Net message from client' ela= 913538 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81035292341
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81035292444
FETCH #4:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81035292518
WAIT #4: nam='SQL*Net message from client' ela= 267057 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81035559633
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81035559739
FETCH #4:c=0,e=103,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81035559809
WAIT #4: nam='SQL*Net message from client' ela= 264279 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81035824140
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81035824250
FETCH #4:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=2,tim=81035824299
WAIT #4: nam='SQL*Net message from client' ela= 264985 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81036089335
WAIT #4: nam='SQL*Net message to client' ela= 9 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81036089472
FETCH #4:c=0,e=117,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=2,tim=81036089530
WAIT #4: nam='SQL*Net message from client' ela= 180479 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81036270097
STAT #4 id=1 cnt=100 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=62545 pr=0
pw=0 time=1491902 us)'
STAT #4 id=2 cnt=100 pid=1 pos=1 obj=0 op='VIEW (cr=62545 pr=0 pw=0
time=1491890 us)'
STAT #4 id=3 cnt=100 pid=2 pos=1 obj=0 op='SORT UNIQUE STOPKEY
(cr=62545 pr=0 pw=0 time=1491879 us)'
STAT #4 id=4 cnt=315139 pid=3 pos=1 obj=0 op='HASH JOIN RIGHT OUTER
(cr=62545 pr=0 pw=0 time=1520234 us)'
STAT #4 id=5 cnt=41407 pid=4 pos=1 obj=51166 op='TABLE ACCESS BY INDEX
ROWID B3ADDRES (cr=37654 pr=0 pw=0 time=248515 us)'
STAT #4 id=6 cnt=42043 pid=5 pos=1 obj=51167 op='INDEX RANGE SCAN
B3ADDRES_STRNAME_IX (cr=170 pr=0 pw=0 time=42619 us)'
STAT #4 id=7 cnt=312624 pid=4 pos=2 obj=51170 op='TABLE ACCESS BY INDEX
ROWID B1PERMIT (cr=24891 pr=0 pw=0 time=2501055 us)'
STAT #4 id=8 cnt=313182 pid=7 pos=1 obj=51176 op='INDEX RANGE SCAN
B1PERMIT_APPSTATUS_IX (cr=1040 pr=0 pw=0 time=630445 us)'
WAIT #0: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=51176 tim=81036270374
*** 2005-12-06 11:41:40.286
WAIT #0: nam='SQL*Net message from client' ela= 266941222 driver
id=1413697536 #bytes=1 p3=0 obj#=51176 tim=81303211619
Received on Mon Dec 05 2005 - 21:46:47 CST
