Home » SQL & PL/SQL » SQL & PL/SQL » Index Not getting used (Oracle 9i)
Index Not getting used [message #333872] Mon, 14 July 2008 13:09 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
I am trying to execute this query

SELECT min(id),max(id) FROM dp_object_instance where
datecreation >= to_timestamp('2003-09-26 02:26:40','YYYY-MM-DD HH24:MI:SS')
AND
datecreation <= to_timestamp('2003-09-27 02:26:40','YYYY-MM-DD HH24:MI:SS');

I have a Index created on the column "datecreation" which is used on the where clause. But that index is not used when this query is executed instead it does a full table scan. Can any one help why this index is not getting used.


Please find the execution plan below.

Elapsed: 00:00:07.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'DP_OBJECT_INSTANCE'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40285 consistent gets
36550 physical reads
0 redo size
464 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Re: Index Not getting used [message #333876 is a reply to message #333872] Mon, 14 July 2008 13:25 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

URL below
http://www.orafaq.com/forum/t/84315/74940/
contains
These articles may help you understand some key points:


* On indexes

o Jonathan Lewis Why Isn't Oracle Using My Index?!
o Tom Kyte Why isn't my index getting used? (need to register to the site but it is free)
o Ross Leishman The index is being ignored

Too bad you are incapable or unwilling to search for answers before posting.

[Updated on: Mon, 14 July 2008 13:41] by Moderator

Report message to a moderator

Previous Topic: send html emails with images?
Next Topic: Delete statement on Huge table is very slow
Goto Forum:
  


Current Time: Mon Dec 05 04:58:45 CST 2016

Total time taken to generate the page: 0.18602 seconds