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/，如需轉載，請註明出處，否則將追究法律責任。

range,list,hash partitioning tables 初識

相關文章