Index on partitioned table not used

I have to query a reasonably large table (450M rows) that is partitoned and indexed.

suppose this structure:

• load_num(int)
• cust_id(int)
• ... some more columns ...

The table is partitioned over load_num, around 3 loads go into one partition. (so load_num is not unique within a partition)

There there are three indexes, two of them who have load_num, cust_id as t开发者_如何学编程he first two columns (int that order)

When I issue this query:

select *
from   fact
where  load_num = 100
       and cust_id = 12345

It takes quite long to return, so I hit explain plan and it gets the right partition but then does a FULL table scan on it.

Why isn't oracle using one of the two indexes to do a ROWID scan on the partition to get the rows?

The cust_id should be fairly unique and the statistics on the table are up to date. We're on 10g Entreprise.

Came from MS SQL so I'm not up to speed with Oracle yet.

Thanks in advance,

Gert-Jan

** EDIT: Some anonimized DDL:

CREATE TABLE FACT
(
  LOAD_NUM NUMBER 
... columns ..
, CUSTOMER_ID VARCHAR2(20 BYTE) 
.. columns 
) 
TABLESPACE "TS_A" 
PCTFREE 0 
INITRANS 1 
STORAGE 
( 
  BUFFER_POOL DEFAULT 
) 
PARALLEL 12 
PARTITION BY LIST (LOAD_NUM) 
(
  PARTITION FACT_46 VALUES (46) TABLESPACE FACT_PART_DATA_46 
    COMPRESS  
, PARTITION FACT_52 VALUES (52) TABLESPACE FACT_PART_DATA_52 
    COMPRESS  
, PARTITION FACT_56 VALUES (56) TABLESPACE FACT_PART_DATA_56 
    COMPRESS  
  ... more partitions ...
)CREATE INDEX SOMESCHEMA.FACT_IDX2 ON SOMESCHEMA.FACT (LOAD_NUM ASC, CUSTOMER_ID ASC, OUTSTANDING_ID ASC) 
LOCAL 
(
  PARTITION FACT_DATA_46 
  LOGGING 
  TABLESPACE "FACT_DATA_46" 
  PCTFREE 10 
  INITRANS 2 
  STORAGE 
  ( 
    INITIAL 65536 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    BUFFER_POOL DEFAULT 
  ) 
  NOCOMPRESS 
, PARTITION FACT_DATA_52
  LOGGING 
  TABLESPACE "FACT_DATA_52" 
  PCTFREE 10 
  INITRANS 2 
  STORAGE 
  ( 
    INITIAL 65536 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    BUFFER_POOL DEFAULT 
  ) 
  NOCOMPRESS 
, 
... etc etc ..
)

Why isn't oracle using one of the two indexes to do a ROWID scan on the partition to get the rows?

It's hard to tell exactly why Oracle doesn't use the index, since there is nothing in your setup that would prevent it from doing so.

Most probably, cust_id distribution is skewed so that Oracle considers PARTITION SCAN more efficient.

Could you please try adding the hint explicitly:

SELECT  /*+ INDEX (f FACT_IDX2) */
        *
FROM    fact f
WHERE   load_num = 100
        AND cust_id = 12345

Make sure that it's used in the plan and check that this method is really faster.

Also, please post what does this query return:

SELECT  COUNT(*), COUNT(DECODE(cust_id, 12345, 1))
FROM    fact f
WHERE   load_num = 100