Oracle Database Discussions

Hi all,

When I used Index Range Scan Descending hint for two select statements, it worked with one and not with the other. Below is what I got.

Any clarification is appreciated.

Oracle 11g R1
Windows 2003

create table try ( a number constraint try_pk primary key , b varchar2(5), c date )

create index indexb on try ( b );

create index indexc on try ( c );

insert into try  values ( 1, 'A', to_date('01-01-2010','dd-mm-yyyy')) ; 

insert into try  values ( 2, 'B', to_date('01-01-2009','dd-mm-yyyy')) ;

insert into try  values ( 3, 'C', to_date('01-01-2008','dd-mm-yyyy')) ;

insert into try  values ( 4, 'B', to_date('01-10-2008','dd-mm-yyyy')) ;
 
analyze table try compute statistics;

-- the optimizer with this select statement uses the Index Range Scan Descending  
select /*+ index_desc(t indexc) */ 
 * from try t where c < sysdate order by c desc
 
-- the optimizer with this select statement does NOT use the Index Range Scan Descending  
select /*+ index_desc(t indexb) */ 
 * from try t where c < sysdate order by b desc


SQL> select /*+ index_desc(t indexc) */
  2   * from try t where c < sysdate order by c desc ;

         A B     C
---------- ----- ---------
         1 A     01-JAN-10
         2 B     01-JAN-09
         4 B     01-OCT-08
         3 C     01-JAN-08


Execution Plan
----------------------------------------------------------
Plan hash value: 4223430712

--------------------------------------------------------------------------------

-------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim

e     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     4 |    40 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | TRY    |     4 |    40 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN DESCENDING| INDEXC |     4 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

SQL> select /*+ index_desc(t indexb) */
  2   * from try t where c < sysdate order by b desc
  3  ;

         A B     C
---------- ----- ---------
         3 C     01-JAN-08
         4 B     01-OCT-08
         2 B     01-JAN-09
         1 A     01-JAN-10

Execution Plan
----------------------------------------------------------
Plan hash value: 3140191737

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    40 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |     4 |    40 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TRY  |     4 |    40 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------