Oracle Database 12c第2版（12.2）中的自动列表分区

深碍√TFBOYSˉ_ 2022-05-31 05:26 165阅读 0赞

在Oracle Database 12c第2版（12.2）中引入了自动列表分区，以解决如何处理列表分区键的新的不同值的问题。

下面做个实验来说明：

1.创建测试分区表

SQL> create table orders
    (
      id            number,
      country_code  varchar2(5),
      customer_id   number,
      order_date    date,
      order_total   number(8,2),
      constraint orders_pk primary key (id)
    )
    partition by list (country_code)
    (
      partition part_usa values ('USA'),
      partition part_uk_and_ireland values ('GBR', 'IRL')
    );  2    3    4    5    6    7    8    9   10   11   12   13   14  
    
    Table created.
    
    SQL> insert into orders values (1, 'USA', 10, sysdate, 10200.93);
    
    1 row created.
    
    SQL> insert into orders values (2, 'USA', 11, sysdate, 948.22);
    
    1 row created.
    
    SQL> insert into orders values (3, 'GBR', 22, sysdate, 300.83);
    
    1 row created.
    
    SQL> insert into orders values (4, 'IRL', 43, sysdate, 978.43);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.

2.这个时候插入如下一条数据：

SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);
    insert into orders values (5, 'BGR', 96, sysdate, 2178.43)
                *
    ERROR at line 1:
    ORA-14400: inserted partition key does not map to any partition

3.使用如下语句启用自动列表分区，然后再插入刚才的数据

SQL> alter table orders set partitioning automatic;
    
    Table altered.
    
    SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.

`4.或者，我们可以使用AUTOMATIC关键字重新创建表格。`

SQL> drop table orders purge;
    
    Table dropped.
    
    SQL> create table orders
    (
      id            number,
      country_code  varchar2(5),
      customer_id   number,
      order_date    date,
      order_total   number(8,2),
      constraint orders_pk primary key (id)
    )
    partition by list (country_code) automatic
    (
      partition part_us  2    3    4    5    6    7    8    9   10   11   12  a values ('USA'),
      partition part_uk_and_ireland values ('GBR', 'IRL')
    );
     13   14  
    Table created.
    
    
    SQL> insert into orders values (1, 'USA', 10, sysdate, 10200.93);
    
    1 row created.
    
    SQL> insert into orders values (2, 'USA', 11, sysdate, 948.22);
    
    1 row created.
    
    SQL> insert into orders values (3, 'GBR', 22, sysdate, 300.83);
    
    1 row created.
    
    SQL> insert into orders values (4, 'IRL', 43, sysdate, 978.43);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from orders;
    
    	ID COUNT CUSTOMER_ID ORDER_DAT ORDER_TOTAL
    ---------- ----- ----------- --------- -----------
    	 1 USA		  10 08-FEB-18	  10200.93
    	 2 USA		  11 08-FEB-18	    948.22
    	 3 GBR		  22 08-FEB-18	    300.83
    	 4 IRL		  43 08-FEB-18	    978.43
    
    SQL> insert into orders values (5, 'BGR', 96, sysdate, 2178.43);
    
    1 row created.
    
    SQL> commit;

通过查询\{CDB | DBA | ALL | USER\} \_TAB\_PARTITIONS视图，我们可以看到已经创建了一个新的分区来保存新的顺序。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'orders', cascade => TRUE);
    
    PL/SQL procedure successfully completed.
    
    SQL> set linesize 100
    SQL> column table_name format a30
    SQL> column partition_name format a30
    SQL> column high_value format a15
    SQL> select table_name, partition_name,high_value,num_rows from  user_tab_partitions order by 1, 2;
    
    TABLE_NAME		       PARTITION_NAME		      HIGH_VALUE	NUM_ROWS
    ------------------------------ ------------------------------ --------------- ----------
    ORDERS			       PART_UK_AND_IRELAND	      'GBR', 'IRL'	       2
    ORDERS			       PART_USA 		      'USA'		       2
    ORDERS			       SYS_P303 		      'BGR'		       1

通过查询\{CDB | DBA | ALL | USER\} \_PART\_TABLES视图中的AUTOLIST列，我们可以看到自动列表分区已启用。

SQL> column table_name format a30
    SQL> column autolist format a8
    SQL> select table_name,autolist from  user_part_tables;
    
    TABLE_NAME		       AUTOLIST
    ------------------------------ --------
    ORDERS			       YES