Ask the Expert

Rewrite query in 7.3.4 without Function Based Indexes

I have a query as follows:

    Requires Free Membership to View

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 first published in June 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: