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

Write this query to make use of an index

I have a query as:
select * from tab1
where trunc(check_date)

This query doesn't make use of an index. Can you tell me a better way to write this query so that it makes use of an index? It would help me greatly. Thanks in advance.

You didn't say which version of Oracle you are running. It can make a difference to the answer.

This problem plagued many SQL queries for many people. So you are not alone. The problem is that your TRUNC function invalidates the use of any index on the CHECK_DATE column. Starting with Oracle 8i, the concept of a Function Based Index was introduced. This lets one create an index on a column at the same time, using a function on that column. The Function Based Index is created as follows:

   CREATE INDEX check_date_trunc_idx
   ON table_name TRUNC(check_date);

In the index, the TRUNC function is now applied to column so that queries such as yours can use this index. There is much more to Function Based Indexes, and I suggest that you read the Oracle 8i Concepts guide for more information.

For More Information

Dig Deeper on Oracle database design and architecture

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.