Tuesday, January 1, 2008

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)

No comments:

Some/All/Major of the blog content is not mine and i'm not the writer of it, all rights reserved to the authors.