question

BI DWH BALA avatar image
BI DWH BALA asked

Why oracle is using Full table scan even though indexes are defined ?

I am having a table which contains 80k to one l lakh records. While running explain plan query its not using indexes. And it seems, its doing full table scan.

I am not sure why oracle is not using indexes? Am I missing anything here?

Thank you, Regards,

BI DWH BALA

oracleperformanceindex
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pratikk avatar image pratikk commented ·
1 lakh = 0.1 million It is a counting unit popular in India
1 Like 1 ·
KillerDBA avatar image KillerDBA commented ·
Could you write that first sentence a little differently? I'm having trouble understanding it. Also, if you supply table definitions (with indexing and keys), the query and the count of rows in each table, it might help provide enough information to really trouble-shoot your problem.
0 Likes 0 ·
KillerDBA avatar image
KillerDBA answered

It could be a cardinality issue. Let's say you have a table with STAT_CODE defined where STAT_CODE is either 'A' or 'C' (Active or Closed).

If half are 'A' and half are 'C', an index on that column is probably useless, it's easier to get the records with a scan than to go through the overhead of using the index. The cutoff point varies by datamanager and other factors but a scan is probably useful until fewer than 25% of the rows would be qualified by checking the index.

Of course, compound indexes and other thing change the situation but generally, an index needs some uniqueness to be useful.

I'm not an Oracle expert, by any means, but I'd bet Oracle also has trouble when datatypes are badly matched. I've also seen scans generated on poorly defined queries.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image HillbillyToad commented ·
Indexes aren't always the solution. FULL TABLE SCANs aren't always bad. Beware rules of thumb when it comes to SQL tuning. Also, be sure that statistics are available and up to date for your objects, else the optimizer will be hamstrung when it comes to building execution plans.
1 Like 1 ·
BI DWH BALA avatar image BI DWH BALA commented ·
It seems you are right and pointed out perfect point. In my table I have a field called Validity_YN. Which contains Y and N. I am running a query where condition as Validity_YN = 'Y'. And almost 70% of the records are having this column value as Validity_YN. Thanks a lot! And I think, I need to redefine my indexes on right columns. Thanks for your suggestions.
0 Likes 0 ·
Ian avatar image
Ian answered

I have found a couple of things that have helped me get rid of table access full, they may help you, they may not:

1) Ensure that all the columns used from the table are covered by an index. 2) Make sure that you reference the columns - either in the select, from or where clauses - in the same order as they are specified in the index. 3) Don't expect upper(....) etc to make use of your indexes. If you need to use upper, consider a function based index.

Consider the amount of data in the table, as Hillbilly says above, sometimes it's quicker for Oracle to load the whole table than to use the index.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.