title: Index on partitioned table not used
body: <p>I have to query a reasonably large table (450M rows) that is partitoned and indexed.</p>
  
  <p>suppose this structure:</p>
  
  <ul>
  <li>load_num(int)</li>
  <li>cust_id(int)</li>
  <li>... some more columns ...</li>
  </ul>
  
  <p>The table is partitioned over load_num, around 3 loads go into one partition. (so load_num is not unique within a partition)</p>
  
  <p>There there are three indexes, two of them who have load_num, cust_id as the first two columns (int that order) </p>
  
  <p>When I issue this query: </p>
  
  <pre><code>select *
  from   fact
  where  load_num = 100
         and cust_id = 12345
  </code></pre>
  
  <p>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. </p>
  
  <p>Why isn't oracle using one of the two indexes to do a ROWID scan on the partition to get the rows?</p>
  
  <p>The cust_id should be fairly unique and the statistics on the table are up to date. We're on 10g Entreprise.</p>
  
  <p>Came from MS SQL so I'm not up to speed with Oracle yet. </p>
  
  <p>Thanks in advance,</p>
  
  <p>Gert-Jan</p>
  
  <p>** EDIT: Some anonimized DDL:</p>
  
  <pre><code>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 ..
  )
  </code></pre>