Redbrick Query Optimizer behaving funny.

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 sending to informix-list