12 Replies Latest reply: Apr 6, 2010 12:34 PM by Girish Sharma RSS

    Usage of Index Range Scan Descending

    ahb72
      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 |
      ---------------------------------------------------------------------------
        • 1. Re: Usage of Index Range Scan Descending
          729338
          Hi,

          Have you tried running both queries without providing the hint. Let us know what is the explain without hints.

          Regards
          • 2. Re: Usage of Index Range Scan Descending
            Girish Sharma
            "-- 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"

            Yes, it is working right, because only using the index hint in the query; not necessarily that CBO will index scan. In this case you are using indexb and on which column indexb is? It is on b varchar2; while you are using C (another column) in your where clause. Index scan (if CBO think that COST of query will be less) will only be used if "indexed" column is the part of where clause.

            If you wish to use indexb then your query should be something like this:

            select /*+ index_desc(t indexb) */
            from try t where b='Any Value' order by b desc;

            Now it will use index scan.

            HTH
            Girish Sharma
            • 3. Re: Usage of Index Range Scan Descending
              ahb72
              Hi skvaish,

              Thanks for your reply.

              Without hints, I get the same results.

              If you just copy paste the commands above in sqlplus, you can easily test it.
              SQL> ed
              Wrote file afiedt.buf
              
                1* select  * from try t where c < sysdate order by b desc
                2  /
              
                       A B     C
              ---------- ----- ---------
                       3 C     01-JAN-08
                       4 B     01-OCT-08
                       2 B     01-JAN-09
                       1 A     01-JAN-10
              
              ---------------------------------------------------------------------------
              | 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 |
              ---------------------------------------------------------------------------
              
              
              SQL> ed
              Wrote file afiedt.buf
              
                1* select  * from try t where c < sysdate order by c desc
              SQL> /
                       A B     C
              ---------- ----- ---------
                       1 A     01-JAN-10
                       2 B     01-JAN-09
                       4 B     01-OCT-08
                       3 C     01-JAN-08
              
              ---------------------------------------------------------------------------------------
              
              | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
              
              ---------------------------------------------------------------------------------------
              
              |   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 |
              
              ---------------------------------------------------------------------------------------
              • 4. Re: Usage of Index Range Scan Descending
                ahb72
                Hi Girish,

                I got your point, thanks a lot.

                Also, from your clarification, I found out that the hit /*+ index_desc(t indexb) */ was cheaper than the hint /*+ index(t indexb) */ in that case. That was interesting for me.
                SQL> ed
                Wrote file afiedt.buf
                
                  1  select  /*+ index(t indexb) */
                  2  *
                  3* from try t where b like '%%' order by b desc
                SQL> /
                
                ---------------------------------------------------------------------------------------
                | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                
                ---------------------------------------------------------------------------------------
                
                |   0 | SELECT STATEMENT             |        |     1 |    10 |     3  (34)| 00:00:01 |
                
                |   1 |  SORT ORDER BY               |        |     1 |    10 |     3  (34)| 00:00:01 |
                
                |   2 |   TABLE ACCESS BY INDEX ROWID| TRY    |     1 |    10 |     2   (0)| 00:00:01 |
                
                |*  3 |    INDEX FULL SCAN           | INDEXB |     1 |       |     1   (0)| 00:00:01 |
                
                ---------------------------------------------------------------------------------------
                
                
                SQL> ed
                Wrote file afiedt.buf
                
                  1  select  /*+ index_desc(t indexb) */
                  2  *
                  3* from try t where b like '%%' order by b desc
                SQL> /
                
                --------------------------------------------------------------------------------------
                
                | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                
                --------------------------------------------------------------------------------------
                
                |   0 | SELECT STATEMENT            |        |     1 |    10 |     2   (0)| 00:00:01 |
                
                |   1 |  TABLE ACCESS BY INDEX ROWID| TRY    |     1 |    10 |     2   (0)| 00:00:01 |
                
                |*  2 |   INDEX FULL SCAN DESCENDING| INDEXB |     1 |       |     1   (0)| 00:00:01 |
                
                --------------------------------------------------------------------------------------
                • 5. Re: Usage of Index Range Scan Descending
                  Jonathan Lewis
                  Girish Sharma wrote:
                  Index scan (if CBO think that COST of query will be less) will only be used if "indexed" column is the part of where clause.
                  This is a frequently repeated error.
                  Oracle is able to use an index for an "order by" even if the index columns don't appear in the "where" clause
                  The problem is that the hint in this example could lead to Oracle producing the wrong results. (It's the same problem as Full Index Scan and Fast Full Index Scans )
                  If you wish to use indexb then your query should be something like this:

                  select /*+ index_desc(t indexb) */
                  from try t where b='Any Value' order by b desc;
                  But only if he wants the rows for 'Any Value' - which isn't the original query wanted.

                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  http://www.jlcomp.demon.co.uk

                  To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                  {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                  fixed format
                  .
                  
                  There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                  
                  +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
                  Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 6. Re: Usage of Index Range Scan Descending
                    Jonathan Lewis
                    ahb72 wrote:
                    Hi Girish,

                    I got your point, thanks a lot.

                    Also, from your clarification, I found out that the hit /*+ index_desc(t indexb) */ was cheaper than the hint /*+ index(t indexb) */ in that case. That was interesting for me.
                    1  select  /*+ index(t indexb) */
                    2  *
                    3* from try t where b like '%%' order by b desc
                    SQL> /
                    
                    ---------------------------------------------------------------------------------------
                    | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    
                    ---------------------------------------------------------------------------------------
                    
                    |   0 | SELECT STATEMENT             |        |     1 |    10 |     3  (34)| 00:00:01 |
                    
                    |   1 |  SORT ORDER BY               |        |     1 |    10 |     3  (34)| 00:00:01 |
                    
                    |   2 |   TABLE ACCESS BY INDEX ROWID| TRY    |     1 |    10 |     2   (0)| 00:00:01 |
                    
                    |*  3 |    INDEX FULL SCAN           | INDEXB |     1 |       |     1   (0)| 00:00:01 |
                    
                    ---------------------------------------------------------------------------------------
                    
                    
                    1  select  /*+ index_desc(t indexb) */
                    2  *
                    3* from try t where b like '%%' order by b desc
                    SQL> /
                    
                    --------------------------------------------------------------------------------------
                    
                    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    
                    --------------------------------------------------------------------------------------
                    
                    |   0 | SELECT STATEMENT            |        |     1 |    10 |     2   (0)| 00:00:01 |
                    
                    |   1 |  TABLE ACCESS BY INDEX ROWID| TRY    |     1 |    10 |     2   (0)| 00:00:01 |
                    
                    |*  2 |   INDEX FULL SCAN DESCENDING| INDEXB |     1 |       |     1   (0)| 00:00:01 |
                    
                    --------------------------------------------------------------------------------------
                    Not surprising - in one case you are asking for the data in descending order, but insisting that Oracle first access it in ascending order, so it has to sort it before reporting. In the other case you are telling Oracle to access it in descending order, which means it doesn't have to be sorted before being reported.

                    Note the 'sort order by' line in the first plan, which is where the extra cost appears.

                    Regards
                    Jonathan Lewis
                    http://jonathanlewis.wordpress.com
                    http://www.jlcomp.demon.co.uk

                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                    fixed format
                    .
                    
                    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                    
                    +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
                    Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                    • 7. Re: Usage of Index Range Scan Descending
                      orawiss
                      Thanks Jonathan,

                      Edited by: Orawiss on Apr 6, 2010 3:08 PM
                      • 8. Re: Usage of Index Range Scan Descending
                        Girish Sharma
                        Hi Sir,

                        Thanks for correcting me. Here my two doubts:

                        (A) If CBO thinks that COST of query will be less if i (CBO) use Full table scan in compare to index scan; will it still use index scan; just because of we have used index hint in the query?

                        (B) I wish to see an example/link in which Oracle is producing the wrong results please; as you are saying "The problem is that the hint in this example could lead to Oracle producing the wrong results." and if it is producing the wrong results then why? I am using your (Oracle) data, technology and all stuff, and is it identified as a bug.

                        Thanks and Best Regards
                        Girish Sharma
                        • 9. Re: Usage of Index Range Scan Descending
                          Charles Hooper
                          Girish Sharma wrote:
                          Hi Sir,

                          Thanks for correcting me. Here my two doubts:

                          (A) If CBO thinks that COST of query will be less if i (CBO) use Full table scan in compare to index scan; will it still use index scan; just because of we have used index hint in the query?

                          (B) I wish to see an example/link in which Oracle is producing the wrong results please; as you are saying "The problem is that the hint in this example could lead to Oracle producing the wrong results." and if it is producing the wrong results then why? I am using your (Oracle) data, technology and all stuff, and is it identified as a bug.

                          Thanks and Best Regards
                          Girish Sharma
                          Girish,

                          This test case may answer your question regarding producing the wrong result. I will create a test table, insert 10,000 rows into the test table, create an index on the second column, and then gather the statistics for the table and indexes:
                          CREATE TABLE T1 (
                            C1 NUMBER,
                            C2 NUMBER,
                            C3 VARCHAR2(100),
                            PRIMARY KEY (C1));
                           
                          INSERT INTO T1
                          SELECT
                            ROWNUM,
                            DECODE(MOD(ROWNUM,100),0,NULL,ROWNUM),
                            RPAD('A',100,'A')
                          FROM
                            DUAL
                          CONNECT BY
                            LEVEL<=10000;
                           
                          COMMIT;
                           
                          CREATE INDEX IND_T1_C2 ON T1(C2);
                           
                          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
                          In the above table, 1 row of every 100 rows contains a NULL value in column C2. If we were to tell Oracle to order the rows using that column, Oracle could not use the IND_T1_C2 index to speed up the ordering of the rows because the NULL values are not contained in the index.
                          SELECT
                            *
                          FROM
                            T1
                          ORDER BY
                            C2;
                           
                          SELECT
                            *
                          FROM
                            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                          As expected:
                          -----------------------------------------------------------------------------------
                          | Id  | Operation        | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       |
                          -----------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT   |       |       |       |       |   217 (100)|       |
                          |   1 |  SORT ORDER BY        |       | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
                          |   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
                          -----------------------------------------------------------------------------------
                          Now we will repeat the test, making certain that no NULL values are introduced in column C2:
                          DROP TABLE T1 PURGE;
                           
                          CREATE TABLE T1 (
                            C1 NUMBER,
                            C2 NUMBER,
                            C3 VARCHAR2(100),
                            PRIMARY KEY (C1));
                           
                          INSERT INTO T1
                          SELECT
                            ROWNUM,
                            ROWNUM,
                            RPAD('A',100,'A')
                          FROM
                            DUAL
                          CONNECT BY
                            LEVEL<=10000;
                           
                          COMMIT;
                           
                          CREATE INDEX IND_T1_C2 ON T1(C2);
                           
                          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
                          In the above, NO_INVALIDATE=>FALSE was used to make certain that the queries for this table are hard parsed.
                          SELECT /*+ INDEX(T1 IND_T1_C2) */
                            *
                          FROM
                            T1
                          ORDER BY
                            C2;
                           
                          SELECT
                            *
                          FROM
                            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                          Oracle still cannot use that index to speed up retrieval, because the column C2 could possibly contain a NULL value, even though it does not:
                          -----------------------------------------------------------------------------------
                          | Id  | Operation        | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       |
                          -----------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT   |       |       |       |       |   217 (100)|       |
                          |   1 |  SORT ORDER BY        |       | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
                          |   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
                          -----------------------------------------------------------------------------------
                          So, we fix that problem and re-run the test:
                          ALTER TABLE T1 MODIFY (C2 NOT NULL);
                           
                          EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
                           
                          SELECT /*+ INDEX(T1 IND_T1_C2) */
                            *
                          FROM
                            T1
                          ORDER BY
                            C2;
                           
                          SELECT
                            *
                          FROM
                            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                          Now what happens? Oracle knows that all possible values for C2 will be in the index, so there is no chance that it could return the wrong result (missing rows):
                          SQL_ID  2d4j0qkfaynst, child number 0
                          -------------------------------------
                          SELECT /*+ INDEX(T1 IND_T1_C2) */   * FROM   T1 ORDER BY   C2
                           
                          Plan hash value: 4220775576
                           
                          -----------------------------------------------------------------------------------------
                          | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                          -----------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT            |           |       |       |   182 (100)|          |
                          |   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 10000 |  1054K|   182   (1)| 00:00:01 |
                          |   2 |   INDEX FULL SCAN           | IND_T1_C2 | 10000 |       |    22   (0)| 00:00:01 |
                          -----------------------------------------------------------------------------------------
                          Edit:
                          ----------------
                          Unfortunately, with my test case, the same execution plan is achieved with or without the index hint in place due to the calculated cost of the plan, so continuing with an artificially increased cost for the index access:
                          ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ=200;
                           
                          SELECT
                            *
                          FROM
                            T1
                          ORDER BY
                            C2;
                           
                          SELECT
                            *
                          FROM
                            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                           
                          SQL_ID     c9s457r2swafn, child number 1
                           
                          SELECT   * FROM   T1 ORDER BY      C2
                           
                          Plan hash value: 2148421099
                          -----------------------------------------------------------------------------------
                          | Id  | Operation        | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time       |
                          -----------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT   |       |       |       |       |   217 (100)|       |
                          |   1 |  SORT ORDER BY        |       | 10000 |  1054K|  2376K|   217   (2)| 00:00:02 |
                          |   2 |   TABLE ACCESS FULL| T1   | 10000 |  1054K|       |    11  (10)| 00:00:01 |
                          -----------------------------------------------------------------------------------
                          The above shows that without the index hint a full table scan is used. Now with the index hint:
                          SELECT /*+ INDEX(T1 IND_T1_C2) */
                            *
                          FROM
                            T1
                          ORDER BY
                            C2;
                           
                          SELECT
                            *
                          FROM
                            TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                           
                          SQL_ID     7cxfwpzxqfsbz, child number 0
                           
                          SELECT /*+ INDEX(T1 IND_T1_C2) */   * FROM   T1 ORDER BY   C2
                           
                          Plan hash value: 4220775576
                           
                          -----------------------------------------------------------------------------------------
                          | Id  | Operation              | Name     | Rows     | Bytes | Cost (%CPU)| Time     |
                          -----------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT         |          |     |     |   364 (100)|          |
                          |   1 |  TABLE ACCESS BY INDEX ROWID| T1     | 10000 |  1054K|   364   (1)| 00:00:02 |
                          |   2 |   INDEX FULL SCAN         | IND_T1_C2 | 10000 |     |    45   (3)| 00:00:01 |
                          -----------------------------------------------------------------------------------------
                          Charles Hooper
                          Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                          http://hoopercharles.wordpress.com/
                          IT Manager/Oracle DBA
                          K&M Machine-Fabricating, Inc.

                          Edited by: Charles Hooper on Apr 6, 2010 11:33 AM
                          Added section that add the OPTIMIZER_INDEX_COST_ADJ to show that the index hint is valid
                          • 10. Re: Usage of Index Range Scan Descending
                            Girish Sharma
                            Hi Charles,

                            First of all let me thanks a lot for your such a nice and very clear explanation and time given to my doubt.

                            Here my doubt is now bit cleared that if table is having NULL value or if it can carry NULL value in the indexed column, CBO will use always full table scan; even if we uses index hint or even where clause contains indexed column.

                            Rest part of doubt is:
                            (A) In your example, it was case (culprit) of NULL value; not it is due to index hint; while i became confused on saying by Lewis sir that index hint could lead to Oracle producing the wrong results. Index hints are working fine even in your example as in the last para. And only if it was the mean by Lewis sir, then now i am having no doubt.

                            (B) And suppose there is no NULL value in the table and NOT NULL constraint has been applied to the column; and only due to using index hint; Oracle is producing wrong result (which is very serious and i doubt that ORACLE will produce any wrong result); then definately it is the case which has to be referred to you/other experts for kind clarification.

                            Once again Thank you very much.
                            Best Regards
                            Girish Sharma
                            • 11. Re: Usage of Index Range Scan Descending
                              Charles Hooper
                              Girish,

                              I might have misunderstood what Jonathan wrote, but I believe that he stated that the index hint was invalid, because if Oracle was to use the index access path, there is a risk that the wrong results might be returned. The optimizer understands that there is this risk of returning an incorrect result set, and determines that the index hint is invalid. I do not think that Jonathan stated that Oracle will produce the wrong result set, only that certain access path might generate the wrong result set, and therefore cannot be used.

                              There is another example here:
                              http://hoopercharles.wordpress.com/2010/01/10/the-effects-of-potential-null-values-in-row-sources-during-updates-using-an-in-subquery/

                              Charles Hooper
                              Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                              http://hoopercharles.wordpress.com/
                              IT Manager/Oracle DBA
                              K&M Machine-Fabricating, Inc.
                              • 12. Re: Usage of Index Range Scan Descending
                                Girish Sharma
                                Thank you very much for clearing the remaining doubt.

                                Best Regards
                                Girish Sharma