IDS Forum

RE: Incorrect Query Plan

Posted By: Angus Miller
Date: Wednesday, 14 June 2006, at 2:48 a.m.

In Response To: Re: Incorrect Query Plan (Yunyao (Frank) Qu)

Thanks Frank, below are the complete scripts. Please can you elaborate
on a better desing for the indexes. The sql gets run for a report, the
reason there for the 3 composite indexes is that queries in general are
filtering by either:

style, fin_year, fin_week
style, colour, fin_year, fin_week
style, colour, size_8, fin_year, fin_week

Here is the info:

SQL statement that needs help
-----------------------------
select rpl_scs_plan_ord_wk.style,

rpl_scs_plan_ord_wk.colour,

rpl_scs_plan_ord_wk.size_8,

rpl_scs_plan_ord_wk.fin_year,

rpl_scs_plan_ord_wk.fin_month,

case rpl_scs_plan_ord_wk.fin_month

when 1 then 'Jul'

when 2 then 'Aug'

when 3 then 'Sept'

when 4 then 'Oct'

when 5 then 'Nov'

when 6 then 'Dec'

when 7 then 'Jan'

when 8 then 'Feb'

when 9 then 'Mar'

when 10 then 'Apr'

when 11 then 'May'

when 12 then 'Jun'

end monthname,

rpl_scs_plan_ord_wk.supplier supplier,

sum(rpl_scs_plan_ord_wk.rp_planned_ord_qty) planorders,

case

when

(select sum(a.rp_planned_ord_qty)

from rpl_scs_plan_ord_wk a

where a.style = 197895

and a.colour = 'N3'

and a.fin_year = 2006

and a.fin_month = rpl_scs_plan_ord_wk.fin_month) > 0 then

sum(rpl_scs_plan_ord_wk.rp_planned_ord_qty) /

(select sum(a.rp_planned_ord_qty)

from rpl_scs_plan_ord_wk a

where a.style = 197895

and a.colour = 'N3'

and a.fin_year = 2006

and a.fin_month = rpl_scs_plan_ord_wk.fin_month) * 100

else

sum(0)

end contr,

sum(rpl_scs_plan_ord_wk.orig_order_qty) origorderqty,

sum(rpl_scs_plan_ord_wk.asn_qty) asnqty,

(select sum(rpl_scs_st_orderfill.asn_qty)

from rpl_scs_st_orderfill

where rpl_scs_st_orderfill.fin_year = rpl_scs_plan_ord_wk.fin_year

and rpl_scs_st_orderfill.style = rpl_scs_plan_ord_wk.style

and rpl_scs_st_orderfill.colour = rpl_scs_plan_ord_wk.colour

and rpl_scs_st_orderfill.size_8 = rpl_scs_plan_ord_wk.size_8

and rpl_scs_st_orderfill.fin_week between

(select min(fin_week)

from dim_calendar

where fin_year = rpl_scs_plan_ord_wk.fin_year

and fin_month = rpl_scs_plan_ord_wk.fin_month) and

(select max(fin_week)

from dim_calendar

where fin_year = rpl_scs_plan_ord_wk.fin_year

and fin_month = rpl_scs_plan_ord_wk.fin_month)) orderfillasn,

(select sum(rpl_scs_st_orderfill.di_qty)

from rpl_scs_st_orderfill

where rpl_scs_st_orderfill.fin_year = rpl_scs_plan_ord_wk.fin_year

and rpl_scs_st_orderfill.style = rpl_scs_plan_ord_wk.style

and rpl_scs_st_orderfill.colour = rpl_scs_plan_ord_wk.colour

and rpl_scs_st_orderfill.size_8 = rpl_scs_plan_ord_wk.size_8

and rpl_scs_st_orderfill.fin_week between

(select min(fin_week)

from dim_calendar

where fin_year = rpl_scs_plan_ord_wk.fin_year

and fin_month = rpl_scs_plan_ord_wk.fin_month) and

(select max(fin_week)

from dim_calendar

where fin_year = rpl_scs_plan_ord_wk.fin_year

and fin_month = rpl_scs_plan_ord_wk.fin_month)) orderfilldi
from rpl_scs_plan_ord_wk where rpl_scs_plan_ord_wk.fin_year = 2006 and
rpl_scs_plan_ord_wk.style = 197895 and rpl_scs_plan_ord_wk.colour =
'N3' and rpl_scs_plan_ord_wk.fin_month >=

(select min(fin_month)

from dim_calendar

where fin_year = 2006

and old_season = 'W')
and rpl_scs_plan_ord_wk.fin_month <=

(select max(last_completed_mt)

from rpl_scs_plan_ord_wk

where style = 197895

and fin_year = 2006

and trading_season = 'W')
group by rpl_scs_plan_ord_wk.style,

rpl_scs_plan_ord_wk.colour,

rpl_scs_plan_ord_wk.size_8,

rpl_scs_plan_ord_wk.fin_year,

rpl_scs_plan_ord_wk.fin_month,

rpl_scs_plan_ord_wk.supplier,

12,

13

Table rpl_scs_plan_ord_wk
-------------------------
CREATE TABLE rpl_scs_plan_ord_wk (

style INTEGER NOT NULL DEFAULT 0,

colour CHAR(2) NOT NULL DEFAULT 0,

size_5 CHAR(5) DEFAULT 0,

size_8 CHAR(8) NOT NULL DEFAULT 0,

plan_loc CHAR(10) NOT NULL DEFAULT 0,

fin_year SMALLINT NOT NULL DEFAULT 0,

fin_month SMALLINT NOT NULL DEFAULT 0,

fin_week SMALLINT NOT NULL DEFAULT 0,

trading_date DATE NOT NULL DEFAULT 0,

trading_season CHAR(1) NOT NULL DEFAULT 0,

msr_indicator CHAR(1) DEFAULT 0,

constr_avail_ind SMALLINT DEFAULT 0,

supplier INTEGER DEFAULT 0,

sales_qty INTEGER DEFAULT 0,

stock_qty INTEGER DEFAULT 0,

intake_qty INTEGER DEFAULT 0,

stk_transit_qty INTEGER DEFAULT 0,

asn_qty INTEGER DEFAULT 0,

rp_planned_ord_qty INTEGER DEFAULT 0,

auto_apprv_ord_qty INTEGER DEFAULT 0,

date_last_updated DATE DEFAULT 0,

proj_style_ord_qty INTEGER DEFAULT 0,

proj_stycol_ord_qty INTEGER DEFAULT 0,

stock_eom_qty INTEGER DEFAULT 0,

sit_eom_qty INTEGER DEFAULT 0,

orig_order_qty INTEGER DEFAULT 0,

last_completed_mt SMALLINT DEFAULT 0,

last_completed_yr SMALLINT DEFAULT 0
)
;

CREATE UNIQUE INDEX rpl_scs_plan_ord_wk1 ON
informix.rpl_scs_plan_ord_wk(style, colour, plan_loc, size_8, fin_week,
fin_year) ; CREATE INDEX rpl_scs_plan_ord_wk2 ON
informix.rpl_scs_plan_ord_wk(trading_date, style, colour, size_8) ;
CREATE INDEX rpl_scs_plan_ord_wk3 ON
informix.rpl_scs_plan_ord_wk(fin_year, fin_month, style, plan_loc,
colour) ; CREATE INDEX rpl_scs_plan_ord_wk4 ON
informix.rpl_scs_plan_ord_wk(fin_year, trading_season, style, plan_loc,
colour) ;

Table rpl_scs_st_orderfill
--------------------------
CREATE TABLE informix.rpl_scs_st_orderfill (

di_id INTEGER NOT NULL DEFAULT 0,

style INTEGER NOT NULL DEFAULT 0,

colour CHAR(2) NOT NULL DEFAULT 0,

size_8 CHAR(8) NOT NULL DEFAULT 0,

store SMALLINT NOT NULL DEFAULT 0,

fin_week SMALLINT NOT NULL DEFAULT 0,

fin_year SMALLINT NOT NULL DEFAULT 0,

trading_date DATE NOT NULL DEFAULT 0,

di_date DATE DEFAULT 0,

di_qty INTEGER DEFAULT 0,

di_val DECIMAL(10, 2) DEFAULT 0,

asn_no DECIMAL(13, 0) DEFAULT 0,

asn_date DATE DEFAULT 0,

asn_qty INTEGER DEFAULT 0,

asn_val DECIMAL(10, 2) DEFAULT 0,

di_curr_qty INTEGER DEFAULT 0,

di_curr_val DECIMAL(10, 2) DEFAULT 0,

asn_curr_qty INTEGER DEFAULT 0,

asn_curr_val DECIMAL(10, 2) DEFAULT 0,

date_last_updated DATE DEFAULT 0
)
;

CREATE UNIQUE INDEX ix_rpl_ix1 ON informix.rpl_scs_st_orderfill(di_id,
style, colour, size_8, store, fin_week, fin_year) ; CREATE INDEX
ix_rpl_ix2 ON informix.rpl_scs_st_orderfill(style, fin_week, fin_year) ;
CREATE INDEX ix_rpl_ix3 ON informix.rpl_scs_st_orderfill(style, colour,
fin_week, fin_year) ; CREATE INDEX ix_rpl_ix4 ON
informix.rpl_scs_st_orderfill(style, colour, size_8, fin_week, fin_year)
;

-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Yunyao (Fra....
Sent: Tuesday, 13 June 2006 07:03 PM
To: ids@iiug.org
Subject: Re: Incorrect Query Plan [6945]

It would be lot easier for people to have the full scripts to look at
at your case.
BTW, the indexes designed look not pretty(or efficient) to me.
Frankly, probably there is no best plan in all scenarios for this set of

indexes.

Thanks,
Frank

Angus Miller wrote:

>Hi
>
>We have a table with 3 composite indexes like so:
>
>CREATE INDEX ix_rpl_ix2 ON rpl_scs_st_orderfill(
>
>style,
>
>fin_week,
>
>fin_year);
>
>CREATE INDEX ix_rpl_ix3 ON rpl_scs_st_orderfill(
>
>style,
>
>colour,
>
>fin_week,
>
>fin_year);
>
>CREATE INDEX ix_rpl_ix4 ON rpl_scs_st_orderfill(
>
>style,
>
>colour,
>
>size_8,
>
>fin_week,
>
>fin_year);
>
>I have a query with a subquery like so:
>
>Select
>
>blah,
>
>blah,
>....
>
>(select sum(rpl_scs_st_orderfill.value)
>
>from rpl_scs_st_orderfill
>
>where rpl_scs_st_orderfill.style = tablex.style
>
>and rpl_scs_st_orderfill.colour = tablex.colour
>
>and rpl_scs_st_orderfill.size_8 = tablex.size_8
>
>and fin_week between
>
>(select min(fin_week)
>
>from dim_calendar
>
>where fin_year = tablex.fin_year
>
>and fin_month = tablex.fin_month) and
>
>(select max(fin_week)
>
>from dim_calendar
>
>where fin_year = tablex.fin_year
>
>and fin_month = tablex.fin_month))
>....
>>From tablex
>....
>
>I have run update statistics as follows for this table:
>
>UPDATE STATISTICS MEDIUM FOR TABLE rpl_scs_st_orderfill;
>UPDATE STATISTICS HIGH FOR TABLE rpl_scs_st_orderfill(style);
>UPDATE STATISTICS HIGH FOR TABLE rpl_scs_st_orderfill(colour);
>UPDATE STATISTICS HIGH FOR TABLE rpl_scs_st_orderfill(size_8);
>UPDATE STATISTICS HIGH FOR TABLE rpl_scs_st_orderfill(fin_week);
>UPDATE STATISTICS HIGH FOR TABLE rpl_scs_st_orderfill(fin_year);
>
>PROBLEM: The query plan for the subquery uses index ix_rpl_ix2 instead
>of ix_rpl_ix4 which makes a huge difference in perfomance. I have tried

>everything I can think of, but short of using optimizer directives I
>cannot get the subquery to use index ix_rpl_ix4
>
>Any ideas?
>
>Regards
>Angus
>

>-----------------------------------------------------------------------
---------
>Please note: This e-mail and its contents are subject to a disclaimer
>which can be viewed at http://www.woolworths.co.za/disclaimer. Should
>you be unable to access the link please e-mail
disclaimer@woolworths.co.za
>and a copy of the disclaimer will be e-mailed to you.
>
>

>***********************************************************************
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>

--
Yunyao "Frank" Qu
Computer Sciences Corporation(CSC)
NOAA/CLASS, (301)817-4696

************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
--------------------------------------------------------------------------------
Please note: This e-mail and its contents are subject to a disclaimer
which can be viewed at http://www.woolworths.co.za/disclaimer. Should
you be unable to access the link please e-mail disclaimer@woolworths.co.za
and a copy of the disclaimer will be e-mailed to you.

Messages In This Thread

• Incorrect Query Plan
  Angus Miller -- Tuesday, 13 June 2006, at 10:37 a.m.
  • Re: Incorrect Query Plan
    Yunyao (Frank) Qu -- Tuesday, 13 June 2006, at 1:03 p.m.
    • Re: Incorrect Query Plan
      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Tuesday, 13 June 2006, at 1:36 p.m.
      • RE: Incorrect Query Plan
        Angus Miller -- Wednesday, 14 June 2006, at 3:10 a.m.
    • RE: Incorrect Query Plan
      Angus Miller -- Wednesday, 14 June 2006, at 2:32 a.m.
    • RE: Incorrect Query Plan
      Angus Miller -- Wednesday, 14 June 2006, at 2:48 a.m.
      • Re: Incorrect Query Plan
        Yunyao (Frank) Qu -- Thursday, 15 June 2006, at 10:22 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.