I've attached an sqexplain on a similar query, if that might help.
On Wed, 2007-11-07 at 19:30 -0500, Chris Salch wrote:
> IBM Informix Dynamic Server Version 9.40.HC5
> HPUX 11iV1
>
> I have a very weird issue with a very complex set of views. Here is
> what I'm seeing:
>
> SELECT
>
> bucket, id
> FROM
>
> lu_ar_bucket_vw
> WHERE
>
> bucket=5
> ;
>
> bucket id
> --------- -------
> 8 2058987
> 8 2078673
> 8 2059319
> 8 2062939
> * snip *
QUERY:
------
create view "informix".lu_ar_prog_vw
(id,bal_act,debit_flag,prog,deggrp,enr_date,last_class_date) as select
x0.suba_no ,x0.bal_act ,(x0.bal_act >
'0.00' ) ,x1.prog ,x1.deggrp ,x1.enr_date ,(select max(x4.end_date )
from ("informix".cw_rec x3 left join "informix".sec_rec x4 on
(((((x4.crs_no = x3.crs_no ) AND (x4.sec_no = x3.sec ) ) AND (x4.cat =
x3.cat ) ) AND (x4.sess = x3.sess ) ) AND (x4.yr = x3.yr ) ) )where
((x3.stat = 'R' ) AND (x3.id = x0.suba_no ) ) ) from
(("informix".suba_rec x0 join "informix".prog_enr_rec x1 on (x0.id =
x1.id ) )join "informix".prog_enr_rec x2 on (x0.id = x2.id ) )where
(((x0.subs = 'S/A' ) AND (x0.bal_act != '0.00' ) ) AND (x1.subprog IN
('EC' ,'NG' ,'NU' )) ) group by
x0.suba_no ,x0.bal_act ,3 ,x1.prog ,x1.deggrp ,x1.enr_date ,7 having
(x1.enr_date = max(x2.enr_date ) ) ;
Estimated Cost: 4144
Estimated # of Rows Returned: 70
Temporary Files Required For: Group By
1) informix.suba_rec: INDEX PATH
Filters: informix.suba_rec.bal_act != $0.00
(1) Index Keys: subs
Lower Index Filter: informix.suba_rec.subs = 'S/A'
2) informix.prog_enr_rec: INDEX PATH
Filters: informix.prog_enr_rec.subprog IN ('EC' , 'NG' ,
'NU' )
(1) Index Keys: id prog site
Lower Index Filter: informix.suba_rec.id =
informix.prog_enr_rec.id
ON-Filters:informix.suba_rec.id = informix.prog_enr_rec.id
NESTED LOOP JOIN
3) informix.prog_enr_rec: INDEX PATH
(1) Index Keys: id prog site
Lower Index Filter: informix.suba_rec.id =
informix.prog_enr_rec.id
ON-Filters:informix.suba_rec.id = informix.prog_enr_rec.id
NESTED LOOP JOIN
PostJoin-Filters:(((informix.suba_rec.subs = 'S/A' AND
informix.suba_rec.bal_act != $0.00 ) AND informix.prog_enr_rec.subprog
IN ('EC' , 'NG' , 'NU' )) AND CASE WHEN informix.prog_enr_rec.deggrp
LIKE 'W/D%' THEN 10 WHEN informix.prog_enr_rec.deggrp LIKE 'SAB%'
THEN 9 WHEN <subquery> = t THEN 8 WHEN <subquery> = t THEN 7 WHEN
<subquery> = t THEN 6 WHEN <subquery> = t THEN 5 WHEN <subquery> = t
THEN 4 WHEN <subquery> = t THEN 3 WHEN <subquery> = t THEN 2 ELSE 1
END= 2 )
Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) salchc.x1: INDEX PATH
Filters: salchc.x1.stat = 'C'
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x1.id =
informix.suba_rec.suba_no AND salchc.x1.tick = 'FY06' )
Key-First Filters: (salchc.x1.resrc = 'ISIR' )
(2) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x1.id =
informix.suba_rec.suba_no AND salchc.x1.tick = 'FY07' )
Key-First Filters: (salchc.x1.resrc = 'ISIR' )
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) salchc.x2: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x2.id =
informix.suba_rec.suba_no
2) salchc.x3: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x3.id = salchc.x2.id AND
salchc.x3.tick = 'FY06' )
Key-First Filters: (salchc.x3.resrc = 'NRF7' )
ON-Filters:(salchc.x3.id = salchc.x2.id AND (salchc.x3.tick
= 'FY06' AND salchc.x3.resrc = 'NRF7' ) )
NESTED LOOP JOIN
3) salchc.x4: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x4.id = salchc.x2.id AND
salchc.x4.tick = 'FY07' )
Key-First Filters: (salchc.x4.resrc = 'NRF8' )
ON-Filters:(salchc.x4.id = salchc.x2.id AND (salchc.x4.tick =
'FY07' AND salchc.x4.resrc = 'NRF8' ) )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x2.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 2
Estimated # of Rows Returned: 1
1) salchc.x5: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x5.id =
informix.suba_rec.suba_no
2) salchc.x6: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x6.id = salchc.x5.id AND
salchc.x6.tick = 'FY06' )
Key-First Filters: (salchc.x6.resrc = 'FAC7' )
ON-Filters:(salchc.x6.id = salchc.x5.id AND (salchc.x6.tick
= 'FY06' AND salchc.x6.resrc = 'FAC7' ) )
NESTED LOOP JOIN
3) salchc.x7: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x7.id = salchc.x5.id AND
salchc.x7.tick = 'FY07' )
Key-First Filters: (salchc.x7.resrc = 'FAC8' )
ON-Filters:(salchc.x7.id = salchc.x5.id AND (salchc.x7.tick =
'FY07' AND salchc.x7.resrc = 'FAC8' ) )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x5.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 6
Estimated # of Rows Returned: 1
1) salchc.x8: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x8.id =
informix.suba_rec.suba_no
2) salchc.x9: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc
(Key-First)
Lower Index Filter: (salchc.x9.id = salchc.x8.id
AND salchc.x9.tick = 'FY06' )
Key-First Filters: (salchc.x9.resrc = 'FAC7' )
ON-Filters:(salchc.x9.id = salchc.x8.id AND
(salchc.x9.tick = 'FY06' AND salchc.x9.resrc = 'FAC7' ) )
NESTED LOOP JOIN
3) salchc.x10: INDEX PATH
(1) Index Keys: id ay_slot bbay_entry_id (Key-Only)
(Serial, fragments: ALL)
Lower Index Filter: (salchc.x10.id = salchc.x8.id
AND salchc.x10.ay_slot = 2 )
ON-Filters:(salchc.x10.id = salchc.x8.id AND
salchc.x10.ay_slot = 2 )
NESTED LOOP JOIN
4) salchc.x11: INDEX PATH
Filters: salchc.x11.beg_date > TODAY + 30
(1) Index Keys: bbay_entry_id (Serial, fragments: ALL)
Lower Index Filter: salchc.x10.bbay_entry_id =
salchc.x11.bbay_entry_id
ON-Filters:(salchc.x10.bbay_entry_id = salchc.x11.bbay_entry_id
AND salchc.x11.beg_date > TODAY + 30 )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x8.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 7
Estimated # of Rows Returned: 1
1) salchc.x12: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x12.id =
informix.suba_rec.suba_no
2) salchc.x13: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc
(Key-First)
Lower Index Filter: (salchc.x13.id =
salchc.x12.id AND salchc.x13.tick = 'FY06' )
Key-First Filters: (salchc.x13.resrc =
'FAC7' )
ON-Filters:(salchc.x13.id = salchc.x12.id AND
(salchc.x13.tick = 'FY06' AND salchc.x13.resrc = 'FAC7' ) )
NESTED LOOP JOIN
3) salchc.x14: INDEX PATH
(1) Index Keys: id ay_slot bbay_entry_id
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (salchc.x14.id =
salchc.x12.id AND salchc.x14.ay_slot = 2 )
ON-Filters:(salchc.x14.id = salchc.x12.id AND
salchc.x14.ay_slot = 2 )
NESTED LOOP JOIN
4) salchc.x15: INDEX PATH
Filters: salchc.x15.beg_date <= TODAY + 30
(1) Index Keys: bbay_entry_id (Serial, fragments: ALL)
Lower Index Filter: salchc.x14.bbay_entry_id =
salchc.x15.bbay_entry_id
ON-Filters:(salchc.x14.bbay_entry_id =
salchc.x15.bbay_entry_id AND salchc.x15.beg_date <= TODAY + 30 )
NESTED LOOP JOIN
5) salchc.x16: INDEX PATH
Filters: salchc.x16.stat = 'C'
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x16.id = salchc.x12.id AND
salchc.x16.tick = 'FY07' )
Key-First Filters: (salchc.x16.resrc = 'ISIR' )
ON-Filters:(((salchc.x16.id = salchc.x12.id AND salchc.x16.resrc
= 'ISIR' ) AND salchc.x16.stat = 'C' ) AND salchc.x16.tick = 'FY07' )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x12.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 7
Estimated # of Rows Returned: 1
1) salchc.x17: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x17.id =
informix.suba_rec.suba_no
2) salchc.x18: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc
(Key-First)
Lower Index Filter: (salchc.x18.id =
salchc.x17.id AND salchc.x18.tick = 'FY06' )
Key-First Filters: (salchc.x18.resrc =
'FAC7' )
ON-Filters:(salchc.x18.id = salchc.x17.id AND
(salchc.x18.tick = 'FY06' AND salchc.x18.resrc = 'FAC7' ) )
NESTED LOOP JOIN
3) salchc.x19: INDEX PATH
(1) Index Keys: id ay_slot bbay_entry_id
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (salchc.x19.id =
salchc.x17.id AND salchc.x19.ay_slot = 2 )
ON-Filters:(salchc.x19.id = salchc.x17.id AND
salchc.x19.ay_slot = 2 )
NESTED LOOP JOIN
4) salchc.x20: INDEX PATH
Filters: salchc.x20.beg_date <= TODAY + 30
(1) Index Keys: bbay_entry_id (Serial, fragments: ALL)
Lower Index Filter: salchc.x19.bbay_entry_id =
salchc.x20.bbay_entry_id
ON-Filters:(salchc.x19.bbay_entry_id =
salchc.x20.bbay_entry_id AND salchc.x20.beg_date <= TODAY + 30 )
NESTED LOOP JOIN
5) salchc.x21: INDEX PATH
Filters: salchc.x21.stat = 'C'
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x21.id = salchc.x17.id AND
salchc.x21.tick = 'FY07' )
Key-First Filters: (salchc.x21.resrc = 'ISIR' )
ON-Filters:(((salchc.x21.id = salchc.x17.id AND salchc.x21.resrc
= 'ISIR' ) AND salchc.x21.stat = 'C' ) AND salchc.x21.tick = 'FY07' )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x17.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
Subquery:
---------
Estimated Cost: 7
Estimated # of Rows Returned: 1
1) salchc.x22: INDEX PATH
(1) Index Keys: id (Key-Only)
Lower Index Filter: salchc.x22.id =
informix.suba_rec.suba_no
2) salchc.x23: INDEX PATH
(1) Index Keys: tick id cmpl_date resrc
(Key-First)
Lower Index Filter: (salchc.x23.id =
salchc.x22.id AND salchc.x23.tick = 'FY06' )
Key-First Filters: (salchc.x23.resrc =
'FAC7' )
ON-Filters:(salchc.x23.id = salchc.x22.id AND
(salchc.x23.tick = 'FY06' AND salchc.x23.resrc = 'FAC7' ) )
NESTED LOOP JOIN
3) salchc.x24: INDEX PATH
(1) Index Keys: id ay_slot bbay_entry_id
(Key-Only) (Serial, fragments: ALL)
Lower Index Filter: (salchc.x24.id =
salchc.x22.id AND salchc.x24.ay_slot = 2 )
ON-Filters:(salchc.x24.id = salchc.x22.id AND
salchc.x24.ay_slot = 2 )
NESTED LOOP JOIN
4) salchc.x25: INDEX PATH
Filters: salchc.x25.beg_date > TODAY + 30
(1) Index Keys: bbay_entry_id (Serial, fragments: ALL)
Lower Index Filter: salchc.x24.bbay_entry_id =
salchc.x25.bbay_entry_id
ON-Filters:(salchc.x24.bbay_entry_id =
salchc.x25.bbay_entry_id AND salchc.x25.beg_date > TODAY + 30 )
NESTED LOOP JOIN
5) salchc.x26: INDEX PATH
Filters: salchc.x26.stat = 'C'
(1) Index Keys: tick id cmpl_date resrc (Key-First)
Lower Index Filter: (salchc.x26.id = salchc.x22.id AND
salchc.x26.tick = 'FY07' )
Key-First Filters: (salchc.x26.resrc IN ('ANFORM' ,
'ANFBBY' ))
ON-Filters:(((salchc.x26.tick = 'FY07' AND salchc.x26.resrc IN
('ANFORM' , 'ANFBBY' )) AND salchc.x26.stat = 'C' ) AND salchc.x26.id =
salchc.x22.id )
NESTED LOOP JOIN
PostJoin-Filters:salchc.x22.id = informix.suba_rec.suba_no
UDRs in query:
--------------
UDR id : -113
UDR name: equal
UDRs in query:
--------------
UDR id : -113
UDR name: equal
QUERY:
------
SELECT
bucket, id
FROM
lu_ar_bucket_vw
WHERE
bucket=2
--id in ( 2069949, 2069952)
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) (Temp Table For View): SEQUENTIAL SCAN
UDRs in query:
--------------
UDR id : -113
UDR name: equal
QUERY:
------
create view "informix".lu_ar_prog_vw
(id,bal_act,debit_flag,prog,deggrp,enr_date,last_class_date) as select
x0.suba_no ,x0.bal_act ,(x0.bal_act >
'0.00' ) ,x1.prog ,x1.deggrp ,x1.enr_date ,(select max(x4.end_date )
from ("informix".cw_rec x3 left join "informix".sec_rec x4 on
(((((x4.crs_no = x3.crs_no ) AND (x4.sec_no = x3.sec ) ) AND (x4.cat =
x3.cat ) ) AND (x4.sess = x3.sess ) ) AND (x4.yr = x3.yr ) ) )where
((x3.stat = 'R' ) AND (x3.id = x0.suba_no ) ) ) from
(("informix".suba_rec x0 join "informix".prog_enr_rec x1 on (x0.id =
x1.id ) )join "informix".prog_enr_rec x2 on (x0.id = x2.id ) )where
(((x0.subs = 'S/A' ) AND (x0.bal_act != '0.00' ) ) AND (x1.subprog IN
('EC' ,'NG' ,'NU' )) ) group by
x0.suba_no ,x0.bal_act ,3 ,x1.prog ,x1.deggrp ,x1.enr_date ,7 having
(x1.enr_date = max(x2.enr_date ) ) ;
Estimated Cost: 19
Estimated # of Rows Returned: 1
1) salchc.x3: INDEX PATH
Filters: salchc.x3.stat = 'R'
(1) Index Keys: id
Lower Index Filter: salchc.x3.id =
informix.suba_rec.suba_no
2) salchc.x4: INDEX PATH
(1) Index Keys: yr sess crs_no cat sec_no (Serial, fragments:
ALL)
Lower Index Filter: ((((salchc.x4.crs_no = salchc.x3.crs_no
AND salchc.x4.sec_no = salchc.x3.sec ) AND salchc.x4.cat =
salchc.x3.cat ) AND salchc.x4.sess = salchc.x3.sess ) AND salchc.x4.yr =
salchc.x3.yr )
ON-Filters:((((salchc.x4.crs_no = salchc.x3.crs_no AND
salchc.x4.sec_no = salchc.x3.sec ) AND salchc.x4.cat = salchc.x3.cat )
AND salchc.x4.sess = salchc.x3.sess ) AND salchc.x4.yr = salchc.x3.yr )
NESTED LOOP JOIN(LEFT OUTER JOIN)
PostJoin-Filters:(salchc.x3.stat = 'R' AND salchc.x3.id =
informix.suba_rec.suba_no )
UDRs in query:
--------------
UDR id : -113
UDR name: equal
QUERY:
------
SELECT ODB_DBName FROM SysMaster:"informix".SysOpenDB WHERE
ODB_SessionID = DBINFO('sessionid') AND ODB_IsCurrent = 'Y'
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) sysmaster:informix.sysopendb: INDEX PATH
Filters: sysmaster:informix.sysopendb.odb_iscurrent = 'Y'
(1) Index Keys: odb_sessionid (desc) odb_odbno
Lower Index Filter: sysmaster:informix.sysopendb.odb_sessionid =
DBINFO ('sessionid')
--
--------------------------------------------
Chris Salch
Programmer/Analyst
LeTourneau University
903-233-3537
