OraFAQ Forum: Performance Tuning » Index vs Full table scan on sysdate

Home » RDBMS Server » Performance Tuning » Index vs Full table scan on sysdate (11.2.0.3,Linux X86_64)

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

Index vs Full table scan on sysdate [message #627476]

Wed, 12 November 2014 04:39

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 ?

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627477 is a reply to message #627476]

Wed, 12 November 2014 04:42

John Watson
Messages: 8963
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

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

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627481 is a reply to message #627480]

Wed, 12 November 2014 04:54

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Post explain plan for both queries

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627482 is a reply to message #627480]

Wed, 12 November 2014 04:54

John Watson
Messages: 8963
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.

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627495 is a reply to message #627482]

Wed, 12 November 2014 06:52

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.

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #627512 is a reply to message #627495]

Wed, 12 November 2014 08:27

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.

Report message to a moderator

Re: Index vs Full table scan on sysdate [message #628463 is a reply to message #627512]

Tue, 25 November 2014 08:38

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.

Report message to a moderator

Previous Topic:	Decide on Partitioning Column/Purge Approach
Next Topic:	A question about execution plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 17 23:29:15 CST 2025