Oracle分割槽表基礎運維-02範圍分割槽

Oracle 分割槽表基礎運維 -02 範圍分割槽

Range Partitioning

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition.

Range partitioning is the most common type of partitioning and is often used with dates. For a table with a date column as the partitioning key, the January-2017 partition would contain rows with partitioning key values from 01-Jan-2017 to 31-Jan-2017.

Each partition has a VALUES LESS THAN clause, that specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this

literal are added to the next higher partition. All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause of the previous partition.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.

建立範圍分割槽表 range_part_tab

create   table  range_part_tab ( id   number , deal_date date , area_code number , contents   varchar2 ( 4000 ))

partition   by   range   ( deal_date )

(

partition  p1 values   less   than   ( TO_DATE ( '2019-06-01' ,   'YYYY-MM-DD' )),

partition  p2 values   less   than   ( TO_DATE ( '2019-07-01' ,   'YYYY-MM-DD' )),

partition  p3 values   less   than   ( TO_DATE ( '2019-08-01' ,   'YYYY-MM-DD' )),

partition  p4 values   less   than   ( TO_DATE ( '2019-09-01' ,   'YYYY-MM-DD' )),

partition  p5 values   less   than   ( TO_DATE ( '2019-10-01' ,   'YYYY-MM-DD' )),

partition  p6 values   less   than   ( TO_DATE ( '2019-11-01' ,   'YYYY-MM-DD' )),

partition  p7 values   less   than   ( TO_DATE ( '2019-12-01' ,   'YYYY-MM-DD' )),

partition  p8 values   less   than   ( TO_DATE ( '2020-01-01' ,   'YYYY-MM-DD' )),

partition  p9 values   less   than   ( TO_DATE ( '2020-02-01' ,   'YYYY-MM-DD' )),

partition  p10 values   less   than   ( TO_DATE ( '2020-03-01' ,   'YYYY-MM-DD' )),

partition  p11 values   less   than   ( TO_DATE ( '2020-04-01' ,   'YYYY-MM-DD' )),

partition  p12 values   less   than   ( TO_DATE ( '2020-05-01' ,   'YYYY-MM-DD' )),

partition  p_max values   less   than   ( maxvalue )

);

插入資料：

---插入一整年日期隨機數和表示福建地區號含義（591到599）的隨機數記錄，共有10萬條，如下：

insert   into  range_part_tab

   ( id ,  deal_date ,  area_code ,   contents )

   select   rownum ,

         to_date ( to_char ( sysdate   -   365 ,   'J' )   +

                  TRUNC ( DBMS_RANDOM.VALUE ( 0 ,   365 )),

                  'J' ),

          ceil ( dbms_random.value ( 590 ,   599 )),

          rpad ( '*' ,   400 ,   '*' )

     from  dual

   connect   by   rownum   <=   100000 ;

commit ;

函式說明：

ceil(n) 取大於等於數值n的最小整數；
floor(n)取小於等於數值n的最大整數 ；

select   ceil ( 9.1 ),   floor ( 9.1 ),   ceil ( 9.9 ),   floor ( 9.9 )   from  dual ;

lpad( string1, padded_length, [ pad_string ] )

rpad( string1, padded_length, [ pad_string ] )

select   rpad ( 'CJC' ,   10 ,   '*' ), lpad ( 'CJC' ,   10 ,   '*' )   from  dual ;

檢視分割槽表資訊

---檢視當前使用者下有哪些分割槽表

select  TABLE_NAME from  user_tables a where  a.partitioned = 'YES' ;

---檢視分割槽表名，分割槽名，表空間等資訊

select  table_name ,  partition_name ,  tablespace_name ,  high_value

   from  user_tab_partitions

  where  table_name =   'RANGE_PART_TAB' ;

---檢視high_value

with  xs as

  ( select  x. *,

         to_date ( substr ( high_value ,   11 ,   19 ),   'YYYY-MM-DD HH24:MI:SS' )  dates ,

          replace ( high_value ,   'TIMESTAMP''' )  datestr

     from   xmltable ( '/ROWSET/ROW'   passing   ( select  dbms_xmlgen.getxmltype ( '

select t.table_owner, t.table_name, t.partition_name, t.high_value

  from dba_tab_partitions t' )

                       from  dual )   columns  table_owner varchar2 ( 30 )   path

                    'TABLE_OWNER' ,

                   table_name varchar2 ( 30 )   path   'TABLE_NAME' ,

                   partition_name varchar2 ( 30 )   path   'PARTITION_NAME' ,

                   high_value varchar2 ( 30 )   path   'HIGH_VALUE' )  x )

select  xs.datestr ,  p.table_name ,  p.partition_name ,  p.tablespace_name

   from  xs ,  dba_tab_partitions p

  where  xs.table_owner =   'CJC'

    and  p.table_owner =  xs.table_owner

    and  p.table_name =  xs.table_name

    and  p.partition_name =  xs.partition_name

  order   by  p.table_owner ,  p.table_name ,  p.partition_position ;

SQL> SELECT P.TABLE_NAME, P.PARTITION_NAME, P.HIGH_VALUE FROM USER_TAB_PARTITIONS P;

---檢視時間分佈

select   count (*)   from  RANGE_PART_TAB partition ( P1 );

select   count (*)   from  RANGE_PART_TAB partition ( P2 );

select   count (*)   from  RANGE_PART_TAB partition ( P3 );

select   count (*),  to_char ( deal_date ,   'yyyy-mm' )

   from  RANGE_PART_TAB

  group   by  to_char ( deal_date ,   'yyyy-mm' )

  order   by   2 ;

---通過object_id檢視每個分割槽資料分佈情況

select   *   from  dba_segments where  segment_name =   'RANGE_PART_TAB' ;

select   *   from  dba_objects where  object_name = 'RANGE_PART_TAB' ;

select  dbms_rowid.rowid_object ( rowid )  obj_id ,   count (*)

   from  RANGE_PART_TAB

  group   by  dbms_rowid.rowid_object ( rowid )

  order   by   1 ;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2692263/，如需轉載，請註明出處，否則將追究法律責任。