Hello Experts,
Please can someone explain to me why the query below not excluding partitions ( partition pruning ) ?
MEI table is INTERVAL partitioned on the column ETL_SOURCE_APPLY_DTTM which is timestamp(6)
And it is looking for anything greater than the date from LOAD_MASTER table, where CDS_START_DTTM is defined as date..
Please let me know if you need any other details
Thanks in advance.
WITH BID
AS (SELECT *
FROM (SELECT CDC_START_DTTM,
CDC_END_DTTM,
LOAD_CODE,
BATCH_ID,
RANK ()
OVER (PARTITION BY LOAD_CODE ORDER BY LOAD_ID DESC)
AS RNK
FROM ETL_CTRL.LOAD_MASTER
WHERE LOAD_CODE = 1002002 AND STATUS = 'RUNNING')
WHERE RNK = 1)
SELECT
MEI.ETL_SOURCE_CAPTURE_DTTM,
MEI.MID P_MID,
CASE
WHEN INTERFACE_TYPE = 'OFAC'
AND RESPONSE_REQUEST_IND = 'Request'
THEN
'OFAC_REQUEST'
WHEN INTERFACE_TYPE = 'OFAC'
AND RESPONSE_REQUEST_IND = 'Response'
THEN
'OFAC_RESPONSE'
END
P_MSG_STS,
MEI.TIME_STAMP AS P_TIME_STAMP,
MEI.ETL_SOURCE_SYSTEM_INSTANCE,
MEI.ETL_SOURCE_SYSTEM_ORIGIN,
MEI.ETL_SOURCE_CDC_ACTION_CODE,
MEI.ETL_SOURCE_APPLY_DTTM,
BD.BATCH_ID
FROM GPP_E_REPL.MESSAGE_EXTERNAL_INTERACTION MEI, BID BD
where MEI.ETL_SOURCE_APPLY_DTTM > to_timestamp(BD.CDC_START_DTTM,'DD-MM-YYYY HH24:MI:SS')
AND MEI.ETL_SOURCE_APPLY_DTTM <= to_timestamp(BD.CDC_END_DTTM,'DD-MM-YYYY HH24:MI:SS')
AND MEI.INTERFACE_TYPE = 'OFAC'
AND MEI.RESPONSE_REQUEST_IND IN( 'Request','Response')
AND MEI.ETL_SOURCE_CDC_ACTION_CODE = 'I'
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 108M| 13G| | 1842K (1)| | |
| 1 | MERGE JOIN | | 108M| 13G| | 1842K (1)| | |
| 2 | SORT JOIN | | 4703 | 202K| | 31823 (1)| | |
| 3 | VIEW | | 4703 | 202K| | 31822 (1)| | |
| 4 | WINDOW SORT PUSHED RANK | | 4703 | 257K| | 31822 (1)| | |
| 5 | TABLE ACCESS STORAGE FULL| LOAD_MASTER | 4703 | 257K| | 31821 (1)| | |
| 6 | FILTER | | | | | | | |
| 7 | SORT JOIN | | 9245K| 784M| 2006M| 1804K (1)| | |
| 8 | PARTITION RANGE ALL | | 9245K| 784M| | 1627K (1)| 1 |1048575|
| 9 | TABLE ACCESS STORAGE FULL| T_GPP_E_MESSAGE_EXTRNL_INTRCTN | 9245K| 784M| | 1627K (1)| 1 |1048575|
-----------------------------------------------------------------------------------------------------------------------------