Incorrect Query Plan
A user found that a correlated subquery summing from rpl_scs_st_orderfill kept picking the narrower composite index (style, fin_week, fin_year) instead of the more selective (style, colour, size_8, fin_week, fin_year), badly hurting report performance, despite UPDATE STATISTICS MEDIUM/HIGH on the table and columns; he wanted to avoid optimizer directives. Art Kagel suggested rewriting the correlated subquery as a plain join of the two tables with a GROUP BY on the outer columns, and the poster confirmed this fixed the plan. Frank Qu also questioned the composite index design and suggested trying single-column indexes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
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
Try this alternative query:
Select blah1, blah2, ..., blahN, sum(rpl_scs_st_orderfill.value)
from tablex, 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
)
)
group by blah1, blah2, ..., blahN;
Art S. Kagel
----- Original Message -----
From: Yunyao (Fra.... <ids@iiug.org>
At: 6/13 13:08:31
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.
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-----
Fro
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 INDEXix_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 O
Thanks Art - that did the trick!
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
ART KAGEL, ....
Sent: Tuesday, 13 June 2006 07:37 PM
To: ids@iiug.org
Subject: Re: Incorrect Query Plan [6946]
Try this alternative query:
Select blah1, blah2, ..., blahN, sum(rpl_scs_st_orderfill.value)
from tablex, 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
)
)
group by blah1, blah2, ..., blahN;
Art S. Kagel
----- Original Message -----
From: Yunyao (Fra.... <ids@iiug.org>
At: 6/13 13:08:31
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.
************************************************************************
*******
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.
HI, Angus,
The index's efficiency depends on the query and data distribution.
If the query needs access large percent of the data, the index may not
be beneficial.
By the way, did you try the following indexes?
CREATE INDEX idx_style ON informix.rpl_scs_plan_ord_wk(style)
CREATE INDEX idx_color ON informix.rpl_scs_plan_ord_wk(colour)
CREATE INDEX idx_trading_date ON informix.rpl_scs_plan_ord_wk(trading_date)
CREATE INDEX idx_size_8 ON informix.rpl_scs_plan_ord_wk(size_8)
CREATE INDEX idx_fin_year ON informix.rpl_scs_plan_ord_wk(fin_year)
CREATE INDEX idx_fin_month ON informix.rpl_scs_plan_ord_wk(fin_month)
CREATE INDEX idx_fin_week ON informix.rpl_scs_plan_ord_wk(fin_week)
CREATE INDEX idx_plan_loc ON informix.rpl_scs_plan_ord_wk(plan_loc)
Frank Qu
Angus Miller wrote:
>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,
>@