Database version is 12.2.0.2, compatibility 11.2.0.4.
I have a tables with range partitions.
when I am selecting from the tables, using the partition key itself, Oracle is not always aware to the actual partitions that are going to be used.
Since I have some partitions which are very big and some which are not, I want the optimizer to be able to know in Advance (when choosing its plan) to which partitions it is going to access.
I would like to use IN LIST on the field of the partition key.
Since I am passing the true value (and no a bind variable) I would expect Oracle to know exactly which partitions will be use.
Here is an example:
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')));
insert into interval_tab values (1,'1','1',sysdate);
insert into interval_tab values (1,'1','1',sysdate-60);
insert into interval_tab values (1,'1','1',sysdate-100);
insert into interval_tab values (1,'1','1',sysdate-150);
--Here Oracle is aware to the specific 2 partition. partition stop/start is showing the exact two partition id.
SELECT COUNT(*) FROM INTERVAL_TAB WHERE CREATED_DATE =TO_DATE('12-JUN-2019','DD-MON-YYYY')
UNION ALL
SELECT COUNT(*) FROM INTERVAL_TAB WHERE CREATED_DATE =TO_DATE('10-APR-2019','DD-MON-YYYY') ;
--Here Oracle switched to stop/start=KEY. Doesn’t know the specific partition
SELECT COUNT(*) FROM INTERVAL_TAB WHERE CREATED_DATE IN (TO_DATE('12-JUN-2019','DD-MON-YYYY'),TO_DATE('10-APR-2019','DD-MON-YYYY') );
--Also here Oracle switched to stop/start=KEY. Doesn’t know the specific partition
SELECT /*+ use_concat */ COUNT(*) FROM INTERVAL_TAB
WHERE CREATED_DATE = TO_DATE('12-JUN-2019','DD-MON-YYYY')
or CREATED_DATE = TO_DATE('10-APR-2019','DD-MON-YYYY') ;
I would like to write the query with IN , but I want Oracle to be aware to the relevant 2 partitions (maybe transform it to UNION ALL and concatenation).
I have tried using use_concat hint, but failed to receive the need effect.
Any idea how to do it?
Best Regards,
Merav