CODEWITHSUNDEEP
oracle-databaseindexingsql-order-by
Ele
Ele

Reputation: 553

oracle order by optimization

I am running a query on a large table and I am expecting a large number of returning row. unfortunately I need to order the result by 2 columns, which makes the query quite slow.

I added an index to those specific columns but was wondering, if the order direction makes a difference. one column is ordered desc and one is order asc.

thanks and best wishes, e.

Upvotes: 1

Views: 409

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

Your query might benefit from an index ordered the same way as your order by clause e.g.

create index index1 on table1 (col1 desc, col2 asc);

Whether it will benefit depends on the relative cost of the index scans and table lookups versus a simple full table scan. If the number of rows you want is low relative to the total number of rows in the table the query might benefit.

The only way to know for sure is try it.

Upvotes: 1

Related Questions