Skip to Main Content
  • Questions
  • In-Memory Column Store affects results of query.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: September 06, 2021 - 4:27 pm UTC

Last updated: September 09, 2021 - 5:39 am UTC

Version: 19.11.0.0.0

Viewed 1000+ times

You Asked

Experienced this across 3 different instances now. On 19.11.0.0.

When IMCS is enabled on a partition which is composite-partitioned. Partition is on a DATE column and SUB-PARTS are on a VARCHAR2 column.
IMCS enabled on the latest partition changes the query plan such that INMEMORY FULL Scan is used incorrectly and retrieves no results.
The problem manifests if the query uses either just a string like '01-SEP-21' or even the TO_DATE('01-SEP-21','DD-MON-YY') function.

However, using the same query returns the correct results when either
(a) INMEMORY is disabled ( Hence confirming my suspicion that its not jus the literal date format used )
OR
(b) The query is changed to use the literal like DATE '2021-09-01'

It does look like the use of implicit string-to-date conversion with INMEMORY is interracting in some way

with LiveSQL Test Case:

and Connor said...

I know LiveSQL isn't ideal for these demos, because you don't get access to things like in-memory, but I can't repro on my instance

SQL> create table RE
  2  (
  3  cobdate date,
  4  entity  varchar(20),
  5  label   varchar2(20),
  6  value   float(126)
  7  )
  8  PARTITION BY RANGE (COBDATE) INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
  9    SUBPARTITION BY LIST (ENTITY)
 10    SUBPARTITION TEMPLATE (
 11      SUBPARTITION "PART_A" VALUES ( 'A' ),
 12      SUBPARTITION "PART_B" VALUES ( 'B' ),
 13      SUBPARTITION "PART_C" VALUES ( 'C' ),
 14      SUBPARTITION "PART_OTHERS" VALUES ( DEFAULT ) )
 15  (PARTITION "P_20210830"  VALUES LESS THAN (TO_DATE(' 2021-08-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 16  )
 17    PARALLEL 8
 18  /

Table created.

SQL> insert into RE values ( DATE '2021-08-30', 'A', 'AAA', 11.11 );

1 row created.

SQL> insert into RE values ( DATE '2021-08-30', 'B', 'BBB', 22.11 );

1 row created.

SQL> insert into RE values ( DATE '2021-08-30', 'XXX', 'XXX', 33.11 );

1 row created.

SQL> insert into RE values ( DATE '2021-09-01', 'A', 'AAA', 11.22 );

1 row created.

SQL> insert into RE values ( DATE '2021-09-01', 'B', 'BBB', 22.22 );

1 row created.

SQL>
SQL> select partition_name from user_Tab_partitions where table_name='RE';

PARTITION_NAME
------------------------------
P_20210830
SYS_P657

SQL>
SQL> alter table RE modify partition SYS_P657 inmemory memcompress for capacity low priority medium;

Table altered.

SQL> select round(i.inmemory_size/1024/1024,1) MB_mem, round(i.bytes/1024/1204,1) MB_disk, i.* from v$im_segments i order by segment_name,partition_name
  2  @pr
==============================
MB_MEM                        : 1.3
MB_DISK                       : 6.7
OWNER                         : MCDONAC
SEGMENT_NAME                  : RE
PARTITION_NAME                : SYS_SUBP653
SEGMENT_TYPE                  : TABLE SUBPARTITION
TABLESPACE_NAME               : USERS
INMEMORY_SIZE                 : 1310720
BYTES                         : 8241152
BYTES_NOT_POPULATED           : 0
POPULATE_STATUS               : COMPLETED
INMEMORY_PRIORITY             : MEDIUM
INMEMORY_DISTRIBUTE           : AUTO
INMEMORY_DUPLICATE            : NO DUPLICATE
INMEMORY_COMPRESSION          : FOR CAPACITY LOW
INMEMORY_SERVICE              : DEFAULT
INMEMORY_SERVICE_NAME         :
IS_EXTERNAL                   : FALSE
CON_ID                        : 0
==============================
MB_MEM                        : 1.3
MB_DISK                       : 6.7
OWNER                         : MCDONAC
SEGMENT_NAME                  : RE
PARTITION_NAME                : SYS_SUBP654
SEGMENT_TYPE                  : TABLE SUBPARTITION
TABLESPACE_NAME               : USERS
INMEMORY_SIZE                 : 1310720
BYTES                         : 8241152
BYTES_NOT_POPULATED           : 0
POPULATE_STATUS               : COMPLETED
INMEMORY_PRIORITY             : MEDIUM
INMEMORY_DISTRIBUTE           : AUTO
INMEMORY_DUPLICATE            : NO DUPLICATE
INMEMORY_COMPRESSION          : FOR CAPACITY LOW
INMEMORY_SERVICE              : DEFAULT
INMEMORY_SERVICE_NAME         :
IS_EXTERNAL                   : FALSE
CON_ID                        : 0

PL/SQL procedure successfully completed.

SQL> insert into RE values ( DATE '2021-09-01', 'C', 'CCC', 99.99 );

1 row created.

SQL> select * from re where cobdate= '01-SEP-21' and entity ='C';

COBDATE   ENTITY               LABEL                     VALUE
--------- -------------------- -------------------- ----------
01-SEP-21 C                    CCC                       99.99

SQL> select * from re where cobdate= DATE '2021-09-01' and entity ='C';

COBDATE   ENTITY               LABEL                     VALUE
--------- -------------------- -------------------- ----------
01-SEP-21 C                    CCC                       99.99

SQL> select round(i.inmemory_size/1024/1024,1) MB_mem, round(i.bytes/1024/1204,1) MB_disk, i.* from v$im_segments i order by segment_name,partition_name
  2  @pr
==============================
MB_MEM                        : 1.3
MB_DISK                       : 6.7
OWNER                         : MCDONAC
SEGMENT_NAME                  : RE
PARTITION_NAME                : SYS_SUBP653
SEGMENT_TYPE                  : TABLE SUBPARTITION
TABLESPACE_NAME               : USERS
INMEMORY_SIZE                 : 1310720
BYTES                         : 8241152
BYTES_NOT_POPULATED           : 0
POPULATE_STATUS               : COMPLETED
INMEMORY_PRIORITY             : MEDIUM
INMEMORY_DISTRIBUTE           : AUTO
INMEMORY_DUPLICATE            : NO DUPLICATE
INMEMORY_COMPRESSION          : FOR CAPACITY LOW
INMEMORY_SERVICE              : DEFAULT
INMEMORY_SERVICE_NAME         :
IS_EXTERNAL                   : FALSE
CON_ID                        : 0
==============================
MB_MEM                        : 1.3
MB_DISK                       : 6.7
OWNER                         : MCDONAC
SEGMENT_NAME                  : RE
PARTITION_NAME                : SYS_SUBP654
SEGMENT_TYPE                  : TABLE SUBPARTITION
TABLESPACE_NAME               : USERS
INMEMORY_SIZE                 : 1310720
BYTES                         : 8241152
BYTES_NOT_POPULATED           : 0
POPULATE_STATUS               : COMPLETED
INMEMORY_PRIORITY             : MEDIUM
INMEMORY_DISTRIBUTE           : AUTO
INMEMORY_DUPLICATE            : NO DUPLICATE
INMEMORY_COMPRESSION          : FOR CAPACITY LOW
INMEMORY_SERVICE              : DEFAULT
INMEMORY_SERVICE_NAME         :
IS_EXTERNAL                   : FALSE
CON_ID                        : 0

PL/SQL procedure successfully completed.

SQL>


Can you do your same demo again on your system but with the changes as below:

select * from re where cobdate= '01-SEP-21' and entity ='C';
select * from re where cobdate= DATE '2021-09-01' and entity ='C';

becomes

select /*+ gather_plan_statistics */ * from re where cobdate= '01-SEP-21' and entity ='C';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select /*+ gather_plan_statistics */ * from re where cobdate= DATE '2021-09-01' and entity ='C';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


and paste back the top to bottom test output (like mine above) as a review.




Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Query Plans show differences

Dava, September 07, 2021 - 9:55 am UTC

Thanks for suggestion. I did as you suggested. The only slight difference ( and to prove my point about seeing the same problem using the TO_DATE() instead ) is that I changed the first query to use TO_DATE()now - the plan is the same as using the 'DD-MON-YY' string anyway.
Clearly the plan changes between the 2 queries. The 1st produces zero rows and the 2nd produces the correct rows.
Again, my system(s) say their versions are 19.11.0.0.0.

SQL_ID  gu93bw2bh1pj0, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from re where cobdate= 
TO_DATE('2021-09-01','YYYY-MM-DD') and entity ='C'
 
Plan hash value: 3372703730
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |      1 |        |      0 |00:00:00.04 |     328 |
|   1 |  PX COORDINATOR                  |          |      1 |        |      0 |00:00:00.04 |     328 |
|   2 |   PX SEND QC (RANDOM)            | :TQ10000 |      0 |      3 |      0 |00:00:00.01 |       0 |
|   3 |    VIEW                          | VW_TE_2  |      0 |      3 |      0 |00:00:00.01 |       0 |
|   4 |     UNION-ALL                    |          |      0 |        |      0 |00:00:00.01 |       0 |
|*  5 |      FILTER                      |          |      0 |        |      0 |00:00:00.01 |       0 |
|   6 |       PX BLOCK ITERATOR          |          |      0 |      3 |      0 |00:00:00.01 |       0 |
|*  7 |        TABLE ACCESS INMEMORY FULL| RE       |      0 |      3 |      0 |00:00:00.01 |       0 |
|*  8 |      FILTER                      |          |      0 |        |      0 |00:00:00.01 |       0 |
|   9 |       PX BLOCK ITERATOR          |          |      0 |      3 |      0 |00:00:00.01 |       0 |
|* 10 |        TABLE ACCESS INMEMORY FULL| RE       |      0 |      3 |      0 |00:00:00.01 |       0 |
|  11 |      PX BLOCK ITERATOR           |          |      0 |      1 |      0 |00:00:00.01 |       0 |
|* 12 |       TABLE ACCESS INMEMORY FULL | RE       |      0 |      1 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter(NULL IS NOT NULL)
   7 - inmemory(:Z>=:Z AND :Z<=:Z AND "COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
       filter("COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   8 - filter(NULL IS NOT NULL)
  10 - inmemory(:Z>=:Z AND :Z<=:Z AND "COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
       filter("COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  12 - inmemory(:Z>=:Z AND :Z<=:Z AND "COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss'))
       filter("COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
Note
-----
   - Degree of Parallelism is 8 because of table property



----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
select /*+ gather_plan_statistics */ * from re where cobdate= DATE 
'2021-09-01' and entity ='C'
 
Plan hash value: 523297854
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |      1 |00:00:00.04 |      41 |
|   1 |  PX COORDINATOR               |          |      1 |        |      1 |00:00:00.04 |      41 |
|   2 |   PX SEND QC (RANDOM)         | :TQ10000 |      0 |      1 |      0 |00:00:00.01 |       0 |
|   3 |    PX BLOCK ITERATOR          |          |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS INMEMORY FULL| RE       |      0 |      1 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - inmemory(:Z>=:Z AND :Z<=:Z AND "COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))
       filter("COBDATE"=TO_DATE(' 2021-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
Note
-----
   - Degree of Parallelism is 8 because of table property



Connor McDonald
September 08, 2021 - 7:01 am UTC

VW_TE_... stands for "table expansion".

So we can see the bug now with the following:

SQL> select /*+ EXPAND_TABLE(t) */ * from re t where cobdate= to_date('01-SEP-21') and entity ='C';

no rows selected

SQL> select * from re t where cobdate= to_date('01-SEP-21') and entity ='C';

COBDATE   ENTITY               LABEL                     VALUE
--------- -------------------- -------------------- ----------
01-SEP-21 C                    CCC                       99.99


I can log a bug, but I recommend that you do it because bugs carry more weight when they come from customers.

Thanks. I have raised an SR

Dave, September 08, 2021 - 9:31 am UTC

Thanks for confirming my thoughts : that this is probably a bug.
I have followed your advice and raise a SR using my client's Support Identifier.
Let's hope for a patch soon addresses this issue.
Thanks for quick repsonses.
Connor McDonald
September 09, 2021 - 5:39 am UTC

If you can email the SR# to asktom_us@oracle.com with subject line of "9545553100346975543" , we'll try keep track of it

(no drama if you'd prefer not)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database