11 Replies Latest reply: Jan 14, 2013 11:06 AM by user12002352 RSS

    Query by Partitions Dynamically

    user12002352
      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
          >
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          >
                          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
                            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
                              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