Why oracle doesn't use the index? [message #484646] |
Thu, 02 December 2010 03:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
iblazquez
Messages: 26 Registered: June 2007
|
Junior Member |
|
|
Hello,
First, I'm sorry for my english, it's difficult to me explain this.
I have a table "NEWS_COMMENT" like this:
Name Type
------- --------------
ID NUMBER(8)
USERID NUMBER(8)
SORT_TEXT VARCHAR2(100)
TEXT VARCHAR2(1000)
DATE DATE
VALID VARCHAR2(1)
CODNEW NUMBER(10)
The table has a normal index for the userid column.
There is a query that looks for the differents CODNEW for a USERID but allways the CODNEW has to be greater than 2248833
select codnew from news-comment where userid=2914655 and valid='N' and codnew>2248833
I have created a new index for this kind of querys
create index coment_new_IDX on news_comment
(CASE WHEN codnew >2248833 and valid='N' THEN userid ELSE NULL END )
but oracle doesn't use it. I have used a hint to force it but doesn't run.
What am I doing bad?
Thanks
CM: added [code] tags, please do so yourself next time
[Updated on: Thu, 02 December 2010 04:03] by Moderator Report message to a moderator
|
|
|
Re: Why oracle doesn't use the index? [message #484649 is a reply to message #484646] |
Thu, 02 December 2010 04:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Can you please read and follow the orafaq forum guide especially the part on how to format your post.
Function based indexes are only used when the exact function specified in the index is used in the query. Your sample query doesn't even contain a case statement. So why would you think oracle would use that index?
I also don't see any need for a function based index here. A normal index on the three columns should do just fine.
|
|
|
Re: Why oracle doesn't use the index? [message #484658 is a reply to message #484649] |
Thu, 02 December 2010 04:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
iblazquez
Messages: 26 Registered: June 2007
|
Junior Member |
|
|
I'm sorry for the post format
I want to use this new index because there ara a lot of records with the CODNEW less than 2248833
The size of the index wich have all the records is 300M and the new index has 10M.
Could you help me with the query to use that new index?
|
|
|
|
|
Re: Why oracle doesn't use the index? [message #484698 is a reply to message #484669] |
Thu, 02 December 2010 05:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
iblazquez
Messages: 26 Registered: June 2007
|
Junior Member |
|
|
I can't believe, I've created the index thinking that the performance will be better and this is the result:
Using the index for all the records
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 209 (1)| 00:00:03 |
| 1 | HASH UNIQUE | | 4 | 56 | 209 (1)| 00:00:03 |
|* 2 | TABLE ACCESS BY INDEX ROWID| NEWS_COMMENT | 4 | 56 | 208 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | ALL_COMMENTS_IDX | 205 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
2 - filter("CODNEW">2248833 AND "VALID"='N')
3 - access("USERID"=2914655)
Using the index for the records greatest than 2248833
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33587 | 459K| 39524 (1)| 00:07:55 |
| 1 | HASH UNIQUE | | 33587 | 459K| 39524 (1)| 00:07:55 |
| 2 | TABLE ACCESS BY INDEX ROWID| NEWS_COMMENT | 99434 | 1359K| 39520 (1)| 00:07:55 |
|* 3 | INDEX RANGE SCAN | COMENTS_2248833_IDX | 39774 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
3 - access(CASE WHEN ("CODNEW">2248833 AND "VALID"='N') THEN "USERID" ELSE NULL END =2914655)
Why?
CM: applied [code] tags, again.
[Updated on: Thu, 02 December 2010 05:57] by Moderator Report message to a moderator
|
|
|
|
|
|
|