This discussion is archived
12 Replies Latest reply: Apr 6, 2010 10:34 AM by Girish Sharma RSS

Usage of Index Range Scan Descending

ahb72 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    "-- 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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Thanks Jonathan,

    Edited by: Orawiss on Apr 6, 2010 3:08 PM
  • 8. Re: Usage of Index Range Scan Descending
    Girish Sharma Guru
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Thank you very much for clearing the remaining doubt.

    Best Regards
    Girish Sharma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points