The simple fix to date queries

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.

8 thoughts on “The simple fix to date queries

  1. 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’;

    • 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

    • 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

  2. 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!

    • 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

      • 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s