Home » RDBMS Server » Performance Tuning » Index vs Full table scan on sysdate (11.2.0.3,Linux X86_64)
Index vs Full table scan on sysdate [message #627476] Wed, 12 November 2014 04:39 Go to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
I am running the below 2 statements

1) select name from table where date > sysdate -50;
Index used

2) select name from table where date > sysdate -51;
Full table scan

Why does the 1st query uses the index whereas second does a full table scan ?
Re: Index vs Full table scan on sysdate [message #627477 is a reply to message #627476] Wed, 12 November 2014 04:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There could be many reasons. Better start by posting details of what you are doing and the objects involved (not forgetting that DATE is not a valid column name).

[Updated on: Wed, 12 November 2014 04:43]

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627480 is a reply to message #627477] Wed, 12 November 2014 04:51 Go to previous messageGo to next message
preet_kumar
Messages: 204
Registered: March 2007
Senior Member
Desc EMPTAB
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
DT DATE
OID NUMBER(1)

1) select id from emptab where dt > sysdate -50;
Index used

2) select id from emptab where dt > sysdate -51;
Full Table Scan

Hope this helps to answer my query


Re: Index vs Full table scan on sysdate [message #627481 is a reply to message #627480] Wed, 12 November 2014 04:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Post explain plan for both queries
Re: Index vs Full table scan on sysdate [message #627482 is a reply to message #627480] Wed, 12 November 2014 04:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You have been a member long enough to know that using [code] tags to format code is essential if you want to make it easy to read.
You have not provided any execution plans, either projected or actually used.
You have not provided any information on how many rows are selected and how many exist.

So basically, you have added nothing to your original post.
Re: Index vs Full table scan on sysdate [message #627495 is a reply to message #627482] Wed, 12 November 2014 06:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The answer to this is always "because the database thinks it is faster".

There is always a tipping point between index access//FTS and you appear to have found yours.

If there is a manifest problem here, we need a lot more information.
Re: Index vs Full table scan on sysdate [message #627512 is a reply to message #627495] Wed, 12 November 2014 08:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
My take on this is, whether the FTS is actually slower in anyway? If not then, once again, Full table scans are not always bad.
Re: Index vs Full table scan on sysdate [message #628463 is a reply to message #627512] Tue, 25 November 2014 08:38 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
I would

1. Check if/Make sure that statistics are up to date for this table
2. perform a query to check the distribution of the values for this column

select DT, count(*) from emptab group by rollup(DT) order by count(*) desc

3. Please post more info - explain plan e.t.c
4. Please show which indexes exist for your table
5. Please show if there are histograms for the DT column

Please post what you found and we may have some progress in finding explanation for your question.
Previous Topic: Decide on Partitioning Column/Purge Approach
Next Topic: A question about execution plan
Goto Forum:
  


Current Time: Thu Mar 28 15:10:13 CDT 2024