Re: help with complicated query, and joining on views
Posted in 2006
Topics: Performance & Tuning, SQL Development & Query Writing
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> ,