help with complicated query, and joining on views
I have this query, ( below is the sqexplain file ). It's a bugger for sure.
Anyway, pardon my igorance, but I don't understand something. The stprofil table ( st ), is a view of some very big tables.
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.
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 ?
Also, if there are any glaringly obvious things to fix, as to why this query takes so long, I'd appreciate it.
Thanks.
Here is the schema for the view:
create view "sentrycf".stprofil (token,scsublog_token,s_token,scprofil_token,ssn,first_name,initial,last_name,name_suffix,birth_dt,street1,street2,city,state,zip,country,certification_dt,enroll_status,status_start_dt,pre_ch_status_flag,grad_dt,term_beg_dt,term_end_dt,dir_block_ind,source_flag,rpt_sublog_token,rpt_token,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_dlefiltered94Estimated 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/
> 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,last_name,name_suffix,birth_dt,street1,street2,city,state,zip,country,certification_dt,enroll_status,status_start_dt,pre_ch_status_flag,grad_dt,term_beg_dt,term_end_dt,dir_block_ind,source_flag,rpt_sublog_token,rpt_token,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,last_name,name_suffix,birth_dt,street1,street2,city,state,zip,country,certification_dt,enroll_status,status_start_dt,pre_ch_status_flag,grad_dt,term_beg_dt,term_end_dt,dir_block_ind,source_flag,rpt_sublog_token,rpt_token,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_suffix <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> ,x0.term_end_dt ,x0.dir_block_ind ,x0.source_flag ,x0.rpt_sublog_token <BR> ,x0.rpt_token ,x0.rec_type from "sentrycf".stdschl x0 ,"sentrycf"<BR> .student x1
> where ((x0.stprofil_token = x1.token ) AND (x0.scprofil_token <BR> = x1.p_scprofil_token ) ) ; </DIV>
> <DIV> </DIV>
> <DIV>Here is the sqexplain for the query:</DIV>
> <DIV> </DIV>
> <DIV>QUERY:<BR>------<BR>select distinct dledetail_id from dledetail<BR>where dlesublog_id = 94<BR>and not exist