5 Replies Latest reply: Apr 11, 2013 10:11 AM by user5716448 RSS

    Determine partition range start and stop for a partition

    user5716448
      Hi,

      Using oracle 11.2.0.3

      We are using range interval partitioning and partition sget generated automatically. (range-hash) intervbal 1 month

      Whilst can check table manually and see start and end range of partition is it possible to query data dictionary to see
      the satrt and stop range as wish to have script to automatically rename the system generated partitions based on the range involved e.g if generated partition has max date value

      3062013 waoul want PART_201306

      Thanks
        • 1. Re: Determine partition range start and stop for a partition
          Mark Malakanov (user11181920)
          have you take a look into dba_tab_partitions ?
          • 2. Re: Determine partition range start and stop for a partition
            rp0428
            >
            Using oracle 11.2.0.3

            We are using range interval partitioning and partition sget generated automatically. (range-hash) intervbal 1 month

            Whilst can check table manually and see start and end range of partition is it possible to query data dictionary to see
            the satrt and stop range as wish to have script to automatically rename the system generated partitions based on the range involved e.g if generated partition has max date value

            3062013 waoul want PART_201306
            >
            Why do it the hard way? You don't need to see the current partition names or ranges. Just use the FOR PARTITION syntax.

            See the VLDB and Partitioning Guide
            http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1008006
            >
            Renaming Partitions
            It is possible to rename partitions and subpartitions of both tables and indexes. One reason for renaming a partition might be to assign a meaningful name, as opposed to a default system name that was assigned to the partition in another maintenance operation.

            All partitioning methods support the FOR(value) method to identify a partition. You can use this method to rename a system-generated partition name into a more meaningful name. This is particularly useful in interval or interval-* partitioned tables.
            >
            Try this sample code to see what I mean:
            drop table emp_part
            
            CREATE TABLE EMP_PART  
            PARTITION BY RANGE (HIREDATE)
            INTERVAL( NUMTOYMINTERVAL(1,'YEAR'))(
            partition p_2011 values less than (to_date('01/01/2012','DD/MM/YYYY'))
            )
            AS SELECT * FROM EMP where 1 = 2
            
            insert into emp_part (empno, hiredate) values (1, sysdate - 365)
            
            insert into emp_part (empno, hiredate) values (1, sysdate)
            
            select partition_name, partition_position from user_tab_partitions order by partition_position
            
            PARTITION_NAME     PARTITION_POSITION
            P_2011     1
            SYS_P63     2
            SYS_P64     3
            Those are your two system-generated names. Now, since you know (or should know) a value (ANY value) for a partition you can use that value in the rename like this:
            alter table emp_part rename partition for (to_date('04/10/2012', 'mm/dd/yyyy')) to P_2012
            
            alter table emp_part rename partition for (to_date('04/10/2013', 'mm/dd/yyyy')) to P_2013
            
            select partition_name, partition_position from user_tab_partitions order by partition_position
            
            PARTITION_NAME     PARTITION_POSITION
            P_2011     1
            P_2012     2
            P_2013     3
            No muss, no fuss.
            • 3. Re: Determine partition range start and stop for a partition
              user5716448
              Thanks for replies.

              Have triied following sql but despite partition keyword in from clause is not using partition pruning.
              Table belwo retailer_transaction date-range partitioned 1 per month.


              If have to explictly use partitiong_date as condition in where clause to get partition pruning.

              Need to ab able to determine what start and stop dates for that month is automatically.

              Checked user_tab_partitions as have sql geneerator can get partition names but doesn't show partition start an dstop ranges.

              How can I achieve this

              sql gneerator starts
                for p in
                   
                 ( select U.PARTITION_NAME
                   from ALL_tab_partitions u
                   where u.table_name = 'RETAILER_TRANSACTION'
                   and u.partition_name = 'PART_201001'
                   )
                 loop
                     v_sql := q'[create table rt_sum_]'||p.partition_name||q'[ parallel nologging
              as
              SELECT 
               MAX(rt.outlet_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as outlet_id,
              MAX(rt.plant_issue_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as plant_issue_id,
              MAX(rt.cus_plant_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as cus_plant_id,
              MAX(rt.distributor_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as distributor_id,
              MAX(rt.publisher_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as publisher_id,
              MAX(rt.sas_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as sas_id,
              MAX(rt.product_band_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as product_band_id,
              MAX(r.mult_id) KEEP (DENSE_RANK FIRST ORDER BY  c.day_dte) as mult_id,
                    r.out_num,
                     m.plis_issue_num,
                     min(rt.currency_id) currency_id, -- to fix cutover prob if mix £ and E then forces to E 
                     max(rt.currency_convertor_id) currency_convertor_id,
                     sum(case when rtrt.TYP_MAIN_SUPPLY_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) main_supply,
                     sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) -
                     sum(case when rtrt.TYP_MAIN_SUPPLY_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) extra_supply,
                     sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) SALES,
                     sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) RETURNS,
                     sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) CREDITS,
                     sum(case when rtrt.TYP_RETURN_FLAG = 1 AND c.day_dte < m.plis_recall_date then rt.TRANSACTION_QUANTITY ELSE 0 end) early_returns,
                     sum(case when rtrt.TYP_CRD_MANAGER_CREDIT_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end) managers_credit_qty, -- TO CHECK!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                    decode(sign(sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.TRANSACTION_QUANTITY ELSE 0 end)),1,null,( case when sysdate < max(m.plis_claim_end_date) then 'P' ELSE 'Y' end )) sellout_type, -- null= not soldout, 'P'= potential sellout, 'Y'=Sellout (flag based on claim end date)
              -- retail
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.retail_value_excl_vat ELSE 0 end) retail_inv_val_excl_vat,
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.retail_value_vat ELSE 0 end)  retail_inv_val_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.retail_value_excl_vat ELSE 0 end)  retail_ret_val_excl_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.retail_value_vat ELSE 0 end)  retail_ret_val_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.retail_value_excl_vat ELSE 0 end)  retail_crd_val_excl_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.retail_value_vat ELSE 0 end)  retail_crd_val_vat,
              -- trade
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.trade_value_excl_vat ELSE 0 end)  trade_inv_val_excl_vat,
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.trade_value_vat ELSE 0 end)  trade_inv_val_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.trade_value_excl_vat ELSE 0 end)  trade_ret_val_excl_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.trade_value_vat ELSE 0 end)  trade_ret_val_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.trade_value_excl_vat ELSE 0 end)  trade_crd_val_excl_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.trade_value_vat ELSE 0 end)  trade_crd_val_vat,
              -- cost
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.cost_value_excl_vat ELSE 0 end)  cost_inv_val_excl_vat,
              sum(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.cost_value_vat ELSE 0 end)  cost_inv_val_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.cost_value_excl_vat ELSE 0 end)  cost_ret_val_excl_vat,
              sum(case when rtrt.TYP_RETURN_FLAG = 1 then rt.cost_value_vat ELSE 0 end)  cost_ret_val_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.cost_value_excl_vat ELSE 0 end)  cost_crd_val_excl_vat,
              sum(case when rtrt.TYP_CREDIT_FLAG = 1 then rt.cost_value_vat ELSE 0 end)  cost_crd_val_vat,
              --
                   min(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.day_id ELSE null end) first_supply_day_id,
                   max(case when rtrt.TYP_COPIES_INVOICED_FLAG = 1 then rt.day_id ELSE null end) last_supply_day_id,
                   min(case when rtrt.TYP_RETURN_FLAG = 1 then rt.day_id ELSE null end) first_return_day_id,
                   max(case when rtrt.TYP_RETURN_FLAG = 1 then rt.day_id ELSE null end) last_return_day_id
              FROM
                dw.MEDIA m,
                dw.RETAILER_TRN_REPORT_TYPE rtrt,
                dw.RETAILER_TRANSACTION partition (]'||p.partition_name||q'[) rt,
                dw.RETAILER r,
              --  dw.WHOLESALER w,
                dw.CALENDAR  c
              WHERE
              --rt.CUS_PLANT_ID=w.DIMENSION_KEY  
              --AND 
                rt.OUTLET_ID=r.DIMENSION_KEY  
              AND  rt.PLANT_ISSUE_ID = m.DIMENSION_KEY  
              AND   rtrt.DIMENSION_KEY=rt.REPORTING_TRN_TYPE_ID  
              AND  rt.DAY_ID=c.DIMENSION_KEY  
              --and rt.partitioning_date = m.plis_partitioning_date
              -- and m.PLIS_HANDLED_YEAR = 2013--> 2011
              group by 
                r.out_num,
                m.plis_issue_num]';
                
                
                 
              EXECUTE IMMEDIATE(V_SQL);
              
              INSERT INTO RTRN_PART_SUM_AUDIT
              (PARTITION_NAME, DATE_CREATED)
              VALUES(P.PARTITION_NAME, SYSDATE);
              
              COMMIT;   
                 
                 
                 end loop;
                 
              • 4. Re: Determine partition range start and stop for a partition
                rp0428
                >
                Checked user_tab_partitions as have sql geneerator can get partition names but doesn't show partition start an dstop ranges.
                >
                Range partitions do NOT have 'start and stop' ranges. They have a high value and a position id.

                Data with partition key values < the high value and greater than or equal to the 'high value' of the partition with the next lower 'position id' goes into that partition.

                You still haven't described what you need 'start' and 'stop' for.
                • 5. Re: Determine partition range start and stop for a partition
                  user5716448
                  Hi,

                  It was to try and use the between opertaor in hope that this would force partition pruning and so make query quicker

                  between <start_date> and <en_date> of partition

                  Thnaks