| Indexes are not being used ! Why ? [message #63948] |
Mon, 29 November 2004 10:18  |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
Hi,
I have an index on issue_dt field of detail_tab table. The tables and indexes are analyzed.
When I am issuing the following query :
SELECT *
FROM detail_tab
WHERE issue_dt = (SYSDATE - 21);
I am witnessing an indexed access. But when I am issuing the following query :
SELECT *
FROM detail_tab
WHERE issue_dt > SYSDATE - 21
it is doing a full table scan.
Can anyone tell me the reason behind this. Are indexes not being used with a '>' (greater than) operator. If so, how do we re-write the query to get the desired output in good time.
Thanks in advance,
Shibaji
|
|
|
|
| Re: Indexes are not being used ! Why ? [message #63949 is a reply to message #63948] |
Mon, 29 November 2004 11:05  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Check your stats:
select table_name, num_rows, last_analyzed from user_tables where table_name = 'DETAIL_TAB';
select index_name, table_name, uniqueness, distinct_keys, clustering_factor, num_rows, last_analyzed
from user_indexes where table_name = 'DETAIL_TAB';
If they are up to date, then the optimizer has probably decided that doing a FTS is cheaper...
You can test the rule optimizer to see what it does - it'll probably do an index range scan (which is probably worse based on the stats).
alter session set optimizer_goal=rule;
|
|
|
|
| Re: Indexes are not being used ! Why ? [message #63950 is a reply to message #63949] |
Mon, 29 November 2004 15:12 |
Shibaji Ghosh
Messages: 39
Registered: April 2002
|
Member |
|
|
Thank you for the response.
I have recently analyzed the tables and indexes. Also validated the index. When the query is with an '=' operator (say issue_dt = sysdate - 21) then its doing an index scan. Even when the query is like (issue_dt > sysdate) then also index range scan is happenning. The response time is very fast.
But when I am putting issue_dt > sysdate - 21 , then its doing a Full Table Scan. I have put sysdate - 21 within braces, so that the query is like ..where issue_dt > (sysdate - 21). But it fails to do an index scan and is very slow.
Is there a way out ? Does index fails if an arithmetic operator is used on the right side of '=' operator ?
Thanks in advance.
Shibaji
|
|
|
|
| Re: Indexes are not being used ! Why ? [message #63951 is a reply to message #63950] |
Mon, 29 November 2004 15:26 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
if you have collected column histograms, then the optimizer may have determined that that's going to return a "large" proportion of the table, so deduces that a FTS is going to be cheaper (probably less I/Os than opening the index and then having to fetach most of the blocks for that table too).
See if you have any data in user_tab_histograms for that table.
|
|
|
|
| Re: Indexes are not being used ! Why ? [message #63952 is a reply to message #63950] |
Mon, 29 November 2004 20:46 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
What portion of the issue_dt > sysdate - 21 ??
Looks like the optimizer works.
If you change it to issue_dt > sysdate (probably returning (close to) 0 rows) it uses the index because it KNOWS there are only few rows matching this condition.
If there are relatively many rows (> 10-20%) that match your criterium issue_dt > sysdate - 21, then the optimizer decides it's cheaper to do a Full Table Scan then an index scan.
Do as Andrew asked: compare the query time with a rule-based query.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
