This discussion is archived
5 Replies Latest reply: Apr 11, 2013 8:11 AM by user5716448 RSS

Determine partition range start and stop for a partition

user5716448 Explorer
Currently Being Moderated
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) Expert
    Currently Being Moderated
    have you take a look into dba_tab_partitions ?
  • 2. Re: Determine partition range start and stop for a partition
    rp0428 Guru
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Explorer
    Currently Being Moderated
    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

Legend

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