oracle-databasedatetime

Oracle SQL TO_DATE and TO_TIMESTAMP

I have two queries in Oracle SQL that are equivalent.

SELECT ... FROM TABLE WHERE timestamp = TO_DATE('2017-07-01', 'YYYY-MM-DD')

and

SELECT ... FROM TABLE WHERE 
timestamp >= TO_TIMESTAMP('2017-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
timestamp < TO_TIMESTAMP('2017-07-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

Generally, I need to run this everyday (automated) so the first query will suffice for the application. However, for the first few runs I need some custom date-time boundaries, so I might manually intervene and use the second query instead.

What I observed is that the first one run faster. Under the hood, is this really the case? Is the performance difference significant enough? Can someone explain?

Solution

  • Devil is in the details.

    1. How many records in the table?

    2. How many records satisfy

      timestamp = TO_DATE('2017-07-01', 'YYYY-MM-DD')

    3. How many records satisfy

      timestamp >= TO_TIMESTAMP('2017-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND timestamp < TO_TIMESTAMP('2017-07-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

    4. Does the table have stats collected? Does the timestamp column has histogram statistics?

    5. Do you have an index on the timestamp column? Or it might be (sub)partitioned by timestamp?

    It might be easier just send DDLs for both table and index - it will be really helpful.

    Assuming you have timestamp column indexed, for the first query you're looking up by one value, in another case it is a range of value. So depending on stats and many other factors some of which are mentioned above, Oracle can choose to switch to full table scan for example, if it thinks that the second predicate returns much more rows so that its less expensive to read table directly.

    I know it might be more questions than answers, but Oracle Database is very flexible and with flexibility comes complexity. Hope some of the above information will be helpful.

    Also, a simple explain plan, sqlplus autrotrace or best case a 10053 trace or a 10046 trace can show a more definitive answer what's going on there.