KEY VECTOR TRANSFORMATION doesn't work With SYSDATE (Doc ID 2761736.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Performance issue with KEY VECTOR_TRANSFORM difference found when data retrieve using sysdate and using literal for same date value.

Because of this query is taking more time with sysdate as compare to literal value

With SYSDATE

32 - filter(TRUNC(SYSDATE@!,'fmmm')>TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))

With literal date

29 - inmemory((SYS_OP_KEY_VECTOR_FILTER("F"."K3",:KV0002) AND SYS_OP_KEY_VECTOR_FILTER("F"."K2",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("F"."K1",:KV0000)))

filter((SYS_OP_KEY_VECTOR_FILTER("F"."K3",:KV0002) AND SYS_OP_KEY_VECTOR_FILTER("F"."K2",:KV0001) AND SYS_OP_KEY_VECTOR_FILTER("F"."K1",:KV0000)))

Changes

Test Case

create table d1(k1 not null, d1 not null) inmemory as select rownum, mod(rownum, 10) from dual connect by rownum <= 400;

create table d2(k2 not null, d2 not null) inmemory as select rownum, mod(rownum, 10) from dual connect by rownum <= 400;

create table d3(k3 not null, d3 not null) inmemory as select rownum, trunc(to_date('11/12/2020','DD/MM/YYYY')-rownum) from dual connect by rownum <= 700;

create table f(k1 not null, k2 not null, k3 not null , m1 not null) inmemory as select k1, k2, k3, d1 + d2 from d1 cross join d2 cross join d3 ;

Test SQL Statement

SELECT /*+ VECTOR_TRANSFORM gather_plan_statistics */

d1, d2, d3, sum(m1)

FROM f

JOIN d1 ON f.k1 = d1.k1

JOIN d2 ON f.k2 = d2.k2

JOIN d3 ON f.k3 = d3.k3

WHERE d3.d3 >= TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM')

AND d3.d3 < TRUNC (SYSDATE, 'MM')

AND d1.d1 = 8

AND d2.d2 = 8

group by d1,d2,d3;

select * from dbms_xplan.display_cursor(format=>'ALLSTATS LAST');

SELECT /*+ VECTOR_TRANSFORM gather_plan_statistics */

d1, d2, d3, sum(m1)

FROM f

JOIN d1 ON f.k1 = d1.k1

JOIN d2 ON f.k2 = d2.k2

JOIN d3 ON f.k3 = d3.k3

WHERE d3.d3 >= to_date('01/11/2020','DD/MM/YYYY')

AND d3.d3 < to_date('01/12/2020','DD/MM/YYYY')

AND d1.d1 = 8

AND d2.d2 = 8

group by d1,d2,d3;

Predicate Information from 1st query

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter(TRUNC(SYSDATE@!,'fmmm')>TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))

6 - filter("D1"."D1"=8)

10 - filter(TRUNC(SYSDATE@!,'fmmm')>TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))

11 - filter("D2"."D2"=8)

15 - filter(TRUNC(SYSDATE@!,'fmmm')>TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))

16 - filter(("D3"."D3"<TRUNC(SYSDATE@!,'fmmm') AND "D3"."D3">=TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm')))

18 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_9"=INTERNAL_FUNCTION("C0") AND "ITEM_11"=INTERNAL_FUNCTION("C0"))

32 - filter(TRUNC(SYSDATE@!,'fmmm')>TRUNC(ADD_MONTHS(SYSDATE@!,(-1)),'fmmm'))

Note

-----

• vector transformation used for this statement

Predicate Information from 2nd query

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter("D1"."D1"=8)

9 - filter("D2"."D2"=8)

13 - filter(("D3"."D3"<TO_DATE(' 2020-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "D3"."D3">=TO_DATE(' 2020-11-01 00:00:00', 'syyyy-mm-dd

hh24:mi:ss')))

15 - access("ITEM_10"=INTERNAL_FUNCTION("C0") AND "ITEM_9"=INTERNAL_FUNCTION("C0") AND "ITEM_11"=INTERNAL_FUNCTION("C0"))

29 - filter((SYS_OP_KEY_VECTOR_FILTER("F"."K3",:KV0002) AND SYS_OP_KEY_VECTOR_FILTER("F"."K1",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("F"."K2",:KV0001)))

Note

-----

- vector transformation used for this statement

Here we can say that when SYSDATE used "SYS_OP_KEY_VECTOR_FILTER" not reported in predicate information

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.