CODEWITHSUNDEEP
oracle-databaseindexingsql-order-byquery-optimization
user3198603
user3198603

Reputation: 5816

Using index during order by clause?

I have created the index on created_date column of employee table but when i see the explain plan of below query i do not see index being used. Why ?

    Select * from employee order by created_date desc;

Per mine understanding, oracle will keep the created date in sorted fashion(using B tree) along with row id. So why it does not use the already sorted data then fetch the other data using row id ?

If it does not use index then it will fetch all data in memory, then sort it and return it. Is n't it ?

Upvotes: 0

Views: 2856

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

most probably that your created_date column is nullable. Add 

where created_date is not null

to your query,

Select * from employee where created_date is not null order by created_date desc;

and look your explain plan again.

No guarantee to use index, but uses most probably if it doesn't need a full-scan. ( if a query returns more than 10 percent, doesn't need an index generally )

Oracle checks for data magnitude(we could query from dba_tables, manually also), if CBO(Cost based optimization,in most of the systems, this type of optimization preferred,instead of Rule-based optimization) used, and relevant table analyzed.

  • The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation.

Upvotes: 3

user5683823
user5683823

Reputation:

You must read all the rows from the base table, right? To read all of them from disk, the fastest way is to read them in bulk - block by block, regardless of their ordering on disk. This will minimize the number of reads from hard disk.

What you are proposing:

Look at the index. Find the rowid of the row with the most recent date and read that one row from disk. Then find the rowid of the next row and read that row from disk. Repeat. One read from disk for each row in the table.

Yes, in that way you have the rows ordered already (you avoid the CPU work to order the rows). But you have many more read operations from disk. Just to make up some numbers: you save 0.8 seconds of CPU work, and you increase I/O time by 40 seconds. (Purely made up, but probably the correct order of magnitude in the comparison.)

Upvotes: 1

Related Questions