windows-1252?Q52=65=3A=20=41=20=76=65=72=79=20=73=

Looks like UDR # -113 is broken. It seems to be used to satisfy the "bucket = 5" clause and it's returning invalid responses. Art S. Kagel ----- Original Message ----- From: Chris Salch <ids@iiug.org> To: ids@iiug.org At: 11/07 20:09:59 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 ) @