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
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.