The simple fix to date queries

September 22, 2016

2 minutes

We had question in the OpenWorld panel about why queries on date columns are “always slow”. Well….they aren’t Smile but here’s a common cause of that misconception.

Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is a date).


SQL> create table t as
  2  select *
  3  from dba_objects;

Table created.

SQL>
SQL> create index t_ix on t ( created );

Index created.

The problems start when we do a query on the CREATED column and get “unexpected” results


SQL> select owner, object_name
  2  from t
  3  where created = date '2016-09-20';

no rows selected

We were expecting to get some rows here, but none were returned. And we quickly deduce that this is because of the CREATED column also containing the time component. So nothing was created at midnight on September 20.

So to remove the time component, the query is recast as:


SQL> set autotrace on
SQL> select owner, object_name
  2  from t
  3  where trunc(created) = date '2016-09-20';

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   951 | 38040 |   446   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   951 | 38040 |   446   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2016-09-20
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1602  consistent gets
          0  physical reads
          0  redo size
        620  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

and ker-splat, the ability to use the index on the CREATED column disappears because we put the TRUNC expression around it. We used 1602 logical I/O’s to satisfy our query. But all it takes is a little tinkering of our query to get CREATED “unwrapped”


SQL> select owner, object_name
  2  from t
  3  where created >= date '2016-09-20'
  4  and created < date '2016-09-20' + 1;

OWNER        OBJECT_NAME
------------ ------------------------------
MCDONAC      T1


Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   184 |  7360 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   184 |  7360 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IX |   184 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("CREATED">=TO_DATE(' 2016-09-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "CREATED"< processed rows 1 (disk) sorts 0 (memory) client from to roundtrips SQL*Net 2 via received bytes 552 sent 620 size redo reads physical gets consistent 4 block db calls recursive ---------------------------------------------------------- Statistics hh24:mi:ss?)) ?syyyy-mm-dd 00:00:00?, 2016-09-21>

The query is the same functionally, but we got to use the index to speed up our query. This is not by any means claiming that using the index is always the best option, but at least by having the CREATED column “untarnished” by expressions, then we are giving the optimizer more choices on the potential means to best run the query.

9 responses to “The simple fix to date queries”

Robert Massey

September 22, 2016 at 4:55 pm

Alternatively, you can create a function-based index on the date portion of created:

create index t_ix on t(trunc(created));

Your second query will then use the index:

select owner, object_name from t where trunc(created) = date ‘2016-09-20’;

Reply
1. connormcdonald
  
  September 23, 2016 at 12:32 pm
  
  Yeah…but that can come back and bite you in strange places later 🙂
  
  SQL> create table t as
  2 select *
  3 from dba_objects;
  
  Table created.
  
  SQL>
  SQL> create index t_ix on t ( trunc(created) );
  
  Index created.
  
  SQL>
  SQL> alter table t enable row movement;
  
  Table altered.
  
  SQL>
  SQL> alter table t shrink space;
  alter table t shrink space
  *
  ERROR at line 1:
  ORA-10631: SHRINK clause should not be specified for this object
  
  Reply
Kevan Gelling

September 22, 2016 at 6:46 pm

Does an index on TRUNC help – create index t_ix on t ( trunc(created) )?

Can it replace the standard index?

Reply
1. connormcdonald
  
  September 23, 2016 at 12:33 pm
  
  Yeah…but that can come back and bite you in strange places later 🙂
  
  SQL> create table t as
  2 select *
  3 from dba_objects;
  
  Table created.
  
  SQL>
  SQL> create index t_ix on t ( trunc(created) );
  
  Index created.
  
  SQL>
  SQL> alter table t enable row movement;
  
  Table altered.
  
  SQL>
  SQL> alter table t shrink space;
  alter table t shrink space
  *
  ERROR at line 1:
  ORA-10631: SHRINK clause should not be specified for this object
  
  Reply
Paul Muller

September 22, 2016 at 11:48 pm

I was just discussing this with a colleague earlier this week!

Personally, I don’t care for either the function based index idea, or making a second column (real or virtual) as trunc(created).
Far better to use SQL correctly, as Connor has done in his example.
If you find yourself using the equality operator on dates – think twice and be sure you really don’t want greater than and less than instead.
And by the way, be careful with the BETWEEN operator as it is inclusive on both endpoints!

Reply
1. Sentinel
  
  September 28, 2016 at 10:53 pm
  
  However you can still use the between operator with the addition of one additional predicate to make it half open instead of fully inclusive:
  
  where created between date ‘2016-09-20’ and date ‘2016-09-20’ + 1
  and created date ‘2016-09-20’ + 1
  
  Reply
  1. Sentinel
    
    September 28, 2016 at 10:54 pm
    
    Looks like my not equals got stripped in the second predicate perhaps this alternate syntax will display properly:
    
    and create != date ‘2016-09-20’ + 1
    
    Reply
jkstill

September 24, 2016 at 12:03 am

Thank you Paul Muller. You just saved me some typing.

Reply
cuisine

June 20, 2019 at 3:24 pm

Excellent article. j’apprécie beaucoup votre blog

Reply