oracle – table partitioning

幾個常用的oracle table partition用法：

1. table partitioning type:

a) range partition

b) hash partition

c) list partition

d) composite partition 包括了range-hash partition, list-hash partition

2. table partitioning syntax: 文法

 

            a) range partition:

create table sales_range

( salesman_id number(5),

   salesman_name varchar2(30),

   sales_mount number(10),

   sales_date date)

compress

partition by range ( sales_date)

(

    partition sales_Jan_2000 values less than ( to_date('02/01/2000', 'MM/DD/YYYY') ),

    partition sales_Feb_2000 values less than ( to_date('03/01/2000','MM/DD/YYYY')),

    partition sales_others values less than ( MAXVALUE)

);

注意：  1） 這裡的MAXVALUE是大於或者等於to_date('03/01/2000','MM/DD/YYYY')的；

              2） 可以通過user_tab_partitions或者dba_tab_partitions 來查看相關table partition的情況，但是有些資料需要調用dbms_stats.gather_schema_stats來更新，比如num_rows

              b) hash partition

                         

                       create table sales_hash

( salesman_id number(5),

   salesman_name varchar2(30),

   sales_mount number(10),

   sales_date date)

partition by hash (sales_date)

partitions 4 ;

        注意：1）這裡的partitions 數值必須是2的n次冪

       2）在user_tab_partitions或者dba_tab_partition裡面，high_value是顯示為null的。和range partition的顯示值不一樣

 

               c)  list partition

                       

create table sales_list                         

( salesman_id number(5),

   salesman_name varchar2(30),

   sales_mount number(10),

   sales_state varchar2(20),

   sales_date date)

partition by list (sales_state)

            ( partition sales_west values( 'Califonie','Hawaii'),

               partition sales_east values('NewYork','Florida')

            ) ;