Display dates in explain plan as literals

user_2625561 0
Viewed 79
SELECT DISTINCT i.name, i.daily_cost
FROM edu_event.items i
WHERE i.purchase_date BETWEEN DATE'2015-01-01' AND DATE'2015-12-31';

For this query, the following execution plan is selected:

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |  5750 |   213K|   206   (1)| 00:00:01 |
|   1 |  HASH UNIQUE       |       |  5750 |   213K|   206   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| ITEMS |  5750 |   213K|   205   (1)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("I"."PURCHASE_DATE">=TO_DATE('2015-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "I"."PURCHASE_DATE"<=TO_DATE('2015-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

As you can see, the original date literals are replaced with to_date functions. Can I disable it somehow? This format is larger, but it doesn't give more information, so I don't need it. Can SQL Developr convert it back programmatically?

oracle-sqldeveloper sql-execution-plan oracle sql
2 Answers

This is what Oracle does behind the scenes, and isn't a special SQL Developer view. The format can't be modified:

SQL> explain plan for
  2  with rec (d) as (select sysdate from dual)
  3  select *
  4    from rec
  5   where d > date '2018-01-01';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

   1 - filter(SYSDATE@!>TO_DATE(' 2018-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

15 rows selected.

You can remove the predicate information by using format parameter of DBMS_XPLAN.DISPLAY(), but not to modify what it looks like

SQL> select * from table(dbms_xplan.display(null, null, '-PREDICATE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4034615273

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FILTER          |      |       |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

9 rows selected.
user_4587410

As noted in other answer this is how Oracle rewrites the DATEliteral.

For example while defining a partitioned table using DATE literals as below..

create table TEST 
(id NUMBER,
 PART_DATE DATE
)
PARTITION BY RANGE (PART_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( 
   PARTITION p1 VALUES LESS THAN (DATE'2018-08-01'), 
   PARTITION p2 VALUES LESS THAN (DATE'2018-09-01'),
   PARTITION p3 VALUES LESS THAN (DATE'2018-10-01')
);

... you see the the HIGH_VALUESof the partitions as to_datefunction calls:

TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

What is important - Oracle understands the value of the date.

For example this query ...

select * from TEST where PART_DATE = DATE'2018-09-15';

... produces following execution plan

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |    22 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |
|*  2 |   TABLE ACCESS FULL    | TEST |     1 |    22 |     2   (0)| 00:00:01 |     3 |     3 |
-----------------------------------------------------------------------------------------------

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

   2 - filter("PART_DATE"=TO_DATE(' 2018-09-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note, that FILTER predicate uses the to_datefunction call, but the Pstart and Pstop point to a single partition (#3)

This means, Oracle knows while parsing based on the DATE value which partition will be accessed (and could take profit from this knowledge while optimizing).

So in short - yes, there is a function call around the date string, but this is not comparable with a situation where function call on a column prohibits index access.

On the contrary while accessing the above table wit a predicate PART_DATE = SYSDATE you get an execution plan pointing to a KEY partition.

 |*  2 |   TABLE ACCESS FULL    | TEST |     1 |    22 |     2   (0)| 00:00:01 |   KEY |   KEY |

This means Oracle knows it will access only one partition, but doesn't know which one.

user_48081220
Related