Oninit cdi / Archive
Talk to an expert →

question on sqexplain output

Posted in 2006

Topics: Performance & Tuning, SQL Development & Query Writing

Floyd Wellershaus — — source: Usenet: comp.databases.informix

A question about the below sqexplain file from this long query: Basically, I'm trying to understand the output of sqexplain. In this section below, ( the join on s.scsublog_token with sc.token ), does this output mean that the only index being used is the sc.token index , or is it also using the index on s.scsublog_token ? 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 Thanks 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: 130144 Estimated # of Rows Returned: 9944 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: 12 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) 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.scp rofil_token = sentrycf.student.p_scprofil_token ) NESTED LOOP JOIN 3) root.s: INDEX PATH (1) Index Keys: stprofil_token Lower Index Filter: root.s.stprofil_token = root.st.stprofil_token NESTED LOOP JOIN 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 = sentrycf.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) root.l: INDEX PATH Filters: root.s.mbntfhst_token = root.l.mbntfhst_token (1) Index Keys: dlesublog_id (Serial, fragments: ALL) Lower Index Filter: root.l.dlesublog_id = 94 NESTED LOOP JOIN =========================================== In this section below, ( the join on s.scsublog_token with sc.token ), does this output mean that the only index being used is the sc.token index , or is it also using the index on s.scsublog_token ? 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 Thanks ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/

↪ replying to Floyd Wellershaus
Art S. Kagel — — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > A question about the below sqexplain file from this long query: > > Basically, I'm trying to understand the output of sqexplain. > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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 Floyd, 3) root.s: INDEX PATH (1) Index Keys: stprofil_token Lower Index Filter: root.s.stprofil_token = root.st.stprofil_token> This section indicates that the index on stprofil_token is used on the stntfhst table not the index on scsublog_token. The reference above is just saying that the current value of s.scsublog_token is being used as a start off point to seach the sc.token in the index above. Art S. Kagel > > Thanks > > > > > > > > > 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: 130144 > Estimated # of Rows Returned: 9944 > 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: 12 > 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) 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.scp > rofil_token = sentrycf.student.p_scprofil_token ) > NESTED LOOP JOIN > 3) root.s: INDEX PATH > (1) Index Keys: stprofil_token > Lower Index Filter: root.s.stprofil_token = > root.st.stprofil_token > NESTED LOOP JOIN > 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 = sentrycf.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) root.l: INDEX PATH > Filters: root.s.mbntfhst_token = root.l.mbntfhst_token > (1) Index Keys: dlesublog_id (Serial, fragments: ALL) > Lower Index Filter: root.l.dlesublog_id = 94 > NESTED LOOP JOIN > =========================================== > > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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

↪ replying to Floyd Wellershaus
Art S. Kagel — — source: Usenet: comp.databases.informix

Floyd Wellershaus wrote: > A question about the below sqexplain file from this long query: > > Basically, I'm trying to understand the output of sqexplain. > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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 Floyd, 3) root.s: INDEX PATH (1) Index Keys: stprofil_token Lower Index Filter: root.s.stprofil_token = root.st.stprofil_token> This section indicates that the index on stprofil_token is used on the stntfhst table not the index on scsublog_token. The reference above is just saying that the current value of s.scsublog_token is being used as a start off point to seach the sc.token in the index above. Art S. Kagel > > Thanks > > > > > > > > > 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: 130144 > Estimated # of Rows Returned: 9944 > 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: 12 > 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) 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.scp > rofil_token = sentrycf.student.p_scprofil_token ) > NESTED LOOP JOIN > 3) root.s: INDEX PATH > (1) Index Keys: stprofil_token > Lower Index Filter: root.s.stprofil_token = > root.st.stprofil_token > NESTED LOOP JOIN > 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 = sentrycf.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) root.l: INDEX PATH > Filters: root.s.mbntfhst_token = root.l.mbntfhst_token > (1) Index Keys: dlesublog_id (Serial, fragments: ALL) > Lower Index Filter: root.l.dlesublog_id = 94 > NESTED LOOP JOIN > =========================================== > > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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

↪ replying to Art S. Kagel
Floyd Wellershaus — — source: Usenet: comp.databases.informix

Aahhh. Duh. I guess the key word there is Lower Index Filter. Thanks for pointing that out Art. ======================== -<<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: Art S. Kagel <kagel@bloomberg.net> To: informix-list@iiug.org Cc: informix-list@iiug.org Sent: Thursday, August 3, 2006 3:46:20 PM Subject: Re: question on sqexplain output Floyd Wellershaus wrote: > A question about the below sqexplain file from this long query: > > Basically, I'm trying to understand the output of sqexplain. > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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 Floyd, 3) root.s: INDEX PATH (1) Index Keys: stprofil_token Lower Index Filter: root.s.stprofil_token = root.st.stprofil_token> This section indicates that the index on stprofil_token is used on the stntfhst table not the index on scsublog_token. The reference above is just saying that the current value of s.scsublog_token is being used as a start off point to seach the sc.token in the index above. Art S. Kagel > > Thanks > > > > > > > > > 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: 130144 > Estimated # of Rows Returned: 9944 > 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: 12 > 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) 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.scp > rofil_token = sentrycf.student.p_scprofil_token ) > NESTED LOOP JOIN > 3) root.s: INDEX PATH > (1) Index Keys: stprofil_token > Lower Index Filter: root.s.stprofil_token = > root.st.stprofil_token > NESTED LOOP JOIN > 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 = sentrycf.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) root.l: INDEX PATH > Filters: root.s.mbntfhst_token = root.l.mbntfhst_token > (1) Index Keys: dlesublog_id (Serial, fragments: ALL) > Lower Index Filter: root.l.dlesublog_id = 94 > NESTED LOOP JOIN > =========================================== > > In this section below, ( the join on s.scsublog_token with sc.token ), > does this output mean that the only index being used is the sc.token > index , or is it also using the index on s.scsublog_token ? > > 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 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list