Query by Partitions Dynamically
I'm using Oracle 11.2
CREATE TABLE PT_NC_NP_MISMATCH
(
NP_TN VARCHAR2(32 BYTE),
NP_SERVICE VARCHAR2(100 BYTE),
NC_SERVICE VARCHAR2(64 BYTE),
NC_TN VARCHAR2(64 BYTE),
CREATED_ON DATE DEFAULT sysdate NOT NULL
)
PARTITION BY RANGE (CREATED_ON)
INTERVAL( NUMTODSINTERVAL(1,'DAY'))
WITH v_inline_view
AS (SELECT np_tn, np_service, nc_service, nc_tn, DENSE_RANK () OVER (ORDER BY created_on DESC) AS rn
FROM pt_nc_np_mismatch)
SELECT np_tn, np_service, nc_service, nc_tn
FROM v_inline_view
WHERE rn = 1
INTERSECT
SELECT np_tn, np_service, nc_service, nc_tn
FROM v_inline_view
WHERE rn = 2
So in a nutshell(example) RN=1 is today's data and RN=2 yesterday data. However the the dates are not fixed so RN=1 could be yesterday and RN=2 could last week, hence dynamic.
Is there a query that would be faster that doesn't scan through all the partitions.