range,list,hash partitioning tables 初识

Partitioning table 初识，区分（堆表与分区表、分区特性）

 

1、create range partition table

 

CREATE TABLE time_range_sales

   ( prod_id        NUMBER(6)

   , cust_id        NUMBER

   , time_id        DATE

   , channel_id     CHAR(1)

   , promo_id       NUMBER(6)

   , quantity_sold  NUMBER(3)

   , amount_sold    NUMBER(10,2)

   )

PARTITION BY RANGE (time_id)

 (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),

  PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),

  PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),

  PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)

 );

 

doudou@TEST> select table_name,status,partitioned from user_tables;

 

TABLE_NAME                     STATUS                         PARTITIONED

------------------------------ ------------------------------ ------------------------------

TIME_RANGE_SALES               VALID                          YES

 

doudou@TEST>  select partition_name,partition_position,high_value from

  2   user_tab_partitions where table_name='TIME_RANGE_SALES';

 

PARTITION_NAME       PARTITION_POSITION HIGH_VALUE

-------------------- ------------------ --------------------------------------------------

SALES_1998                            1 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                        MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

SALES_1999                            2 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                        MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

SALES_2000                            3 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:

                                        MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

SALES_2001                            4 MAXVALUE

 

2、  create list partition table

 

CREATE TABLE list_sales

   ( prod_id        NUMBER(6)

   , cust_id        NUMBER

   , time_id        DATE

   , channel_id     CHAR(1)

   , promo_id       NUMBER(6)

   , quantity_sold  NUMBER(3)

   , amount_sold    NUMBER(10,2)

   )

PARTITION BY LIST (channel_id)

 (PARTITION even_channels VALUES (2,4),

  PARTITION odd_channels VALUES (3,9)

 ); 

 

doudou@TEST> select table_name,status,partitioned from user_tables where table_name='LIST_SALES';

 

TABLE_NAME           STATUS                         PARTITIONED

-------------------- ------------------------------ ------------------------------

LIST_SALES           VALID                          YES

 

doudou@TEST>  select partition_name,partition_position,high_value from

  2   user_tab_partitions where table_name='LIST_SALES';

 

PARTITION_NAME       PARTITION_POSITION HIGH_VALUE

-------------------- ------------------ --------------------------------------------------

EVEN_CHANNELS                         1 '2', '4'

ODD_CHANNELS                          2 '3', '9'

 

3、  create hash partition table

 

CREATE TABLE hash_sales

   ( prod_id        NUMBER(6)

   , cust_id        NUMBER

   , time_id        DATE

   , channel_id     CHAR(1)

   , promo_id       NUMBER(6)

   , quantity_sold  NUMBER(3)

   , amount_sold    NUMBER(10,2)

   )

PARTITION BY HASH (prod_id)

PARTITIONS 2; 

 

doudou@TEST> select table_name,status,partitioned from user_tables where table_name='HASH_SALES';

 

TABLE_NAME           STATUS                         PARTITIONED

-------------------- ------------------------------ ------------------------------

HASH_SALES           VALID                          YES

 

doudou@TEST>  select partition_name,partition_position,high_value from

  2   user_tab_partitions where table_name='HASH_SALES';

 

PARTITION_NAME       PARTITION_POSITION HIGH_VALUE

-------------------- ------------------ --------------------------------------------------

SYS_P21                               1

SYS_P22                               2

 

4、  create heap-organized table “DOUDOU”

 

doudou@TEST>  select table_name,status,partitioned from user_tables where table_name='DOUDOU';

 

TABLE_NAME           STATUS                         PARTITIONED

-------------------- ------------------------------ ------------------------------

DOUDOU               VALID                          NO

 

总结：

1、  如何区别分区表与堆组织表？

可以通过视图user_tables中的列PARTITIONED 值 YES or NO 来判断。

2、  如何区别分区表特性？

可以通过视图user_tab_partitions中的列HIGH_VALUE值判断。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-752766/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-752766/