title: Oracle is not using the index
body: <p>I have a table A with 1 million rows. I have an index for the <code>poliespa</code> column with '03 or '04' values.</p>
  
  <pre><code>CREATE INDEX A21 ON A(POLIESPA);
  </code></pre>
  
  <p>My query:</p>
  
  <pre><code>     SELECT polinpol,
         policdde,
         polipext,
         polidext,
         DECODE (polipext,  'T', polipdmx,  'A', polipdmx,  polipdix),
         politipo,
         polipdtu,
         DECODE (TO_NUMBER (RTRIM (LTRIM (polivmrd))), 0, 0, polivmrd),
         RTRIM (LTRIM (polipdtx)),
         polifeca
    FROM A
   WHERE poliespa = '03';
  </code></pre>
  
  <p>The explain plan for this SQL query makes a full table scan:</p>
  
  <pre><code>Plan hash value: 3450718889
  
  ----------------------------------------------------------------------------
  | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |        |   369K|    14M|  6615   (3)| 00:00:01 |
  |*  1 |  TABLE ACCESS FULL| A      |   369K|    14M|  6615   (3)| 00:00:01 |
  ----------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("POLIESPA"='03')
  </code></pre>
  
  <p>Blocks - 41735:</p>
  
  <pre><code>SELECT COUNT (
             DISTINCT (SUBSTR (DBMS_ROWID.rowid_to_restricted (ROWID, 1), 1, 8)))
             blocks
    FROM A;
  </code></pre>
  
  <p>Clustering:</p>
  
  <pre><code>SELECT index_name, clustering_factor
    FROM user_indexes
   WHERE index_name LIKE 'A21%'
  
  INDEX_NAME  CLUSTERING_FACTOR
  -----------------
  A21         79247
  
  1 row selected.
  </code></pre>
  
  <p>Can anyone help me to understand why the optimizer doesn't use the index or why this is the best approach? Thanks</p>
  
  <p>EDIT: </p>
  
  <pre><code>select count(*) from A
  where poliespa = '03';
  
  select count(*) from A
  where poliespa = '04';
  
    COUNT(*)
  ----------
      221379
  1 row selected.
  
    COUNT(*)
  ----------
      517095
  1 row selected.
  </code></pre>