Home » RDBMS Server » Performance Tuning » Index not being used for Min/Max (Oracle 11g)
icon3.gif  Index not being used for Min/Max [message #345433] Wed, 03 September 2008 10:22 Go to next message
GreekBoy
Messages: 1
Registered: September 2008
Junior Member
Hello pals, I have a major problem with a table of about 12M records.

The table contains a Date field (non-unique) with an index on it.

Through the Plan explanation facility, I notice that when I select the MIN or MAX value of the Date field, a full table scan is followed. But, when I select the MIN or MAX value of the integer primary key of the table, the relevant index is being used.

Is this normal? Why doesn't Oracle take advantage of the sorted index to find the values instantly; Am I doing something wrong?

Update: I just noticed that when the table contains considerably less records (arround 1 million), the index is being correctly used also for the case of the Date field...

Re: Index not being used for Min/Max [message #345436 is a reply to message #345433] Wed, 03 September 2008 10:28 Go to previous messageGo to next message
BlackSwan
Messages: 25032
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

Can this mythical date field contain NULL value?

From http://www.orafaq.com/forum/t/84315/74940/

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

[Updated on: Wed, 03 September 2008 10:31] by Moderator

Report message to a moderator

Re: Index not being used for Min/Max [message #345438 is a reply to message #345436] Wed, 03 September 2008 10:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Exactly!.
If the column is been defined as NOT NULL,
then it might do a INDEX FAST FULL SCAN.
Re: Index not being used for Min/Max [message #345510 is a reply to message #345438] Wed, 03 September 2008 22:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This article may also be useful

Ross Leishman

[edit- woops - 0 length href]

[Updated on: Thu, 04 September 2008 06:45]

Report message to a moderator

Re: Index not being used for Min/Max [message #345540 is a reply to message #345510] Thu, 04 September 2008 01:45 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:
This article may also be useful-
Ross Leishman


which one?


http://www.orafaq.com/tuningguide/range%20key%20table.html






Regards,
Oli

[Updated on: Thu, 04 September 2008 01:51]

Report message to a moderator

Previous Topic: Which one is to choose?
Next Topic: Function Based Join Without Nested Loops
Goto Forum:
  


Current Time: Fri Dec 02 12:12:42 CST 2016

Total time taken to generate the page: 0.15995 seconds