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?
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.