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)
Tuesday, January 1, 2008
my query is not using my index
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment