Redbrick List Forum

Redbrick Query Optimizer behaving funny.

Posted By: Asheesh Rastogi
Date: Tuesday, 21 October 2003, at 11:19 a.m.

Hi All,
Today I saw very funny behaviour of Redbrick
query optimizer where the order in which tables are
listed in the from clause is controlling the indexes
being used. I am sending the results of explain for
both the conditions:

CONDITION 1:
=============
RISQL> explain SELECT PERIOD_KEY, IA_SK_LOCATION_KEY,
IA_SK_SKU_KEY, DEPARTMENT_NUMBER, BRAND_NUMBER,
STAGE_STATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM,
STAGE_STATISTICAL_ACCOUNTS.STATISTICAL_QUANTITY FROM
STAGE_STATISTICAL_ACCOUNTS, DIM_SKU,
DIM_LOCATION_CC_COMPANY WHERE
STAGE_STATISTICAL_ACCOUNTS.BRAND_NUMBER IS NOT NULL
AND DIM_SKU.ia_sku_level='De-BRAND' AND
DIM_SKU.ia_sku_level <> 'DSKU' AND
STAGE_STATISTICAL_ACCOUNTS.DEPARTMENT_NUMBER IS NOT
NULL AND AND AND STATISTICAL_ACCOUNTS.COST_CENTER =
DIM_LOCATION_CC_COMPANY.HS_COST_CENTER_NUMBER AND
STAGE_STATISTICAL_ACCOUNTS.BRAND_NUMBER =
DIM_SKU.HS_BRAND_NUMBER AND
STAGE_STATISTICAL_ACCOUNTS.DEPARTMENT_NUMBER =
DIM_SKU.HS_DEPARTMENT_NUMBER AND
TRIM(STATISTICAL_LINE_ITEM) IN ( 'SQFTLS' , 'SQFTSS'
);
EXPLANATION

[

- EXECUTE (ID: 0) 3 Table locks (table, type):
(DIM_SKU, Read_Only), (STAGE_STA
TISTICAL_ACCOUNTS, Read_Only),
(DIM_LOCATION_CC_COMPANY, Read_Only)
--- HASH 1-1 MATCH (ID: 1) Join type: InnerJoin;

----- FUNCTIONAL JOIN (ID: 2) 1 tables: DIM_SKU

------- BTREE 1-1 MATCH (ID: 3) Join type: InnerJoin;
Index(s): [Table: DIM_SKU
, Index: DIM_SKU_DEPT_NUM_TI]

--------- TABLE SCAN (ID: 4) Table:
STAGE_STATISTICAL_ACCOUNTS, Predicate: ((((
(STAGE_STATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM) ) =
('SQFTSS') ) || (((STAGE
_STATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM) ) =
('SQFTLS') ) ) && ((STAGE_STAT
ISTICAL_ACCOUNTS.DEPARTMENT_NUMBER) <not isnull> ) )
&& ((STAGE_STATISTICAL_ACC
OUNTS.BRAND_NUMBER) <not isnull> )

----- TABLE SCAN (ID: 5) Table:
DIM_LOCATION_CC_COMPANY, Predicate: <none>
]


Note that no predicate is being applied to table
DIM_SKU and the TARGET index on the IA_SKU_LEVEL
column doesnot even figure in the query plan.

CONDITION 2:
===============
RISQL> explain SELECT PERIOD_KEY, IA_SK_LOCATION_KEY,
IA_SK_SKU_KEY, DEPARTMENT_NUMBER, BRAND_NUMBER,
STAGE_STATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM,
STAGE_STATISTICAL_ACCOUNTS.STATISTICAL_QUANTITY FROM
DIM_SKU, STAGE_STATISTICAL_ACCOUNTS,
DIM_LOCATION_CC_COMPANY WHERE
STAGE_STATISTICAL_ACCOUNTS.BRAND_NUMBER IS NOT NULL
AND
DIM_SKU.ia_sku_level='De-BRAND' AND
STAGE_STATISTICAL_ACCOUNTS.DEPARTMENT_NUMBER IS NOT
NULL AND STAGE_STATISTICAL_ACCOUNTS.COST_CENTER =
DIM_LOCATION_CC_COMPANY.HS_COST_CENTER_NUMBER AND
STAGE_STATISTICAL_ACCOUNTS.BRAND_NUMBER =
DIM_SKU.HS_BRAND_NUMBER AND
STAGE_STATISTICAL_ACCOUNTS.DEPARTMENT_NUMBER =
DIM_SKU.HS_DEPARTMENT_NUMBER AND
TRIM(STATISTICAL_LINE_ITEM) IN ( 'SQFTLS' , 'SQFTSS'
);
EXPLANATION

[

- EXECUTE (ID: 0) 3 Table locks (table, type):
(DIM_SKU, Read_Only), (STAGE_STA
TISTICAL_ACCOUNTS, Read_Only),
(DIM_LOCATION_CC_COMPANY, Read_Only)
--- HASH 1-1 MATCH (ID: 1) Join type: InnerJoin;

----- HASH 1-1 MATCH (ID: 2) Join type: InnerJoin;

------- FUNCTIONAL JOIN (ID: 3) 1 tables: DIM_SKU

--------- TARGET SCAN (ID: 4) Table: DIM_SKU,
Predicate: (DIM_SKU.IA_SKU_LEVEL)
= ('De-BRAND ') ; Num indexes: 1
Index(s): Index: DIM_SKU
_LEVEL_TI

------- TABLE SCAN (ID: 5) Table:
STAGE_STATISTICAL_ACCOUNTS, Predicate: (((((S
TAGE_STATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM) ) =
('SQFTSS') ) || (((STAGE_S
TATISTICAL_ACCOUNTS.STATISTICAL_LINE_ITEM) ) =
('SQFTLS') ) ) && ((STAGE_STATIS
TICAL_ACCOUNTS.DEPARTMENT_NUMBER) <not isnull> ) ) &&
((STAGE_STATISTICAL_ACCOU
NTS.BRAND_NUMBER) <not isnull> )

----- TABLE SCAN (ID: 6) Table:
DIM_LOCATION_CC_COMPANY, Predicate: <none>
]

Note that once I move the DIM_SKU table to the start
of list TARGET index on IA_SKU_LEVEL column comes into
picture.
This behaviour could result in a performance
difference of days for this query.
I thought that redbrick has one of the better query
analyzers.Could somebody explain this strange
behaviour. I am running Redbrick 6.11 on AIX4.3.

TIA,
Asheesh.


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

Messages In This Thread

• Redbrick Query Optimizer behaving funny.
  Asheesh Rastogi -- Tuesday, 21 October 2003, at 11:19 a.m.
  • Re: Redbrick Query Optimizer behaving funny.
    Rob Vorbroker -- Tuesday, 21 October 2003, at 12:02 p.m.
    • Re: Redbrick Query Optimizer behaving funny.
      Asheesh Rastogi -- Wednesday, 22 October 2003, at 1:42 a.m.
  • Re: Redbrick Query Optimizer behaving funny.
    ADAM MCGUINNESS -- Thursday, 23 October 2003, at 1:20 a.m.
    • Re: Redbrick Query Optimizer behaving funny.
      Asheesh Rastogi -- Thursday, 23 October 2003, at 7:28 a.m.
      • Re: Re: Redbrick Query Optimizer behaving funny.
        ADAM MCGUINNESS -- Thursday, 23 October 2003, at 6:47 p.m.
        • Re: Re: Redbrick Query Optimizer behaving funny.
          Asheesh Rastogi -- Tuesday, 4 November 2003, at 12:12 a.m.

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.