CODEWITHSUNDEEP
oracle-databaseindexing
user729089
user729089

Reputation: 13

indexing in oracle

I have a table

Name | Postal_CODE

I have created an index on Postal_Code as

CREATE INDEX MNO on Table1(Postal_code).

but, when i see the execution plan of

select * from Table1 where Postal_code is not null

it show's full scan for Table1.It does not make use of index. Any idea why?

Upvotes: 1

Views: 268

Answers (3)

cagcowboy
cagcowboy

Reputation: 30828

If more than 10% (approx) of the rows in the table have postal_codes, then it's more efficient for Oracle to do a fast full scan or full table scan than use the index.

(10% is a very rough rule of thumb. YMMV.)

Upvotes: 1

ravnur
ravnur

Reputation: 2852

Oracle's BTree indexes do not store null values. It means that you need to get all rows from index and in this case it will be faster to use full scan.

Upvotes: 4

gview
gview

Reputation: 15361

In this situation, Oracle will not store a reference to rows where Postal_code is null. Therefore it will not use the index. It's not quite that simple either as Oracle may have determined that the cost of using the index is more expensive than doing a tablescan.

Upvotes: 0

Related Questions