Fw: Fragmented index queries

Hello, IDS v11.570.fc7w3 Aix 6.1 Can anyone shed some light on the below ... I'm thinking the serial scan is because of the function and informix not having any idea of the values at run time .. Would there be a optimizer directive that might force the parallel scan? Any help is greatly appreciated. Peter Peter Logan Senior Database Administrator Phone: 616/878-8309 ----- Forwarded by Peter Logan/Corporate/Spartan on 03/25/2011 08:13 AM ----- From: Bruce Farwell/OTI/Spartan To: Peter Logan/Corporate/Spartan@SpartanStore Date: 03/24/2011 04:35 PM Subject: Re: Fragmented index queries Well, that would be the best, but I'm not after fragment elimination. I already know I'm not going to get that because of the sub select. I want a parallel scan of the fragments, but I only get parallel when the sub select has values. From: Peter Logan/Corporate/Spartan To: Bruce Farwell/OTI/Spartan@SpartanStore Date: 03/24/2011 04:25 PM Subject: Re: Fragmented index queries I'm going to guess it's due to the sub select ... can't determine fragment elimination ... get rid of the sub select and I'm pretty sure you will get what you want .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: Bruce Farwell/OTI/Spartan To: Peter Logan/Corporate/Spartan@SpartanStore Cc: Phil Hahn/Corporate/Spartan@SpartanStore Date: 03/24/2011 04:21 PM Subject: Fragmented index queries Peter, I'm running EIS queries that are using the fragmented indexes on efin_wk_chain_item. Fragments are by week. I've got PDQ turned on. When I give the weeks criteria as a function using weekid(), the query engine does a serial scan of all fragments. When I give the weeks as actual numbers, the query engine does a parallel scan of all fragments. Either way it scans all fragments, but I want it to be parallel at all times. Is there any way to achieve this? Query 1 and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where fiscal_week_id between weekid(TODAY,-8) and weekid(TODAY, -1))) (1) Index Name: whmgr.efin_wk_chn_itm_i2 Index Keys: item_id (Serial, fragments: ALL) Query 2 and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where fiscal_week_id between 201144 and 201151)) (1) Index Name: whmgr.efin_wk_chn_itm_i2 Index Keys: item_id (Parallel, fragments: ALL) Here is the whole query 1, if you want that. select a11.fiscal_week_id fiscal_week_id, a11.item_id item_id, sum(a11.whse_mvmt_qty) WJXBFS1, sum(a11.ext_profit_amt) WJXBFS2, sum(a11.total_sales_amt) WJXBFS3 from efin_wk_chain_item a11, mdse_item a12, mdse_class a13 where a11.item_id = a12.item_id and a12.mdse_class_key = a13.mdse_class_key and (a13.mdse_catgy_key in (1023) and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where fiscal_week_id between weekid(TODAY,-8) and weekid(TODAY, -1))) group by a11.fiscal_week_id, a11.item_id

Try unfolding the sub-query into a join and see what happens. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 25, 2011 at 8:16 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Hello, > > IDS v11.570.fc7w3 > Aix 6.1 > > Can anyone shed some light on the below ... I'm thinking the serial scan > is because of the function and informix not having any idea of the values > at run time .. Would there be a optimizer directive that might force the > parallel scan? Any help is greatly appreciated. > > Peter > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > ----- Forwarded by Peter Logan/Corporate/Spartan on 03/25/2011 08:13 AM > ----- > > From: Bruce Farwell/OTI/Spartan > To: Peter Logan/Corporate/Spartan@SpartanStore > Date: 03/24/2011 04:35 PM > Subject: Re: Fragmented index queries > > Well, that would be the best, but I'm not after fragment elimination. I > already know I'm not going to get that because of the sub select. I want > a parallel scan of the fragments, but I only get parallel when the sub > select has values. > > From: Peter Logan/Corporate/Spartan > To: Bruce Farwell/OTI/Spartan@SpartanStore > Date: 03/24/2011 04:25 PM > Subject: Re: Fragmented index queries > > I'm going to guess it's due to the sub select ... can't determine fragment > elimination ... get rid of the sub select and I'm pretty sure you will get > what you want .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > From: Bruce Farwell/OTI/Spartan > To: Peter Logan/Corporate/Spartan@SpartanStore > Cc: Phil Hahn/Corporate/Spartan@SpartanStore > Date: 03/24/2011 04:21 PM > Subject: Fragmented index queries > > Peter, I'm running EIS queries that are using the fragmented indexes on > efin_wk_chain_item. Fragments are by week. I've got PDQ turned on. > When I give the weeks criteria as a function using weekid(), the query > engine does a serial scan of all fragments. > When I give the weeks as actual numbers, the query engine does a parallel > scan of all fragments. > > Either way it scans all fragments, but I want it to be parallel at all > times. Is there any way to achieve this? > > Query 1 > and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where > fiscal_week_id between weekid(TODAY,-8) and weekid(TODAY, -1))) > > (1) Index Name: whmgr.efin_wk_chn_itm_i2 > > Index Keys: item_id (Serial, fragments: ALL) > > Query 2 > > and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where > fiscal_week_id between 201144 and 201151)) > > (1) Index Name: whmgr.efin_wk_chn_itm_i2 > > Index Keys: item_id (Parallel, fragments: ALL) > > Here is the whole query 1, if you want that. > > select a11.fiscal_week_id fiscal_week_id, > a11.item_id item_id, > sum(a11.whse_mvmt_qty) WJXBFS1, > sum(a11.ext_profit_amt) WJXBFS2, > sum(a11.total_sales_amt) WJXBFS3 > from efin_wk_chain_item a11, > mdse_item a12, > mdse_class a13 > where a11.item_id = a12.item_id and > > a12.mdse_class_key = a13.mdse_class_key > > and (a13.mdse_catgy_key in (1023) > and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where > fiscal_week_id between weekid(TODAY,-8) and weekid(TODAY, -1))) > group by a11.fiscal_week_id, a11.item_id > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec519643bcff7be049f4eb2df

Check the datatype of fiscal_week_id and the datatype of the value being= returned from the function. If they're not alike. it may cause the differ= ence in the in query plans from the optimizer. --IDS v11.570.fc7w3=20 --Aix 6.1=20 --Can anyone shed some light on the below ... I'm thinking the serial scan= =20 --is because of the function and informix not having any idea of the value= s=20 --at run time .. Would there be a optimizer directive that might force the= =20 --parallel scan? Any help is greatly appreciated.=20 --Peter=20 --select a11.fiscal_week_id fiscal_week_id,=20 --a11.item_id item_id,=20 --sum(a11.whse_mvmt_qty) WJXBFS1,=20 --sum(a11.ext_profit_amt) WJXBFS2,=20 --sum(a11.total_sales_amt) WJXBFS3=20 --from efin_wk_chain_item a11,=20 --mdse_item a12,=20 --mdse_class a13=20 --where a11.item_id =3D a12.item_id and=20 --a12.mdse_class_key =3D a13.mdse_class_key=20 --and (a13.mdse_catgy_key in (1023)=20 --and a11.fiscal_week_id in (select fiscal_week_id from fiscal_week where= =20 --fiscal_week_id between weekid(TODAY,-8) and weekid(TODAY, -1)))=20 --group by a11.fiscal_week_id, a11.item_id=20