OraFAQ Forum: Performance Tuning » Why oracle doesn't use the index?

Home » RDBMS Server » Performance Tuning » Why oracle doesn't use the index?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why oracle doesn't use the index? [message #484646]

Thu, 02 December 2010 03:55

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: Why oracle doesn't use the index? [message #484658 is a reply to message #484649]

Thu, 02 December 2010 04:38

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?

Report message to a moderator

Re: Why oracle doesn't use the index? [message #484669 is a reply to message #484658]

Thu, 02 December 2010 04:50

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

select codnew from news-comment  
where (CASE  WHEN codnew >2248833 and valid='N' THEN userid ELSE NULL END ) = 2914655

Report message to a moderator

Re: Why oracle doesn't use the index? [message #484674 is a reply to message #484669]

Thu, 02 December 2010 04:54

iblazquez
Messages: 26
Registered: June 2007

Junior Member

It works.

Thanks

Report message to a moderator

Re: Why oracle doesn't use the index? [message #484698 is a reply to message #484669]

Thu, 02 December 2010 05:47

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

Re: Why oracle doesn't use the index? [message #484704 is a reply to message #484698]

Thu, 02 December 2010 05:58

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What did I say about code tags?

You need to tell us what indexes being used are.

Report message to a moderator

Re: Why oracle doesn't use the index? [message #484713 is a reply to message #484704]

Thu, 02 December 2010 06:35

iblazquez
Messages: 26
Registered: June 2007

Junior Member

Sorry again,

In the first case oracle use a index by USERID indexing all the rows

In the second one, oracle use the new index function based. This index only has the USERIDS for CODNEWS >2248833

Report message to a moderator