Re: help with complicated query, and joining on views

We should avoid "join on views". join on view could cause performance suffer. Use the base tables to join, NOT views! Frank ----- Original Message ----- From: Superboer <superboer7@t-online.de> Date: Monday, July 31, 2006 10:33 am Subject: Re: help with complicated query, and joining on views > > > If I do a dbschema -ss on it, there are no indexes. I'm not even > sure you can put an index on a view. > > you can not create an index on a view; but you can create indexes on > the underlying tables which get used > > > Yet, according to the sqexplain file, that table is being joined > on an indexed column ( if I'm reading this correctly ). > > > > How does that work ? > > join is done on underlying tables and therefor using indexes. > > in order to tell more on this issue; the # of rows per table is > needed, > to see if the order of joining makes sense... > the schema of the tables are needed > (in case for example one is joning an int with a char....) > has update stats being run recently.?????????-->>>> see/grab Art's > update stats stuff. > > > > is: > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: root.s.mbntfhst_token = > root.l.mbntfhst_token > > this really nessesary; may be there is an index missing or?? > > may be you need to experiment with hints to the optimizer... > > Superboer. > > > > > > Here is the schema for the view: > > create view "sentrycf".stprofil > (token,scsublog_token,s_token,scprofil_token,ssn,first_name,initial,las t_name,name_suffix,birth_dt,street1,street2,city,state,zip,country,cert ification_dt,enroll_status,status_start_dt,pre_ch_status_flag,grad_dt,t erm_beg_dt,term_end_dt,dir_block_ind,source_flag,rpt_sublog_token,rpt_t oken,rec_type) as > > select x0.stprofil_token ,x0.scsublog_token ,x0.s_token > ,x0.scprofil_token> ,x0.ssn ,x0.first_name ,x0.initial > ,x0.last_name ,x0.name_suffix > > ,x0.birth_dt ,x0.street1 ,x0.street2 ,x0.city ,x0.state , > > x0.zip ,x0.country ,x0.certification_dt ,x0.enroll_status > > ,x0.status_start_dt ,x0.pre_ch_status_flag ,x0.grad_dt > ,x0.term_beg_dt> ,x0.term_end_dt ,x0.dir_block_ind > ,x0.source_flag ,x0.rpt_sublog_token > > ,x0.rpt_token ,x0.rec_type from "sentrycf".stdschl x0 > ,"sentrycf"> .student x1 where ((x0.stprofil_token = x1.token > ) AND (x0.scprofil_token > > = x1.p_scprofil_token ) ) ;> > > > Here is the sqexplain for the query: > > > > QUERY: > > ------ > > select distinct dledetail_id from dledetail > > where dlesublog_id = 94 > > and not exists > > (select s.* from stntfhst s,stprofil st,scsublog sc, scprofil p,> dlesublog l > > where s.stprofil_token = st.token and l.dlesublog_id = 94 > > and s.scsublog_token = sc.token and sc.scprofil_token = p.token > > and st.ssn = dledetail.ssn and p.schl_code =dledetail.schl_code > > and p.schl_branch = dledetail.schl_branch and s.mbntfhst_token = > l.mbntfhst_token)> into temp temp_dlefiltered94 > > Estimated Cost: 83855 > > Estimated # of Rows Returned: 2368 > > 1) sentrycf.dledetail: INDEX PATH > > Filters: NOT EXISTS <subquery> > > (1) Index Keys: dlesublog_id (Serial, fragments: ALL) > > Lower Index Filter: sentrycf.dledetail.dlesublog_id = 94 > > Subquery: > > --------- > > Estimated Cost: 17 > > Estimated # of Rows Returned: 1 > > 1) root.st: INDEX PATH > > > > (1) Index Keys: ssn scprofil_token (Serial, fragments: > ALL)> Lower Index Filter: root.st.ssn = > sentrycf.dledetail.ssn> 2) root.s: INDEX PATH > > (1) Index Keys: stprofil_token > > Lower Index Filter: root.s.stprofil_token = > root.st.stprofil_token> NESTED LOOP JOIN > > 3) root.l: INDEX PATH > > (1) Index Keys: dlesublog_id (Serial, fragments: ALL) > > Lower Index Filter: root.l.dlesublog_id = 94 > > > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: root.s.mbntfhst_token = > root.l.mbntfhst_token> 4) root.p: INDEX PATH > > (1) Index Keys: schl_code schl_branch > > Lower Index Filter: (root.p.schl_code = > sentrycf.dledetail.schl_code AND root.p.schl_branch = sen > > trycf.dledetail.schl_branch ) > > NESTED LOOP JOIN > > 5) root.sc: INDEX PATH > > Filters: root.sc.scprofil_token = root.p.token > > (1) Index Keys: token > > Lower Index Filter: root.s.scsublog_token = > root.sc.token> NESTED LOOP JOIN > > 6) sentrycf.student: INDEX PATH > > (1) Index Keys: p_scprofil_token token (Key-Only) > (Serial, fragments: ALL) > > Lower Index Filter: (root.st.stprofil_token = > sentrycf.student.token AND root.st.scprofil_token = > > sentrycf.student.p_scprofil_token ) > > NESTED LOOP JOIN > > > > > > > > > > > > > > > > > > > > > > ======================== > > -<<Floyd Wellershaus>>- > > Database Administrator > > Unix Administrator > > > > > > > > email: fwellers@yahoo.com > > > > > > Home: 703-430-0805 > > > > > > Cell: 703-477-6045 > > ======================== > > > > > > http://www.one.org/ > > --0-277742810-1154354087=:13668 > > Content-Type: text/html > > X-Google-AttachSize: 6438 > > > > <html><head><style type="text/css"><!-- DIV {margin:0px} -- > ></style></head><body><div style="font-family:times new roman, new > york, times, serif;font-size:12pt"><DIV></DIV> > > <DIV>I have this query, ( below is the sqexplain file ). It's a > bugger for sure.</DIV> > > <DIV>Anyway, pardon my igorance, but I don't understand > something. The stprofil table ( st ), is a view of some very big > tables.</DIV>> <DIV>If I do a dbschema -ss on it, there are no > indexes. I'm not even sure you can put an index on a view.</DIV> > > <DIV> </DIV> > > <DIV>Yet, according to the sqexplain file, that table is being > joined on an indexed column ( if I'm reading this correctly ).</DIV> > > <DIV> </DIV> > > <DIV>How does that work ?</DIV> > > <DIV> </DIV> > > <DIV>Also, if there are any glaringly obvious things to fix, as > to why this query takes so long, I'd appreciate it.</DIV> > > <DIV> </DIV> > > <DIV>Thanks.</DIV> > > <DIV> </DIV> > > <DIV>Here is the schema for the view:</DIV> > > <DIV>create view "sentrycf".stprofil > (token,scsublog_token,s_token,scprofil_token,ssn,first_name,initial,las t_name,name_suffix,birth_dt,street1,street2,city,state,zip,country,cert ification_dt,enroll_status,status_start_dt,pre_ch_status_flag,grad_dt,t erm_beg_dt,term_end_dt,dir_block_ind,source_flag,rpt_sublog_token,rpt_t oken,rec_type) as <BR> select x0.stprofil_token ,x0.scsublog_token ,x0.s_token ,x0.scprofil_token <BR> ,x0.ssn ,x0.first_name ,x0.initial ,x0.last_name ,x0.name_suffi x <BR> ,x0.birth_dt ,x0.street1 ,x0.street2 ,x0.city ,x0.state ,<BR> x0.zip ,x0.country ,x0.certification_dt ,x0.enroll_status <BR> ,x0.status_start_dt ,x0.pre_ch_status_flag ,x0.grad_dt ,x0.term _beg_dt <BR> ,