Skip to Main Content
  • Questions
  • Avoid TRUNC and using between on sysdate

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 02, 2016 - 12:21 pm UTC

Last updated: March 24, 2021 - 9:14 am UTC

Version: 11

Viewed 50K+ times! This question is

You Asked

Hi ,

I have the below query where am using TRUNC on the column2 filed which is increasing the performance delay.

so need to avoid the TRUNC on this check and need to use BETWEEN operator.
Here SYSTEMDATE is the currentdateandtime.


So how can write a between condition so that column2[which included time part as well] is between the time of the systemdate[which included time part as well].

Current query:

SELECT sum(column3)
from table1
where column1 = 'XXXXXAA12'
and TRUNC(column2) = TRUNC(SYSTEMDATE)

Need to have smthng below:

SELECT sum(column3)
from table1
where column1 = 'XXXXXAA12'
and column2 between xxxxxxxxxxxxxxxxx and xxxxxxx

so without using TRUNC on both sides how to write the query ?


Please suggest ??

Thank you in advance

and Chris said...

To remove the trunc from your column you need to compare all the rows where the date is between trunc(sysdate) and trunc(sysdate) + 1.

Remember that between is inclusive though. So you'll get all the rows up to and including the final condition. This returns values for the next day!

So you can't replace trunc(col) = trunc(sysdate) with col between trunc(sysdate) and trunc(sysdate)+1. You'll get different results.

Instead, you need to find those greater than or equal to trunc(sysdate) and strictly less than it plus one:

trunc(sysdate) <= dt and dt < trunc(sysdate) + 1 


Notice how in the example below, the second query (with between) returns 24 rows. The others return 23 rows:

create table t as
  select trunc(sysdate) + rownum/24 as dt from dual
  connect by level <= 300;
 
select count(*) from t
where  trunc(sysdate) = trunc(dt);

COUNT(*)  
23  

select count(*) from t
where  dt between trunc(sysdate) and trunc(sysdate) + 1;

COUNT(*)  
24  

select count(*) from t
where  trunc(sysdate) <= dt and dt < trunc(sysdate) + 1;

COUNT(*)  
23 


You mentioned that performance is an issue. So there is another way of solving this. Function-based indexes.

Placing a function on a column "breaks" an index on it. So Oracle can't use it. To overcome this, create an index which matches the function in your where clause.

In this case trunc(column2):

set autotrace trace exp
create index i on t(dt);

select count(*) from t
where  trunc(sysdate) = trunc(dt);

Execution Plan
----------------------------------------------------------
Plan hash value: 1071362934

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    23 |   207 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(INTERNAL_FUNCTION("DT"))=TRUNC(SYSDATE@!))

drop index i;
create index i on t(trunc(dt));

select count(*) from t
where  trunc(sysdate) = trunc(dt);

Execution Plan
----------------------------------------------------------
Plan hash value: 163676535

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| I    |    23 |   207 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TRUNC(INTERNAL_FUNCTION("DT"))=TRUNC(SYSDATE@!))


Notice how Oracle has switched from a full table scan to an index range scan.

Changing your where clause and using a normal index is still the better solution though.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

clarification

Scott Wesley, March 22, 2018 - 2:36 am UTC

Would you please elaborate on this statement?
"Changing your where clause and using a normal index is still the better solution though"

Would there be any advantage to using a function based index vs just indexing the date/time column?

Obviously the FB index would limit what queries could be run, but else should we consider?

Chris Saxon
March 22, 2018 - 5:32 pm UTC

The advantage of the function-based index is if the code uses:

trunc(date_col) = ...


And you can't change it (easily). But otherwise you're better off with a regular index - this will work if you want to query by time of day too.

Note that as of 11.2.0.2 the optimizer can use the FBI for a "regular" query, even if it is less efficient:

https://blog.dbi-services.com/index-on-truncdate-do-you-still-need-old-index/

sb, March 23, 2021 - 5:32 pm UTC

what is wrong with this...

select count(*) from t
where trunc(dt) between trunc(sysdate) and trunc(sysdate) ;
Chris Saxon
March 24, 2021 - 9:14 am UTC

Well

trunc(sysdate) and trunc(sysdate)


Both have the same value, so there's no point in using between here