Unable to use sub-partitioned column in query plan (oracle)

Question

I have a 20 GB table which for some requirement, have to be range partitioned on DATE1 field and list sub-partitioned on DATE2 field. Created a virtual column (VC) on that table to extract numeric month value from DATE2 field and use this VC as the sub-partition key. Per requirement, we'll have 30 partitions on DATE1 and each of them will have 12 sub-partitions on VC. The max size of any sub-partition can be up to 5 GB.

N.B. I could not implement Multi-column partitioning as our inbuilt partition manager does not support them. Also, I could not implement a RANGE-RANGE partition-sub-partitioning as the two Date fields (DATE1 and DATE2) have no-sync in dates coming in them, causing INSERT operations to fail.

Next, I've a simple view created on top of this table. All Date fields including VC are exposed in this view. While querying SELECT * FROM vw; Plan shows PARTITION RANGE SINGLE as expected.

Now, I've a web front end through which I can click on the DATE2 to open some more details. It basically passed DATE2 as filter to query on another table and displays huge records (approx. 3 million).

The PROBLEM is, on clicking the DATE2 field, I'm not able to hit the sub-partition as its based on MONTH value (VC) and not the date.

Thus, I want a PARTITION LIST SINGLE instead of the current PARTITION LIST ALL in plan.

The QUESTION is, how to write a select query to select the SUB partition. I know I have to use the VC in the filter to achieve it, but, the irony is the web-application cannot pass VC in the backend especially when we display only DATE values (not VC). Also, if we CANNOT hit the sub-partition, is there any way we can better the performance by using INDEXES or PARALLELISM?

Please help.

--***********************************************
--TABLE CREATION STATEMENT
--***********************************************
CREATE TABLE M_DTX
(
R_ID NUMBER(3),
R_AMT NUMBER(5),
DATE1 DATE,
DATE2 DATE,
VC NUMBER(2) GENERATE ALWAYS AS (EXTRACT(MONTH FROM DATE2)) 
)
PARTITION BY RANGE (DATE1)
SUBPARTITION BY LIST (VC)
SUBPARTITION TEMPLATE (
SUBPARTITION M1 VALUES (1),
SUBPARTITION M2 VALUES (2),
SUBPARTITION M3 VALUES (3),
SUBPARTITION M4 VALUES (4),
SUBPARTITION M5 VALUES (5),
SUBPARTITION M6 VALUES (6),
SUBPARTITION M7 VALUES (7),
SUBPARTITION M8 VALUES (8),
SUBPARTITION M9 VALUES (9),
SUBPARTITION M10 VALUES (10),
SUBPARTITION M11 VALUES (11),
SUBPARTITION M12 VALUES (12)
TABLESPACE M_DATA
)
(
PARTITION M_DTX_2015060100  
VALUES LESS THAN (
TO_DATE(' 2015-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE( INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
) TABLESPACE M_DATA);

--******************************************
--VIEW ON TOP OF M_DTX:
--******************************************
CREATE OR REPLACE VIEW v_dtx AS
SELECT r_id, TRUNC(date2) date2_dd, vc, SUM(r_amt) amt 
FROM m_dtx WHERE date1 = TRUNC(sysdate)
GROUP BY r_id, TRUNC(date2), vc;

--******************************************
--QUERY FIRED FROM WEB-APPLICATION (AFTER CLICKING ON date2_DD):
--******************************************
SELECT * FROM m_dtx WHERE date1 = trunc(sysdate) AND date2 = ''date2_dd'';
--this is where its bypassing the sub-partition as I could not substitute month or VC ...

Answer 1

The only way I found to hit the subpartition is to write the query this way:

SELECT * 
FROM m_dtx 
WHERE date1 = trunc(sysdate) 
   AND date2 = ''date2_dd'' 
   and vc = EXTRACT(MONTH FROM ''date2_dd'');

So, you don't need to get the vc from the previous screen, because you have the date2.

I've tested with:

SELECT * 
FROM m_dtx 
WHERE date1 = trunc(sysdate) 
  AND date2 = to_date('10-dec-2014','dd-mon-yyyy') 
  and vc = EXTRACT(MONTH FROM to_date('10-dec-2014','dd-mon-yyyy'));