my query is not using my index

Post author By Laurent Schneider
Post date December 11, 2007
13 Comments on 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”

Laurent Schneider Post authorDecember 11, 2007 at 15:15

the statistics seem fine, in the second query oracle knows it return only one row, but it does not want to use the index
Jens Petersen December 11, 2007 at 16:17

I’m quite surprised to find you using ‘connect by level’ 😉
Don Burleson December 11, 2007 at 17:07

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.
Laurent Schneider Post authorDecember 11, 2007 at 17:21

@Jens : yes, I should have written that CONNECT BY PRIOR is not supported 😎 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 < 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)
Gary December 11, 2007 at 21:29

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.
CJ December 13, 2007 at 18:47

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.
Coskan December 13, 2007 at 19:06

@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.
Kamus December 14, 2007 at 17:42

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。非常高兴认识你。
Laurent Schneider December 14, 2007 at 18:47

LOL, I managed to register on itpub.net, but it is pure luck because I cannot read Chinese 😎
my itpub profile
Kamus December 14, 2007 at 19:17

@Laurent
so … you just can speak Chinese? same as Eddie Awad, has a wife who make you speak Chinese? lol
anyway, nice 2 cu
Laurent Schneider Post authorDecember 14, 2007 at 19:40

No, I cannot speak Chinese neither and my wife speaks Bakossi 😉

Anyway, nice to know you too ❗
Kamus December 14, 2007 at 19:55

@Laurent
I found this in in your site:
Language :
* French
* German
* English
* 在家,我不说英语
The last row is Chinese, so I think ….
Laurent Schneider Post authorDecember 15, 2007 at 08:53

Sure, I would love to speak Chinese, I did got help on that one 😀

Comments are closed.