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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
This was first published in June 2001