OraFAQ Forum: Performance Tuning » Index not being used for Min/Max

Home » RDBMS Server » Performance Tuning » Index not being used for Min/Max (Oracle 11g)

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

Index not being used for Min/Max [message #345433]

Wed, 03 September 2008 10:22

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...

Report message to a moderator

Re: Index not being used for Min/Max [message #345436 is a reply to message #345433]

Wed, 03 September 2008 10:28

BlackSwan
Messages: 26766
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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Index not being used for Min/Max [message #345510 is a reply to message #345438]

Wed, 03 September 2008 22:13

rleishman
Messages: 3728
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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 15:10:38 CST 2024