Re: help with complicated query, and joining on views

Posted in 2006

Topics: Performance & Tuning, SQL Development & Query Writing

Floyd Wellershaus — Mon, 31 Jul 2006 08:14:35 -0700 (PDT) — source: Usenet: comp.databases.informix

Do you know if there's any reason for that or documentation about that ? Thanks. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: Yunyao.Qu@noaa.gov To: Superboer <superboer7@t-online.de> Cc: informix-list@iiug.org Sent: Monday, July 31, 2006 10:42:06 AM Subject: 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

↪ replying to Floyd Wellershaus

bozon — 1 Aug 2006 05:17:12 -0700 — source: Usenet: comp.databases.informix

I believe that there is a flag in 10 that helps with view optimization. My memory is broken right now so I can't remember what it is. This is twice in 2 weeks. I'll keep thinking and looking for it. Hopefully someone with less damage to their brain will remember. Floyd Wellershaus wrote: > Do you know if there's any reason for that or documentation about that ? > > Thanks. > > > > > > > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > > > email: fwellers@yahoo.com > > > Home: 703-430-0805 > > > Cell: 703-477-6045 > ======================== > > > http://www.one.org/ > > > > ----- Original Message ---- > From: Yunyao.Qu@noaa.gov > To: Superboer <superboer7@t-online.de> > Cc: informix-list@iiug.org > Sent: Monday, July 31, 2006 10:42:06 AM > Subject: 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@@N

↪ replying to Floyd Wellershaus

bozon — 1 Aug 2006 05:27:16 -0700 — source: Usenet: comp.databases.informix

I am not convinced that there is a general problem with views. I haven't seen problems with view performance over other the similar queries written using temp tables. In fact I had some queries run faster using views than temp tables but it was more a factor of the optimizer choices. In general I would think that they would be converted to their SQL and then run through the optimizer to take full advantage of the fact that they are views. I really suspect that step in the query that uses the dynamic hash. You need to have a good reason for that to be the correct way to run a query, if not it can really have a severe impact on performance. If you can figure out why it isn't using an index to make that join you might solve your problem. Floyd Wellershaus wrote: > Do you know if there's any reason for that or documentation about that ? > > Thanks. > > > > > > > > > > ======================== > -<<Floyd Wellershaus>>- > Database Administrator > Unix Administrator > > > > email: fwellers@yahoo.com > > > Home: 703-430-0805 > > > Cell: 703-477-6045 > ======================== > > > http://www.one.org/ > > > > ----- Original Message ---- > From: Yunyao.Qu@noaa.gov > To: Superboer <superboer7@t-online.de> > Cc: informix-list@iiug.org > Sent: Monday, July 31, 2006 10:42:06 AM > Subject: 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:

↪ replying to bozon

Yunyao (Frank) Qu — Tue, 01 Aug 2006 12:21:02 -0400 — source: Usenet: comp.databases.informix

Theoretically, the performance should be the same for joining views or joining base tables assuming your optimizer is FULLLY smart (must be smarter than some humans!) Take example, view1 is composed by tab1, tab2,… tabn. Query: select … from view1, mytab where view1.x=mytab.y What you should do? Only you know where the data should come from! To my best knowledge, the current AI has some difficulties to get the right answer. Frank bozon wrote: >I am not convinced that there is a general problem with views. I >haven't seen problems with view performance over other the similar >queries written using temp tables. In fact I had some queries run >faster using views than temp tables but it was more a factor of the >optimizer choices. In general I would think that they would be >converted to their SQL and then run through the optimizer to take full >advantage of the fact that they are views. > >I really suspect that step in the query that uses the dynamic hash. You >need to have a good reason for that to be the correct way to run a >query, if not it can really have a severe impact on performance. > >If you can figure out why it isn't using an index to make that join you >might solve your problem. > > >Floyd Wellershaus wrote: > > >>Do you know if there's any reason for that or documentation about that ? >> >>Thanks. >> >> >> >> >> >> >> >> >> >>======================== >>-<<Floyd Wellershaus>>- >>Database Administrator >>Unix Administrator >> >> >> >>email: fwellers@yahoo.com >> >> >>Home: 703-430-0805 >> >> >>Cell: 703-477-6045 >>======================== >> >> >>http://www.one.org/ >> >> >> >>----- Original Message ---- >>From: Yunyao.Qu@noaa.gov >>To: Superboer <superboer7@t-online.de> >>Cc: informix-list@iiug.org >>Sent: Monday, July 31, 2006 10:42:06 AM >>Subject: 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>

↪ replying to Yunyao (Frank) Qu

bozon — 1 Aug 2006 11:53:53 -0700 — source: Usenet: comp.databases.informix

I don't quite understand your example. This view is no different than creating a join of tab1, tab2, ... tabn. Are you saying that if you were only using data from tab1 and tab2 the view would be wasteful? In that case I completely agree. But it is also the same for a select with more tables than are needed in the return set. It is wasteful also. In fact one optimization is to look for tables that you can eliminate from a query and still get the same results. So, I don't think that the view is the problem in your hypothetical case it is just that you are using the wrong view to accomplish what you want. Yunyao (Frank) Qu wrote: > Theoretically, the performance should be the same for joining views or > joining base tables assuming your optimizer is FULLLY smart (must be > smarter than some humans!) > > Take example, view1 is composed by tab1, tab2,... tabn. > > Query: select ... from view1, mytab where view1.x=mytab.y > > What you should do? > > Only you know where the data should come from! To my best knowledge, the > current AI has some difficulties to get the right answer. > > Frank > > > > bozon wrote: > > >I am not convinced that there is a general problem with views. I > >haven't seen problems with view performance over other the similar > >queries written using temp tables. In fact I had some queries run > >faster using views than temp tables but it was more a factor of the > >optimizer choices. In general I would think that they would be > >converted to their SQL and then run through the optimizer to take full > >advantage of the fact that they are views. > > > >I really suspect that step in the query that uses the dynamic hash. You > >need to have a good reason for that to be the correct way to run a > >query, if not it can really have a severe impact on performance. > > > >If you can figure out why it isn't using an index to make that join you > >might solve your problem. > > > > > >Floyd Wellershaus wrote: > > > > > >>Do you know if there's any reason for that or documentation about that ? > >> > >>Thanks. > >> > >> > >> > >> > >> > >> > >> > >> > >> > >>======================== > >>-<<Floyd Wellershaus>>- > >>Database Administrator > >>Unix Administrator > >> > >> > >> > >>email: fwellers@yahoo.com > >> > >> > >>Home: 703-430-0805 > >> > >> > >>Cell: 703-477-6045 > >>======================== > >> > >> > >>http://www.one.org/ > >> > >> > >> > >>----- Original Message ---- > >>From: Yunyao.Qu@noaa.gov > >>To: Superboer <superboer7@t-online.de> > >>Cc: informix-list@iiug.org > >>Sent: Monday, July 31, 2006 10:42:06 AM > >>Subject: 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)