title: Query not using indexes -
body: <p>Is there any way I can use index on REFERENCETYPE column.Below is the table structure and execution plan.</p>
  
  <pre><code>SQL&gt; desc messaginginbox
   Name                                      Null?    Type
   ----------------------------------------- -------- ---------------------------
   MESSAGINGINBOXID                          NOT NULL VARCHAR2(28)
   REFERENCEID                               NOT NULL VARCHAR2(28)
   REFERENCETYPE                                      VARCHAR2(1)
   LISTINGID                                 NOT NULL VARCHAR2(28)
   CREATEDATE                                         DATE
   LASTUPDATED                               NOT NULL DATE
   UPDATEDBY                                 NOT NULL VARCHAR2(28)
   RENTERLISTINGMANAGERID                             VARCHAR2(28)
   OWNERLISTINGMANAGERID                              VARCHAR2(28)
   OCA                                       NOT NULL NUMBER(38)
  
  SQL&gt; create index idx_MESSAGINGIN_REFERENCE on MESSAGINGINBOX( REFERENCETYPE);
  Index created.
  
  SQL&gt; analyze table MESSAGINGINBOX compute statistics;
  
  Table analyzed.
  
  SQL&gt; select * from MESSAGINGINBOX where referencetype='B';
  
  55 rows selected.
  
  Execution Plan
  -------------------------------------------------------------------------
  | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)|
  -------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |                | 71354 |     9M|   873   (1)|
  |*  1 |  TABLE ACCESS FULL| MESSAGINGINBOX | 71354 |     9M|   873   (1)|
  -------------------------------------------------------------------------
  
  SQL&gt; create bitmap index idx_MESSAGINGIN_REFERENCE
       on MESSAGINGINBOX( REFERENCETYPE);
  
  Index created.
  
  SQL&gt; analyze table MESSAGINGINBOX compute statistics;
  
  Table analyzed.
  
  SQL&gt; select * from MESSAGINGINBOX where referencetype='B';
  
  55 rows selected.
  
  Execution Plan
  -------------------------------------------------------------------------
  | Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)|
  -------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |                | 71354 |     9M|   873   (1)|
  |*  1 |  TABLE ACCESS FULL| MESSAGINGINBOX | 71354 |     9M|   873   (1)|
  -------------------------------------------------------------------------
  
  SQL&gt; select count(*) from MESSAGINGINBOX;
  
    COUNT(*)
  ----------
      142707
  
  SQL&gt; select distinct referencetype from MESSAGINGINBOX;
  
  REFERENCETYPE
  -------------
  I             
  B       
  
  SQL&gt; select count(distinct referencetype) from MESSAGINGINBOX;
  
  COUNT(DISTINCTREFERENCETYPE)
  ----------------------------
                     2
  </code></pre>