Oracle12.2数据库版本增加了:只读分区 ;外部表分区 和 多列分区的新特性
官方文档:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/partition-create-tables-indexes.html#GUID-24050391-B7C5-4AE2-86D4-B5438412C3F6
1、只读分区或子分区
在Oracle12.2数据库中可以将表,分区和子分区设置为只读状态,以保护数据免受任何用户或触发器的无意DML操作。
只读子句的值可以是READ ONLY或READ WRITE。READ WRITE是默认值。除非为分区或子分区显式设置了只读子句,否则只读子句的更高级别设置将应用于分区和子分区!
例子:创建具有只读和读写分区的表:orders_read_write_only
以下是创建具有只读和读写状态的复合范围列表分区表的示例。将orders_read_write_only被明确指定为READ WRITE,所以表的默认属性是读写。partition的默认属性order_p1被指定为只读,因此子分区ord_p1_northwest和order_p1_southwest从分区继承只读状态order_p1。子分区ord_p2_southwest和order_p3_northwest显式指定为只读,覆盖默认读写状态。
CREATE TABLE orders_read_write_only (
order_id NUMBER (12),
order_date DATE CONSTRAINT order_date_nn NOT NULL,
state VARCHAR2(2)
) READ WRITE
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (state)
( PARTITION order_p1 VALUES LESS THAN (TO_DATE ('01-DEC-2015','DD-MON-YYYY')) READ ONLY
( SUBPARTITION order_p1_northwest VALUES ('OR', 'WA'),
SUBPARTITION order_p1_southwest VALUES ('AZ', 'UT', 'NM')),
PARTITION order_p2 VALUES LESS THAN (TO_DATE ('01-MAR-2016','DD-MON-YYYY'))
( SUBPARTITION order_p2_northwest VALUES ('OR', 'WA'),
SUBPARTITION order_p2_southwest VALUES ('AZ', 'UT', 'NM') READ ONLY),
PARTITION order_p3 VALUES LESS THAN (TO_DATE ('01-JUL-2016','DD-MON-YYYY'))
(SUBPARTITION order_p3_northwest VALUES ('OR', 'WA') READ ONLY,
SUBPARTITION order_p3_southwest VALUES ('AZ', 'UT', 'NM')) );
--检查分区和子分区的状态:SQL> SELECT PARTITION_NAME, READ_ONLY FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='ORDERS_READ_WRITE_ONLY';
PARTITION_NAME READ
------------------------- ----
ORDER_P1 YES ---只读状态
ORDER_P2 NONE
ORDER_P3 NONE
SQL> set linesize 300
SQL> col PARTITION_NAME for a25
SQL> col SUBPARTITION_NAME for a25
SQL> SELECT PARTITION_NAME, SUBPARTITION_NAME, READ_ONLY FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='ORDERS_READ_WRITE_ONLY';
PARTITION_NAME SUBPARTITION_NAME REA
------------------------- ------------------------- ---
ORDER_P1 ORDER_P1_NORTHWEST YES
ORDER_P1 ORDER_P1_SOUTHWEST YES
ORDER_P2 ORDER_P2_NORTHWEST NO
ORDER_P2 ORDER_P2_SOUTHWEST YES
ORDER_P3 ORDER_P3_NORTHWEST YES
ORDER_P3 ORDER_P3_SOUTHWEST NO
6 rows selected.
(从上面可以看出,如果单独设置子分区为只读状态 会覆盖分区的读写属性,也就是说对一个分区的子分区进行设置只读状态 不会影响这个分区下的其他子分区的属性!)
--:分区的属性可以通过modify语句进行在线修改。
SQL> alter table ORDERS_READ_WRITE_ONLY modify partition ORDER_P1 read write;
################################################################
2、创建 外部表分区:
在12.2中,Oracle还支持外部表分区,类似如下的语法展示了这一特性的用途,对于一个统一的外部表,可以通过分区指向不同的外部文件,不同文件可以用于存储已经分类的数据,从而更加灵活的使用外部表:
例子:SQL> Create directory load_d1 as '/home/oracle';
CREATE TABLE sales (loc_id number, prod_id number, cust_id number, amount_sold number, quantity_sold number)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY load_d1
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
NOBADFILE
LOGFILE log_dir:'sales.log'
FIELDS TERMINATED BY ","
)
)
REJECT LIMIT UNLIMITED
PARTITION BY RANGE (loc_id)
(PARTITION p1 VALUES LESS THAN (1000) LOCATION ('california.txt'),
PARTITION p2 VALUES LESS THAN (2000) DEFAULT DIRECTORY load_d2 LOCATION ('washington.txt'),
PARTITION p3 VALUES LESS THAN (3000));
###########################################################
3、列表分区:
创建列表分区的语义与创建范围分区的语义非常相似。从Oracle12.2开始可以创建多列列表分区。
例子:创建列表分区CREATE TABLE q1_sales_by_region
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX'));
3.1、、使用默认分区创建列表分区:(指定默认分区就是将不符合的行都插入到默认分区中)
与范围分区不同,使用列表分区,分区之间没有明显的顺序感。
您还可以指定一个默认分区,将未映射到任何其他分区的行映射到该分区。如果在前面的示例中指定了默认分区,则状态CA将映射到该分区。
例子:使用默认分区创建列表分区表CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR(30), state_code VARCHAR(2),sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5
PARTITION BY LIST (state_code)
(PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
STORAGE (INITIAL 8M) TABLESPACE tbs8,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') NOLOGGING,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA'),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT));
3.2、创建自动列表分区:
自动列表分区方法允许按需创建列表分区。
自动列表分区表类似于常规列表分区表,但该分区表更易于管理。您可以仅使用已知的分区键值创建自动列表分区表。当数据加载到表中时,如果加载的分区键值与任何现有分区不对应,则数据库会自动创建新分区。由于分区是按需自动创建的,因此自动列表分区方法在概念上与现有的间隔分区方法类似。
注意:
①:除非您可以调整数据,否则对值非常频繁更改的数据类型的自动列表分区不太适合此方法。
②:自动列表分区表在创建时必须至少有一个分区。由于为新的和未知的分区键值自动创建了新分区,因此自动列表分区不能具有DEFAULT分区。
例子:CREATE TABLE sales_auto_list
( salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST (sales_state) AUTOMATIC
(PARTITION P_CAL VALUES ('CALIFORNIA')
);
--查看列表分区是否为自动列表分区SQL> col TABLE_NAME for a20
SQL> col owner for a10
SQL> set linesize 300
SQL> select OWNER,TABLE_NAME,PARTITIONING_TYPE,AUTOLIST from dba_PART_TABLES where table_name=upper('sales_auto_list');
OWNER TABLE_NAME PARTITION AUT
---------- -------------------- --------- ---
SYS SALES_AUTO_LIST LIST YES
3.3、创建多列列表分区:
与单列列表分区类似,各个分区可以包含包含值列表的集合。
语法:PARTITION BY LIST (column1,column2)
例子:创建多列列表分区CREATE TABLE dba_by_db_in_yhem
(dbalic NUMBER,
username VARCHAR2(20),
dbcat VARCHAR2(4),
region VARCHAR2(10)
)
PARTITION BY LIST (dbcat, region)
(
PARTITION north_part VALUES (('ORCL','BEIJING'), ('ORCL','TIANJIN')),
PARTITION south_part VALUES (('DB2','SHENZHEN'), ('DB2','GUANGZHOU')),
PARTITION west_part VALUES (('SQL','CHENGDU'),('ORCL','CHENGDU'),('DB2','KUNMING')),
PARTITION east_part VALUES ('ORCL','SHANGHAI'),
PARTITION rest VALUES (DEFAULT)
);
insert into dba_by_db_in_yhem values(1,'EYGLE','ORCL','BEIJING');
insert into dba_by_db_in_yhem values(2,'KAMUS','ORCL','BEIJING');
insert into dba_by_db_in_yhem values(3,'LAOXIONG','SQL','CHENGDU');
insert into dba_by_db_in_yhem values(4,'ORA-600','DB2','GUANGZHOU');
insert into dba_by_db_in_yhem values(5,'YANGTINGKUN','ALL','BEIJING');
SQL> select * from dba_by_db_in_yhem partition (north_part);
DBALIC USERNAME DBCA REGION
---------- -------------------- ---- ----------
1 EYGLE ORCL BEIJING
2 KAMUS ORCL BEIJING
SQL> select * from dba_by_db_in_yhem partition (south_part);
DBALIC USERNAME DBCA REGION
---------- -------------------- ---- ----------
4 ORA-600 DB2 GUANGZHOU
####################################################
指定秘钥列的分区:
对于范围分区和散列分区表,最多可以指定16个分区键列。
1、按日期创建多列范围分区表
CREATE TABLE sales_demo (
year NUMBER,
month NUMBER,
day NUMBER,
amount_sold NUMBER)
PARTITION BY RANGE (year,month)
(PARTITION before2001 VALUES LESS THAN (2001,1),
PARTITION q1_2001 VALUES LESS THAN (2001,4),
PARTITION q2_2001 VALUES LESS THAN (2001,7),
PARTITION q3_2001 VALUES LESS THAN (2001,10),
PARTITION q4_2001 VALUES LESS THAN (2002,1),
PARTITION future VALUES LESS THAN (MAXVALUE,0));
INSERT INTO sales_demo VALUES(2000,12,12, 1000);
INSERT INTO sales_demo VALUES(2001,3,17, 2000);
INSERT INTO sales_demo VALUES(2001,11,1, 5000);
INSERT INTO sales_demo VALUES(2002,1,1, 4000);
2、创建多列范围分区表(强制实施相等的分区)CREATE TABLE supplier_parts (
supplier_id NUMBER,
partnum NUMBER,
price NUMBER)
PARTITION BY RANGE (supplier_id, partnum)
(PARTITION p1 VALUES LESS THAN (10,100),
PARTITION p2 VALUES LESS THAN (10,200),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));
INSERT INTO supplier_parts VALUES(5,5,1000);
INSERT INTO supplier_parts VALUES(5,150,1000);
INSERT INTO supplier_parts VALUES(10,100,1000);
--查看各分区的值:
SQL> SELECT * FROM supplier_parts PARTITION (p1);
SUPPLIER_ID PARTNUM PRICE
----------- ---------- ----------
5 5 1000
5 150 1000
SQL> SELECT * FROM supplier_parts PARTITION (p2);
SUPPLIER_ID PARTNUM PRICE
----------- ---------- ----------
10 100 1000