title: Performance tuning about the "ORDER BY" and "LIKE" clause
body: <p>I have 2 tables which have many records  (say both TableA and TableB has about 3,000,000 records).vr2_input is a varchar input parameters enter by the users and I want to get the most 200 largest "dateField" 's TableA records whose stringField like 'vr2_input' .The 2 tables are joined as the following:</p>
  
  <pre><code>select * from(   
     select * from 
          TableA join TableB on TableA.id = TableB.id
          where  TableA.stringField like 'vr2_input' || '%'
          order by  TableA.dateField desc   
  ) where rownum &lt; 201
  </code></pre>
  
  <p>The query is slow , I goggled that and found out that it is because "like" and "order by" involves the full table scan .However , I cannot found a solution to solve the problem . How can I tune this type of SQL? I have already create an index on TableA.stringField  and TableA.dateField but how can I use the index feature in the select statement? The database is oracle 10g. Thanks so much!!</p>
  
  <p>Update : I use iddqd 's suggestion and only select the fields that I want and run the explain plan . It cost about 4 mins to finish the query . IX_TableA_stringField is the index name of the TableA.srv_ref field .I run again the explain plan without the hint , the explain plan still get the same result. </p>
  
  <pre><code>EXPLAIN PLAN FOR
      select * from(
           select   
                   /*+ INDEX(TableB IX_TableA_stringField)*/ 
                    TableA.id,
                      TableA.stringField,
                      TableA.dateField,
                      TableA.someField2,
                     TableA.someField3,
              TableB.someField1,
              TableB.someField2,
              TableB.someField3,
                      from TableA 
                      join TableB  on  TableA.id=TableB.id
                      WHERE TableA.stringField  like '21'||'%'  
                   order by TableA.dateField  desc
          ) where rownum &lt; 201
  
  PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                            
  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
  Plan hash value: 871807846                                                                                                                                                                                                                                                                    
  
  --------------------------------------------------------------------------------------------------------                                                                                                                                                                           
  | Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                      
  --------------------------------------------------------------------------------------------------------                                                                                                                                                                           
  |   0 | SELECT STATEMENT                |                      |   200 | 24000 |  3293   (1)| 00:00:18 |                                                                                                                                                                   
  |*  1 |  COUNT STOPKEY                  |                      |       |       |            |          |                                                                                                                                                                                   
  |   2 |   VIEW                          |                      |  1397 |   163K|  3293   (1)| 00:00:18 |                                                                                                                                                                             
  |*  3 |    SORT ORDER BY STOPKEY        |                      |  1397 | 90805 |  3293   (1)| 00:00:18 |                                                                                                                                                              
  |   4 |     NESTED LOOPS                |                      |  1397 | 90805 |  3292   (1)| 00:00:18 |                                                                                                                                                                      
  |   5 |      TABLE ACCESS BY INDEX ROWID| TableA       |  1397 | 41910 |   492   (1)| 00:00:03 |                                                                                                                                                 
  |*  6 |       INDEX RANGE SCAN          | IX_TableA_stringField |  1397 |       |     6   (0)| 00:00:01 |                                                                                                                                                         
  |   7 |      TABLE ACCESS BY INDEX ROWID| TableB      |     1 |    35 |     2   (0)| 00:00:01 |                                                                                                                                                      
  |*  8 |       INDEX UNIQUE SCAN         | PK_TableB   |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                            
  --------------------------------------------------------------------------------------------------------                                                                                                                                                                           
  
  Predicate Information (identified by operation id):                                                                                                                                                                                                                                      
  ---------------------------------------------------                                                                                                                                                                                                                                             
  
     1 - filter(ROWNUM&lt;201)                                                                                                                                                                                                                                                                      
     3 - filter(ROWNUM&lt;201)                                                                                                                                                                                                                                                                      
     6 - access("TableA"."stringField" LIKE '21%')                                                                                                                                                                                                                                                 
         filter("TableA"."stringField" LIKE '21%')                                                                                                                                                                                                                                                     
     8 - access(TableA"."id"="TableB"."id")       
  </code></pre>