Skip to main content

Posts

Featured

VW_TE_2 view and the wrong execution plan when Table Expansion take place

Our customer has a problem today.
The very simple queries take very different time :

select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < to_date('01.01.2015','dd.mm.yyyy');
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < trunc(sysdate-1112);
select min(a.ENTRYDATE) from ORDERS_BASE a where a.ENTRYDATE < date '2015-01-01';

First query takes 850 second.
2nd and 3rd queries take .04 second.

Find the difference !
:(

The queries were tested at 17-JAN-2018 so trunc(sysdate-1112) == to_date('01.01.2015','dd.mm.yyyy') that day.

For clarity and for checking the difference in data types i did dump of these values:

SQL>  select dump(trunc(sysdate-1112)) from dual;

DUMP(TRUNC(SYSDATE-1112))
--------------------------------------------------------------------------------
Typ=13 Len=8: 223,7,1,1,0,0,0,0

SQL> select dump(to_date('01.01.2015','dd.mm.yyyy')) from dual;

DUMP(TO_DATE('01.01.2015','DD.M
-…

Latest Posts

How to deinstall Oracle Database software

[INS-35354] The system on which you are attempting to install Oracle RAC is not part of valid cluster