The following is a query thrown to my desk by a developer.
He claimed it took much longer time than it should be.
I did something but no significant changes.
all yr input is greatly appreciated.
select vs.act_site_code, vs.site_name, vs.pn, vs.test_date,
vs.load_date, vs.match_date, vs.billing_code, vs.hours24_flag, vc.code, vc.name, decode(vc.client_type,'E', 'Education', 'B', 'Business') client_type,
vf.litho_code, vf.lname, vf.ssn_id,
vr.test_type rfi,
vm.test_type am,
vl.test_type l,
vw.test_type w,
vi.test_type li,
vt.test_type tw,
va.test_type at,
vb.test_type ob
from wkbus.v_pn_site vs,
wkbus.v_pn_client vc,
wkbus.v_pn_folder_detail vf,
wkbus.v_pn_rfi_detail vr,
wkbus.v_pn_am_detail vm,
wkbus.v_pn_l_detail vl,
wkbus.v_pn_w_detail vw,
wkbus.v_pn_li_detail vi,
wkbus.v_pn_tw_detail vt,
wkbus.v_pn_at_detail va,
wkbus.v_pn_ob_detail vb
where vs.act_site_code = decode(upper(: p_servicecenter), 'ALL', vs.act_site_code, : p_servicecenter) and
((to_char(vs.load_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) or
(to_char(vs.match_date,'yymmdd') between to_char(: p_begindate,'yymmdd') and to_char(: p_enddate,'yymmdd')) ) and
vs.pn = vc.pn and
vs.pn = vf.pn(+) and
vf.pn = vr.pn(+) and vf.litho_code = vr.litho_code(+) and vf.doc_id = vr.doc_id(+) and
vf.pn = vm.pn(+) and vf.litho_code = vm.litho_code(+) and vf.doc_id = vm.doc_id(+) and
vf.pn = vl.pn(+) and vf.litho_code = vl.litho_code(+) and vf.doc_id = vl.doc_id(+) and
vf.pn = vw.pn(+) and vf.litho_code = vw.litho_code(+) and vf.doc_id = vw.doc_id(+) and
vf.pn = vi.pn(+) and vf.litho_code = vi.litho_code(+) and vf.doc_id = vi.doc_id(+) and
vf.pn = vt.pn(+) and vf.litho_code = vt.litho_code(+) and vf.doc_id = vt.doc_id(+) and
vf.pn = va.pn(+) and vf.litho_code = va.litho_code(+) and vf.doc_id = va.doc_id(+) and
vf.pn = vb.pn(+) and vf.litho_code = vb.litho_code(+) and vf.doc_id = vb.doc_id(+)
order by vs.test_date
Last edited by Lily_Liu_2004; 08-02-2004 at 04:33 PM.
This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...
Originally posted by raghud This sql will not go into cartesian product. Some improvement can be done by taking the filters to the bottom and by creating function based indexes such as upper() and index on test_date,clienttype ...
Any way good luck on outer joins
I never said it was a catesian product. With so many tables being joined and no information about the key structure. It could be that he doesn't have a cartesian product, and that a few simple tweaks will make all the difference. But without an explain plan, and with the query having so many outer joins, how can you say what it will do?
Plan Rows
------------------------------------------------------------ -----
SELECT STATEMENT 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS OUTER 1
NESTED LOOPS 1
VIEW V_PN_SITE 17
SORT UNIQUE 17
FILTER
NESTED LOOPS OUTER
NESTED LOOPS 1
NESTED LOOPS 1
TABLE ACCESS BY INDEX R PN_INFORMA 4
INDEX RANGE SCAN PN_INFORMA 4
TABLE ACCESS BY INDEX R SITE_HEADE 1
INDEX UNIQUE SCA SITE_HEADE 1
TABLE ACCESS BY INDEX R EXPRESS_SI 432
INDEX UNIQUE SCA EXPRESS_SI 432
TABLE ACCESS BY INDEX R ESSAY_TEST 38098
INDEX RANGE SCAN ESSAY_TEST 38098
TABLE ACCESS BY INDEX R LEVEL2_HEA 1607
INDEX RANGE SCAN LEVEL2_HEA 1607
TABLE ACCESS BY INDEX R EXAMINEE 27718
INDEX RANGE SCAN EXAMINEE_L 27718
VIEW V_PN_L_DET 3067
INDEX FAST FULL IDX_ESSAY_ 3067
VIEW V_PN_W_DET 3466
INDEX FAST FULL IDX_ESSAY_ 3466
VIEW V_PN_RFI_D 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019
VIEW V_PN_AM_DE 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019
VIEW V_PN_LI_DE 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019
VIEW V_PN_TW_DE 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019
VIEW V_PN_AT_DE 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019
VIEW V_PN_OB_DE 9676
NESTED LOOPS 9676
TABLE ACCESS FULL FORMS 79
INDEX RANGE SCAN IDX_MC_TES 72019