Oracle 12c Release 2分区新功能
发布于：2021-02-05 10:35:20
Oracle database 12c Release 2中引入了许多对Oracle数据库分区选项的增强。
其中包括：
自动列表分区
多列列表分区
只读分区
筛选分区维护操作
在线将非分区表转换为分区表
分区外部表
与Oracle Database 12c Release 1中引入的间隔分区方法类似，Oracle 12c Release 2中引入的一个新分区特性是自动列表分区特性，它使分区表更易于管理。
基于列中不同值对表进行分区的能力已经存在了很长一段时间，但Oracle database 12.2中的新功能是，我们可以首先基于已知的值对表进行分区，这些值出现在要对表进行分区的列中。当数据加载到表中时，如果加载的分区键值与任何现有分区都不对应，则数据库会自动创建新分区。
在Oracle Database 12c Release 2中，我们现在可以基于多列的值列表来划分表，而不是像以前版本那样仅基于一列。这称为多列列表分区。
因此，如果我们有一个包含整个国家的销售数据的非常大的表，我们可以根据一个州和位于同一州的一个城市来划分表。
另一个新特性是只读分区。因此，整个表不是只读的，但是表中的一些分区可以是只读的。也许我们需要存储历史数据，表中较旧的分区可以设置为只读，以防止对此类“旧”数据执行任何DML操作。
作为DBA，我们必须定期执行的一个常见分区维护操作是移动分区—可能表中较旧的分区被移动到低成本存储上托管的另一个表空间，因为这些数据很少被访问。现在在Oracle数据库12.2中，我们可以将移动分区、合并分区和拆分分区等分区维护操作与数据过滤结合起来。例如，在移动特定分区的数据时，我们还可以过滤将要移动的数据。通过INCLUDING ROWS关键字指定过滤谓词，我们可以确定在执行分区维护操作时应该保留哪些数据以及可以丢弃哪些数据。
但是12.2的一个最好的新特性是，我们现在可以通过带有MODIFY子句的ALTER table命令，以及在表上执行并发DML操作时，非常容易地将非分区表转换为分区表。因此，表的转换是一个在线过程，通过updateindexes子句，我们还可以将表上的任何索引转换为本地或全局分区索引。
我们现在还可以创建一个分区的外部表。因此，基本上，我们是基于Oracle数据库之外的数据创建一个表，并对包含这些外部数据的表进行分区，以便更好地优化查询，如果我们处理大量的这些外部数据集，这将非常有益。
让我们看看所有这些12c版本2分区的新特性。
首先创建一个带有索引的测试非分区表，并在表中插入一些数据。
SQL> CREATE TABLE sales_by_state
(sales_state VARCHAR2(3),
sales_city VARCHAR2(20),
sales_quarter NUMBER,
sales_amount NUMBER);
Table created.
SQL> CREATE INDEX sales_state_ind ON sales_by_state (sales_state);
Index created.
SQL> INSERT INTO sales_by_state
VALUES
('WA','PERTH',1,10000);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('WA','ALBANY',1,9500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('VIC','MELBOURNE',1,75000);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('VIC','GEELONG',1,6500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('NSW','SYDNEY',1,50000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM sales_by_state;
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 1 10000
WA ALBANY 1 9500
VIC MELBOURNE 1 75000
VIC GEELONG 1 6500
NSW SYDNEY 1 50000
现在，我们在线将非分区表转换为多列列表分区表，并在表上创建本地分区索引以及同一操作的一部分。
SQL> ALTER TABLE system.sales_by_state
MODIFY
PARTITION BY LIST (sales_state,sales_city) AUTOMATIC
(PARTITION p_wa_1 VALUES ('WA','PERTH'),
PARTITION p_wa_2 values ('WA','ALBANY'),
PARTITION p_vic_1 values ('VIC','MELBOURNE'),
PARTITION p_vic_2 values ('VIC','GEELONG'),
PARTITION p_nsw_1 values ('NSW','SYDNEY'))
ONLINE
UPDATE INDEXES;
Table altered.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
SQL> select partition_name from user_IND_PARTITIONS WHERE INDEX_NAME='SALES_STATE_IND';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
请注意，当我们在分区表中插入一些新行时，表中还没有state和city值。
SQL> INSERT INTO sales_by_state
VALUES
('SA','ADELAIDE',1,10500);
1 row created.
SQL> INSERT INTO sales_by_state
VALUES
('SA','ADELAIDE',2,8000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_BY_STATE';
PARTITION_NAME
--------------------------------------------------------------------------------
P_NSW_1
P_VIC_1
P_VIC_2
P_WA_1
P_WA_2
SYS_P1323
6 rows selected.
SQL> SELECT COUNT(*) FROM sales_by_state PARTITION(SYS_P1323);
COUNT(*)
----------
2
将表中的一个分区设为只读。
SQL> ALTER TABLE sales_by_state MODIFY PARTITION p_wa_1 READ ONLY;
Table altered.
SQL> INSERT INTO sales_by_state
VALUES
('WA','PERTH',2,5000);
INSERT INTO sales_by_state
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
接下来，我们将创建一个外部分区表。我们有4个平面文件，其中包含特定州的销售信息，现在我们要创建一个按州名称分区的表。
SQL> !cat salesdata_wa.txt
WA PERTH 1 10000
WA ALBANY 1 9500
SQL> !cat salesdata_nsw.txt
NSW SYDNEY 1 50000
SQL> !cat salesdata_vic.txt
VIC MELBOURNE 1 75000
VIC GEELONG 1 6500
SQL> !cat salesdata.txt
SA ADELAIDE 1 10500
SA ADELAIDE 2 8000
CREATE TABLE system.sales_by_state
(sales_state VARCHAR2(3),
sales_city VARCHAR2(20),
sales_quarter NUMBER,
sales_amount NUMBER)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE)
)
PARTITION BY LIST (sales_state)
(PARTITION p_wa VALUES ('WA') LOCATION ('salesdata_wa.txt'),
PARTITION p_vic values ('VIC') LOCATION ('salesdata_vic.txt'),
PARTITION p_nsw values ('NSW') LOCATION ('salesdata_nsw.txt'),
PARTITION p_others values (DEFAULT) LOCATION ('salesdata.txt')
)
;
Table created.
查询已分区的外部表。
SQL> SELECT * FROM sales_by_state;
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 1 10000
WA ALBANY 1 9500
VIC MELBOURNE 1 75000
VIC GEELONG 1 6500
NSW SYDNEY 1 50000
SA ADELAIDE 1 10500
SA ADELAIDE 2 8000
7 rows selected.
SQL> SELECT * FROM sales_by_state partition (p_wa);
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 1 10000
WA ALBANY 1 9500
SQL> SELECT * FROM sales_by_state partition (p_nsw);
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
NSW SYDNEY 1 50000
在本例中，我们将把一个分区移动到另一个表空间，可能是为了存档数据，作为分区维护操作的一部分，我们只想保留满足特定筛选器的行，在本例中，该筛选器仅是分区中属于SALES_QUARTER列且值为2的行。不符合此筛选条件的所有其他行将作为移动分区操作的一部分被清除。
SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_);
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 1 10000
WA ALBANY 1 9500
WA BROOME 1 9700
WA PERTH 2 11000
SQL> ALTER TABLE sales_by_state_part
2 MOVE PARTITION p_wa_ TABLESPACE archive_data COMPRESS ONLINE
3 INCLUDING ROWS WHERE sales_quarter=2;
Table altered.
SQL> SELECT * FROM sales_by_state_part PARTITION (p_wa_);
SAL SALES_CITY SALES_QUARTER SALES_AMOUNT
--- -------------------- ------------- ------------
WA PERTH 2 11000
SQL> SELECT TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE PARTITION_NAME='P_WA_';
TABLESPACE_NAME
------------------------------
ARCHIVE_DATA
作者介绍
