Problem solve Get help with specific problems with your technologies, process and projects.

TOP-N queries and function-based indexes

I need your help regarding TOP-N queries and function-based indexes. As stated in the Oracle 8i SQL Reference a TOP-N query looks like:

  Select * from (
    Select Name from Customer order by Name
  ) where ROWNUM < 10
In http://asktom.oracle.com/, it is also said that Oracle first sorts the whole table and then selects the 10 rows unless there is an index for the name column. That means, if there is an index on the column of the order-by expression, then Oracle will use the index and return the first ten rows. This makes the query fast: It needs only 0.X seconds instead of over 10 seconds (the customer table has 70,000 rows).

But how is it with function-based indexes? Suppose the TOP-N query is:

  Select * from (
    Select (LastName||FirstName) from Customer order by (
Lastname||Firstname ) 
  ) where ROWNUM < 10
We defined a function-based index (LastName||FirstName) on the table Customer, but this query still takes a long(!) time. We also tested the index whether it exists and whether it is enabled by using the expression (Lastname||Firstname) in a where statement. The index exists and works, but it is not used in TOP-N queries.

So my questions are:

1. Is it possible to use function-based indexes for top-n queries?
2. If not, is there any other way to get the first entry of a function-based index?

I do not know of any restriction on the use of function-based indexes in Top-N queries. They should be used as long as the Optimizer thinks the use of the index will produce a faster/better execution plan. The fact that it is not being used would tend to make me think that the Optimizer doesn't think it will help. Without more detailed information on the results of your query's execution plan, the best I can tell you is that the Optimizer decided it was more "efficient" to not use your function-based index.

There are ways to "see" what the optimizer is doing. You can do a 10053 event trace which will give you all the info on how the optimizer made its decision to use the execution plan that it did. There's a great paper called "A Look Under the Hood of the CBO: The 10053 Event". You'll have to sign-up for level 1 access (free) to read the paper, but it might give you some ideas on how to research your problem further.

For More Information

This was last published in November 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.