my query is not using my index

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as 
select 
  sign(rownum-1) r, rpad(rownum,4000,'.') t 
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

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

| Id  | Operation                   | Name | Rows  | Bytes | C
ost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |      |     1 |  4004 |
1979   (2)| 00:00:24 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |  4004 |
1979   (2)| 00:00:24 |

|*  2 |   INDEX FULL SCAN           | I    |     1 |       |
1979   (2)| 00:00:24 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

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

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

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

|   0 | SELECT STATEMENT  |      |     1 |  4004 |   271K  (1)
| 00:54:18 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |  4004 |   271K  (1)
| 00:54:18 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("R"<>1)

13 thoughts on “my query is not using my index”

  1. Laurent,

    There are many ways to express the same syntax in Oracle SQL and the “not equals” operator may be expressed as “<>” or “!=”.

    You can also use the “not exists” or the “minus” clause in SQL.

    These “not equal” operators are supposed to be equivalent, but this note by Scott Canaan suggests that in Oracle 10.2, they can produce different execution plans, and hence, different execution speeds:

    http://www.freelists.org/archives/oracle-l/09-2006/msg01005.html

    Select count(*) from claws_doc_table where claws_doc_id = :id and exists
    (select 1 from claws_person_id where status != 0);

    If you use !=, it returns sub-second. If you use <>, it takes 7 seconds to return. Both return the right answer.

  2. @Jens : yes, I should have written that CONNECT BY PRIOR is not supported 8-) but it was so easy to generate 1 million rows… unfortunately I did it with system as default tablespace and my system tablespace was suddenly 12 Gb…

    @Don : I rewrite your message because this stupid wordpress engine expect you to write &lt; and not < :-( well, the case is different as I am not using count (where full table scan is used), but I select also a non-index column and I want a TABLE ACCESS BY INDEX ROWID (that I did get only with a hint)

  3. With XE and
    dbms_stats.gather_table_stats(user,’T’,cascade=>true,
    method_opt=>’for all columns size skewonly’);

    I could get an INDEX FAST FULL SCAN/INDEX RANGE SCAN use with
    select r,t from t where r in (select r from t where r!=1);

    But it does look like that the optimizer won’t got for the best plan for the plain query without a hint.

  4. Interesting that CBO doesn’t consider cost in the SELECT statement in the above example, but seems to consider it in the UPDATE statement given below:

    UPDATE t SET r = NULL WHERE r != 1;

    The plan should give an indexed full scan directly without hinting etc.

    Not sure if this behaviour changes in 11g, because I don’t see why the optimizer should ignore costs in the SELECT statement.

    Regards,
    Charu.

  5. @Garry

    You get the index fast full scan because of the coumns you select.

    If you select r and t columns together you would probably get full table scan

    but when you just select r column CBO thinks that it is better to go index because selected columns are already indexed.

  6. Laurent,

    It seems you good at Chinese, so … maybe you wanna meet some Oracle ACE from China. or you can visit our biggest oracle forum in China http://www.itpub.net

    I’m an Oracle ACE from Oracle corp. in China.

    Laurent,
    意外发现你的blog,似乎你是懂得中文的,因此,也许你愿意认识更多来自中国的Oracle ACE。我们在维护全中国最大的Oracle论坛www.itpub.net。非常高兴认识你。

  7. @Laurent
    so … you just can speak Chinese? same as Eddie Awad, has a wife who make you speak Chinese? lol
    anyway, nice 2 cu

  8. @Laurent
    I found this in in your site:
    Language :
    * French
    * German
    * English
    * 在家,我不说英语
    The last row is Chinese, so I think ….

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>