My Oracle Support Banner

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

Last updated on FEBRUARY 01, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 18.7.0.0.0 and later
Information in this document applies to any platform.

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

-----

 

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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