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

Rewrite query in 7.3.4 without Function Based Indexes

I have a query as follows:

select * from tab1 where trunc(check_date)

My Oracle version is 7.3.4, and it doesn't use functional indexes. Do you have any idea how to rewrite this in 7.3.4?

There are little tricks that you can do when you don't have Function Based Indexes at your disposal, i.e you are running a pre-Oracle 8i database. The trick is to make sure that there is no function on the indexed column of the table and to move that function to the other side of the comparison operator. Often times, this involves simple algebra. For instance, the following query will not use an index since a function is applied to the indexed column:

   SELECT * FROM table_a
   WHERE column_a+1 = 12;
By simple algebra, we can rewrite this query to:
   SELECT * FROM table_a
   WHERE column_a = 11;

This will give the same exact results while still using an index! Curiously, I have sometimes seen people intentionally suppressing the usage of an index by using a "dummy" function. For instance:

   SELECT * FROM table_a
   WHERE column_a+0 = 56;

Adding zero to column_a does not change the results of the query. But this function (which does nothing) will suppress the index.

So what about functions like TRUNC? For number columns, this is pretty easy. Let's suppose I have a query like:

   SELECT * FROM table_a
   WHERE TRUNC(column_a) = 12;

This will return all rows where column_a has '12' as the integer part of the real number. What does this mean? This means that any number *between* 12.0 and 12.999999999999 (as many nines as you want) will fit the bill. Notice I stressed the word "between". That's my key. Let's rewrite this query as:

   SELECT * FROM table_a
   WHERE column_a BETWEEN 12.0 AND 12.999999;

This query should return the same results. How many nines do you use? That is determined by the precision you specified for column_a.

So what about a TRUNC function on dates? The TRUNC function (without any date format) will round a date to midnight, the beginning of the day. So a query like:

   SELECT * FROM table_a
   WHERE TRUNC(column_a) = TO_DATE('01/01/01','MM/DD/YY');

will return all rows that have Jan 1, 2001 regardless of the time of that day. How do we get rid of the TRUNC function so that the index on column_a is used? We "move" it to the other side and use the BETWEEN operator. For instance:

   SELECT * FROM table_a
   WHERE column_a BETWEEN
      TO_DATE('01/01/01 00:00:00','MM/DD/YY HH24:MI:SS') AND
      TO_DATE('01/01/01 23:59:59','MM/DD/YY HH24:MI:SS');

This will get all rows that have Jan 1, 2001 regardless of the time of that day.

Sometimes, one has to get really creative when moving a function applied to a column to the other side of the comparison operator.

For More Information

This was last published in June 2001

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.