Oracle SQL plans: filter predicate INTERNAL_FUNCTION()

INTERNAL_FUNCTION in combination with datatype conversion

create table tq84_A (
   nm  number,
   dt  varchar2(20)  -- Note: varchar2 instead of date!
);

explain plan for
select
   sum(nm)
from
   tq84_A
where
   dt < date '2019-05-18'
;

select * from table(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     1 |    55 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |        |     1 |    55 |            |          |
|*  2 |   TABLE ACCESS FULL| TQ84_A |     1 |    55 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter(INTERNAL_FUNCTION("DT")<TO_DATE(' 2019-05-18 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

drop table    tq84_A;

Sometimes only shown in dbms_xplan.display_cursor

create table tq84_A (
   nm   number,
   tx   varchar2(10)
);

explain plan for
select
   count(*)
from
   tq84_A
where
   nm =  7  or
   tx = 'Z'
;

select * from table(dbms_xplan.display);

-- because of display_cursor
set serveroutput off

select
   count(*)
from
   tq84_A
where
   nm =  7  or
   nm =  9  or
   tx = 'Z'
;

select * from table(dbms_xplan.display_cursor);

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |    35 |            |          |
|*  2 |   TABLE ACCESS FULL| TQ84_A |     1 |    35 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - filter((INTERNAL_FUNCTION("NM") OR "TX"='Z'))

drop table tq84_A;

set serveroutput on

Links