This discussion is archived
11 Replies Latest reply: Jan 14, 2013 9:06 AM by user12002352 RSS

Query by Partitions Dynamically

user12002352 Newbie
Currently Being Moderated
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.
  • 1. Re: Query by Partitions Dynamically
    rp0428 Guru
    Currently Being Moderated
    >
    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.
    >
    Post your evidence that the query is scanning through all the partitions.

    What is it you are trying to do? It doesn't look like you even need the DENSE_RANK function; it looks like you are just comparing the data from two days.

    If just comparing two days why not just put the date in the queries and Oracle will automatically prune the partitions?

    Post some sample data for two days and the result set you want to get from that data.
  • 2. Re: Query by Partitions Dynamically
    user12002352 Newbie
    Currently Being Moderated
    Your correct, I'm looking at comparing the data from two different dates. I used the DENSE_RANK because it was the only way I could come with to get the two most recent partitions. I could use MAX for the most recent partition, but how would I get the second most recent partition?

    As for scanning all the partition, the execution plan is saying it will scan all the partitions.

    Edited by: user12002352 on Jan 4, 2013 2:22 PM
  • 3. Re: Query by Partitions Dynamically
    Solomon Yakobson Guru
    Currently Being Moderated
    You got yourself into a chicken and egg situation. In order to find out which rows are first and second in created_on descending order Oracle needs to scan whole table. Unfortunately, optimizer is not smart enough to realize it is partitioned table and do something like:

    Since created_on is partitioning column and we need to rumber rows in created_on descending order, let me start from last existing partinion and if it is empty go to previous one and so on until we find not empty partition. Then number partition rows in created_on desc order and if partition has just one row repeat the process starting from previous partition. This is one of those cases where PL/SQL might be more efficient solution.

    SY.
  • 4. Re: Query by Partitions Dynamically
    onedbguru Pro
    Currently Being Moderated
    you don't really need to do all of that. I have had similar tablespace/partitions and simply make sure that the query explicitly has the partition key defined.

    select a,b,c from tablea where created_on between sysdate-4 and sysdate-3
    intersect
    select a,b,c from tablea where created_on between sysdate-2 and sysdate-1

    I can attest to the fact that it will use the partitions as an "index" and would only use the partitions for <date1> and <date2>.

    The sysdates can be coded in a pl/sql block and derived when the query is executed.
  • 5. Re: Query by Partitions Dynamically
    Solomon Yakobson Guru
    Currently Being Moderated
    onedbguru wrote:
    you don't really need to do all of that.
    sysdate != max date in the table.

    SY.
  • 6. Re: Query by Partitions Dynamically
    onedbguru Pro
    Currently Being Moderated
    Based on the inline view, it looks like you are actually looking at the entire table - not a subset (partititon). Your inline view must only look at the partitions you are interested in....

    Not having your test data, I do not know what the data for day1 and day{n} looks like, but you need to include that in your query...

    NOT TESTED or CHECK for the correct syntax.

    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
    where created_on = sysdate-1 or created_on = sysdate-5
    )
    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
  • 7. Re: Query by Partitions Dynamically
    Solomon Yakobson Guru
    Currently Being Moderated
    onedbguru wrote:
    Your inline view must only look at the partitions you are interested in....
    Not having your test data, I do not know what the data for day1 and day{n} looks like, but you need to include that in your query...
    It is not my query and not my data. And again, OP is looking for an intersect between two highest dates in a table. It has nothing to do with SYSDATE.

    SY.
  • 8. Re: Query by Partitions Dynamically
    onedbguru Pro
    Currently Being Moderated
    apologies.... I thought I was replying to the OP. He wants to use partition pruning... sysdate is just a date to be used for that purpose because the partitioning is on a date column. this is an example not exact code. He will need to extrapolate from the example. Without something to filter the data, it is going to to a FTS. Guaranteed.

    OP,

    execute your query and provide us the explain plan

    set autotrace on time on timing on
    <query here>
  • 9. Re: Query by Partitions Dynamically
    rp0428 Guru
    Currently Being Moderated
    >
    Your correct, I'm looking at comparing the data from two different dates. I used the DENSE_RANK because it was the only way I could come with to get the two most recent partitions. I could use MAX for the most recent partition, but how would I get the second most recent partition?

    As for scanning all the partition, the execution plan is saying it will scan all the partitions.
    >
    I ask you to provide the execution plan. When you ask for help you need to provide the actual information; not your interpretation of it.

    You still haven't defined your actual requirement. Is it to find the latest partition? Or is it instead to find the latest partition that actually has any data?

    To find the latest partition for your use case you could write a simple function that returns the DATE representing the HIGH_VALUE for the partition with the highest PARTITION_POSITION.
    CREATE OR REPLACE FUNCTION SF_GET_PARTITION_HIGH_VALUE (
        p_tablename IN VARCHAR2
    ) RETURN DATE
    IS
       v_highvalue varchar2(2000);
       v_highdate date;
    BEGIN
        SELECT HIGH_VALUE INTO v_highvalue
          FROM USER_TAB_PARTITIONS
         WHERE TABLE_NAME = p_tablename
           AND PARTITION_POSITION =
             (SELECT MAX(PARTITION_POSITION)
              FROM USER_TAB_PARTITIONS
              WHERE TABLE_NAME = p_tablename);
        DBMS_OUTPUT.PUT_LINE(v_highvalue);      
        execute immediate 'select ' || v_highvalue || ' from dual' into v_highdate; 
        RETURN v_highdate;
    END SF_GET_PARTITION_HIGH_VALUE;
    Then you can add a call to the function to get the date to use
    select SF_GET_PARTITION_HIGH_VALUE ('PARTITION_DAILY_TEST') hv,
          sysdate,
          SF_GET_PARTITION_HIGH_VALUE ('PARTITION_DAILY_TEST') - trunc(sysdate) diff      
          FROM DUAL
    
    HV     SYSDATE     DIFF
    1/5/2013     1/4/2013 7:30:47 PM     1
  • 10. Re: Query by Partitions Dynamically
    Solomon Yakobson Guru
    Currently Being Moderated
    onedbguru wrote:
    sysdate is just a date to be used for that purpose because the partitioning is on a date column.
    Yes, sysdate is just a date while OP is looking for a particular, yet unknown, dates: last date in the table and second last date in the table . If such dates would be known Oracle wouldn't have any issues pruning not needed partitions. Problem is such dates are not known upfront and oracle has to look through all partitions in order to obtain them.

    SY.
  • 11. Re: Query by Partitions Dynamically
    user12002352 Newbie
    Currently Being Moderated
    That you very much for your suggestion. I've done some changes to you function and the query no completes in less then a second vs. 11 minutes. Because of the nature of the data I did the following:

    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 trunc(sysdate) NOT NULL
    )
    PARTITION BY RANGE (CREATED_ON)
    INTERVAL( NUMTODSINTERVAL(1,'DAY'))

    CREATE OR REPLACE FUNCTION RDA_STG.sf_get_partition_high_value (p_tablename IN VARCHAR2, p_partition_name IN VARCHAR2)
    RETURN DATE
    IS
    v_highvalue VARCHAR2 (2000);
    v_highdate DATE;
    BEGIN
    SELECT high_value
    INTO v_highvalue
    FROM user_tab_partitions
    WHERE table_name = p_tablename
    AND partition_name = p_partition_name;

    EXECUTE IMMEDIATE 'select ' || v_highvalue || ' from dual' INTO v_highdate;

    RETURN v_highdate;
    END sf_get_partition_high_value;
    /

    CREATE OR REPLACE FORCE VIEW RDA_STG.V_FIND_PARTITION_NAME
    (TABLE_NAME, HIGH_VALUE, PARTITION_NAME, PARTITION_DATE, CREATED_ON,
    RNUM)
    AS
    SELECT table_name, high_value, partition_name, partition_date, partition_date - 1 AS created_on,
    ROW_NUMBER () OVER (PARTITION BY table_name ORDER BY partition_date DESC) AS rnum
    FROM (SELECT table_name, high_value, partition_name,
    sf_get_partition_high_value (table_name, partition_name) AS partition_date
    FROM user_tab_partitions)
    /

    WITH v_inline_view
    AS (SELECT np_tn, np_service, nc_service, nc_tn, rnum
    FROM pt_nc_np_mismatch JOIN v_find_partition_name USING (created_on)
    WHERE table_name = 'PT_NC_NP_MISMATCH')
    SELECT np_tn, np_service, nc_service, nc_tn
    FROM v_inline_view
    WHERE rnum = 1
    INTERSECT
    SELECT np_tn, np_service, nc_service, nc_tn
    FROM v_inline_view
    WHERE rnum = 2

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points