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 < 10In 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 < 10We 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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.