Home » RDBMS Server » Performance Tuning » Full table scan when one type of records (Oracle 9.2.0.5.0, Windows)
Full table scan when one type of records [message #377530] Tue, 23 December 2008 06:27 Go to next message
senaka
Messages: 5
Registered: September 2008
Junior Member
When I execute the following query in the table, the explain plan shows a full table scan.
SELECT *
FROM Posting_Proposal_Head_Tab.
WHERE COMPANY = '01'

Company field is part of the primary_key.
table holds records only on company '01'.

When I used a company which is not exist in the table(other than '01') then shows a index range scan.

Is this a bug in oracle or is this the way that the query should handle.
Re: Full table scan when one type of records [message #377538 is a reply to message #377530] Tue, 23 December 2008 06:48 Go to previous message
Michel Cadot
Messages: 65154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle is very smart.
It knows that if you use 01 then it has to return (almost) all rows so it knows it is faster to full scan.
And it knows when you use another value there is no or few rows so it uses index.
Really smart, doesn't it?

Regards
Michel
Previous Topic: Substr,LTrim,Rtrim making query run slow
Next Topic: Query Rewrite to Utilize Materialized Views on a Remote Database
Goto Forum:
  


Current Time: Wed Aug 23 09:05:31 CDT 2017

Total time taken to generate the page: 0.05337 seconds