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 值判断。