Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query by Partitions Dynamically

user12002352Jan 4 2013 — edited Jan 14 2013
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 11 2013
Added on Jan 4 2013
11 comments
1,291 views