Range-Partitioned Table
创建范围分区表。
如果加上ENABLE ROW MOVEMENT，则在对time_id进行修改，导致记录不符合分区标准来，会自动移动到相应分区。
用scott用户创建分区表
CREATE TABLE sales1
( 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_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) TABLESPACE ts1
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE ts2
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE ts3
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE ts4
);
( 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_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy')) TABLESPACE ts1
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')) TABLESPACE ts2
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')) TABLESPACE ts3
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')) TABLESPACE ts4
);
分区表创建后不会立刻生成segment，因此无法从user_segements中查到分区信息。
通过查询user_tables 可看到这个表的partitioned属性为YES，并且由于跨越多个表空间，因此TABLESPACE_NAME不显示
SQL> select t.table_name, t.partitioned, t.tablespace_name from user_tables t where table_name = 'SALES1';
TABLE_NAME PAR TABLESPACE_NAME
------------------------------ --- ------------------------------
SALES1 YES
TABLE_NAME PAR TABLESPACE_NAME
------------------------------ --- ------------------------------
SALES1 YES
SQL> select t.table_name, t.partition_name, t.tablespace_name from user_tab_partitions t where t.table_name = 'SALES1';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SALES1 SALES_Q1_2006 TS1
SALES1 SALES_Q2_2006 TS2
SALES1 SALES_Q3_2006 TS3
SALES1 SALES_Q4_2006 TS4
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SALES1 SALES_Q1_2006 TS1
SALES1 SALES_Q2_2006 TS2
SALES1 SALES_Q3_2006 TS3
SALES1 SALES_Q4_2006 TS4
为分区表创建全局索引
索引的分区建必须和索引的建相同（下面的amount_sold）
CREATE INDEX amount_sold_ix ON sales1(amount_sold)
GLOBAL PARTITION BY RANGE(amount_sold)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
GLOBAL PARTITION BY RANGE(amount_sold)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
确认分区索引创建成功
SQL> select t.index_name, t.partitioned, t.tablespace_name from user_indexes t where table_name = 'SALES1';
INDEX_NAME PARTITIONED TABLESPACE_NAME
------------------------------ ----------- ------------------------------
AMOUNT_SOLD_IX YES
在不指定表空间的情况下，分区创建到默认表空间上
SQL> select t.index_name, t.partition_name, t.tablespace_name from user_ind_partitions t where t.index_name = 'AMOUNT_SOLD_IX';
INDEX_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
AMOUNT_SOLD_IX P_100 USERS
AMOUNT_SOLD_IX P_1000 USERS
AMOUNT_SOLD_IX P_10000 USERS
AMOUNT_SOLD_IX P_100000 USERS
AMOUNT_SOLD_IX P_1000000 USERS
AMOUNT_SOLD_IX P_GREATER_THAN_1000000 USERS
6 rows selected
Interval-Partitioned Tables
Interval-Partitioned Table不许要一次创建所有分区，而是定义一个分区间，然后在插入数据时自动创建。
CREATE TABLE interval_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)
INTERVAL(NUMTOYMINTERVAL( 1, 'MONTH' ))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008' , 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009' , 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009' , 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010' , 'DD-MM-YYYY')) );
SQL> select t.table_name, t.partition_name, t.tablespace_name from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
INTERVAL_SALES P0 USERS
INTERVAL_SALES P1 USERS
INTERVAL_SALES P2 USERS
INTERVAL_SALES P3 USERS
此时插入数据，如果数据满足已有分区条件，则使用现有分区：
SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ---------------
INTERVAL_SALES P0 USERS NO
INTERVAL_SALES P1 USERS YES
INTERVAL_SALES P2 USERS YES
INTERVAL_SALES P3 USERS NO
如果插入了现有分区条件之外的数据，oracle自动按照指定的INTERVAL(NUMTOYMINTERVAL( 1 , 'MONTH' ))跨度建立分区。
SQL> insert into interval_sales (time_id) values (TO_DATE('5-6-2011', 'DD-MM-YYYY'));
1 row inserted
SQL> commit;
Commit complete
SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ---------------
INTERVAL_SALES P0 USERS NO
INTERVAL_SALES P1 USERS YES
INTERVAL_SALES P2 USERS YES
INTERVAL_SALES P3 USERS NO
INTERVAL_SALES SYS_P41 USERS YES
SQL> insert into interval_sales (time_id) values (TO_DATE('8-8-2011', 'DD-MM-YYYY'));
1 row inserted
SQL> commit;
Commit complete
SQL> select t.table_name, t.partition_name, t.tablespace_name, t.segment_created from user_tab_partitions t where t.table_name = 'INTERVAL_SALES';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ---------------
INTERVAL_SALES P0 USERS NO
INTERVAL_SALES P1 USERS YES
INTERVAL_SALES P2 USERS YES
INTERVAL_SALES P3 USERS NO
INTERVAL_SALES SYS_P41 USERS YES
INTERVAL_SALES SYS_P42 USERS YES
6 rows selected
Hash-Partitioned Tables
通过PARTITION BY HASH创建hash分区表。hash分区表只需要指定表空间和分区数量，oracle会将数据均匀地分布到各个分区。
SQL> CREATE TABLE scubagear
2 (id NUMBER,
3 name VARCHAR2 (60))
4 STORAGE (INITIAL 100K)
5 PARTITION BY HASH (id)
6 PARTITIONS 4
7 STORE IN (ts1, ts2, ts3, ts4);
Table created
SQL>
如果指定的STORAGE (INITIAL 100K)，则每个分区的初始大小都是100k，而不是整个表100k:
SQL> insert into scubagear values(1, 'asd');
1 row inserted
SQL> commit;
Commit complete
SQL> select t.segment_name, t.partition_name, t.initial_extent from user_segments t where t.segment_name = 'SCUBAGEAR';
SEGMENT_NAME PARTITION_NAME INITIAL_EXTENT
-------------------------------------------------------------------------------- ------------------------------ --------------
SCUBAGEAR SYS_P54 106496
为分区表创建局部索引
给上面的分区表创建局部索引，索引的分区与表的分区一一对应，并且默认创建到索引分区的表空间上。
索引创建的时候同样不统一segment，而是根据需要创建。
SQL> CREATE INDEX loc_scubagear_ix ON scubagear(id) LOCAL;
Index created
SQL> select t.index_name, t.partition_name, t.tablespace_name, t.segment_created from user_ind_partitions t where t.index_name = 'LOC_SCUBAGEAR_IX';
INDEX_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ---------------
LOC_SCUBAGEAR_IX SYS_P55 TS1 NO
LOC_SCUBAGEAR_IX SYS_P56 TS2 NO
LOC_SCUBAGEAR_IX SYS_P57 TS3 NO
LOC_SCUBAGEAR_IX SYS_P58 TS4 YES
创建基于list的分区表
下面的分区表，分区默认创建到表空间ts1，但是region_east分区创建到ts2，并且初始区的大小是8M
SQL> CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
2 store_name VARCHAR(30), state_code VARCHAR(2),
3 sale_date DATE)
4 STORAGE(INITIAL 10K NEXT 20K) TABLESPACE ts1
5 PARTITION BY LIST (state_code)
6 (
7 PARTITION region_east
8 VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
9 STORAGE (INITIAL 8M)
10 TABLESPACE ts2,
11 PARTITION region_west
12 VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
13 NOLOGGING,
14 PARTITION region_south
15 VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
16 PARTITION region_central
17 VALUES ('OH','ND','SD','MO','IL','MI','IA'),
18 PARTITION region_null
19 VALUES (NULL),
20 PARTITION region_unknown
21 VALUES (DEFAULT)
22 );
Table created
SQL> select t.table_name,
2 t.partition_name,
3 t.tablespace_name,
4 t.segment_created,
5 t.initial_extent,
6 t.next_extent
7 from user_tab_partitions t
8 where t.table_name = 'SALES_BY_REGION';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------
SALES_BY_REGION REGION_CENTRAL TS1 NO 16384 24576
SALES_BY_REGION REGION_EAST TS2 NO 8388608 24576
SALES_BY_REGION REGION_NULL TS1 NO 16384 24576
SALES_BY_REGION REGION_SOUTH TS1 NO 16384 24576
SALES_BY_REGION REGION_UNKNOWN TS1 NO 16384 24576
SALES_BY_REGION REGION_WEST TS1 NO 16384 24576
6 rows selected
创建Reference-Partitioned Table
Reference-Partitioned用在主子表关系中，主表是分区表，字表与主表通过外键关联。这样子表的分区就依照主表的来建立，子表数据放到哪个分区也由主表相应记录的分区决定。
CREATE TABLE orders
( order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) );
Table created.
( order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')) );
Table created.
SQL> CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);
Table created.
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);
Table created.
在主表插入第一、二季度的数据，然后在子表插入相应数据
SQL> insert into orders (order_id, order_date) values (1, to_date('2005-1-1', 'yyyy-mm-dd'));
1 row inserted
SQL> insert into orders (order_id, order_date) values (2, to_date('2005-6-1', 'yyyy-mm-dd'));
1 row inserted
SQL> commit;
Commit complete
SQL> insert into order_items values (1, 1, 1, 1, 1);
1 row inserted
SQL> insert into order_items values (2, 2, 2, 2, 2);
1 row inserted
SQL> commit;
Commit complete
此时查看主、子表的分区情况，发现子表数据插入了和主表相对应的分区中。
SQL>
SQL> select t.table_name,
2 t.partition_name,
3 t.tablespace_name,
4 t.segment_created,
5 t.initial_extent,
6 t.next_extent
7 from user_tab_partitions t
8 where t.table_name = 'ORDERS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------
ORDERS Q1_2005 USERS YES 8388608 1048576
ORDERS Q2_2005 USERS YES 8388608 1048576
ORDERS Q3_2005 USERS NO
ORDERS Q4_2005 USERS NO
SQL> select t.table_name,
2 t.partition_name,
3 t.tablespace_name,
4 t.segment_created,
5 t.initial_extent,
6 t.next_extent
7 from user_tab_partitions t
8 where t.table_name = 'ORDER_ITEMS';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------
ORDER_ITEMS Q1_2005 USERS YES 8388608 1048576
ORDER_ITEMS Q2_2005 USERS YES 8388608 1048576
ORDER_ITEMS Q3_2005 USERS NO
ORDER_ITEMS Q4_2005 USERS NO
复合分区表
在范围分区下建立HASH分区。
其中一些范围分区单独指定了存储位置
CREATE TABLE emp1 (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
STORE IN (ts2, ts4, ts6, ts8),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
(SUBPARTITION p3_s1 TABLESPACE ts4,
SUBPARTITION p3_s2 TABLESPACE ts5));
PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000)
STORE IN (ts2, ts4, ts6, ts8),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
(SUBPARTITION p3_s1 TABLESPACE ts4,
SUBPARTITION p3_s2 TABLESPACE ts5));
插入测试数据
SQL> insert into emp1 values(1500, 'asd', 1);
1 row inserted
SQL> commit;
Commit complete
查看分区情况
主分区显示为USERS表空间，并没有创建，因为主分区只是逻辑存在。
SQL> select t.table_name,
2 t.partition_name,
3 t.tablespace_name,
4 t.segment_created,
5 t.initial_extent,
6 t.next_extent
7 from user_tab_partitions t
8 where t.table_name = 'EMP1';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ ------------------------------ --------------- -------------- -----------
EMP1 P1 USERS NONE
EMP1 P2 USERS NONE
EMP1 P3 USERS NONE
子分区中，建表语句SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)处指定了8个子分区，因此子分区位于TS11335577。
P2的子分区位于临时指定的TS2，4，6，8。
P3的子分区位名字和数量都完全按具体指定的创建，分别位于TS2和TS5。
插入的数据位于P2的SYS_SUBP74分区
SQL> select t.table_name,
2 t.partition_name,
3 t.subpartition_name,
4 t.tablespace_name,
5 t.segment_created
6 from user_tab_subpartitions t
7 where t.table_name = 'EMP1'
8 order by t.partition_name, t.tablespace_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------
EMP1 P1 SYS_SUBP59 TS1 NO
EMP1 P1 SYS_SUBP63 TS1 NO
EMP1 P1 SYS_SUBP60 TS3 NO
EMP1 P1 SYS_SUBP64 TS3 NO
EMP1 P1 SYS_SUBP65 TS5 NO
EMP1 P1 SYS_SUBP61 TS5 NO
EMP1 P1 SYS_SUBP62 TS7 NO
EMP1 P1 SYS_SUBP66 TS7 NO
EMP1 P2 SYS_SUBP71 TS2 NO
EMP1 P2 SYS_SUBP67 TS2 NO
EMP1 P2 SYS_SUBP68 TS4 NO
EMP1 P2 SYS_SUBP72 TS4 NO
EMP1 P2 SYS_SUBP69 TS6 NO
EMP1 P2 SYS_SUBP73 TS6 NO
EMP1 P2 SYS_SUBP74 TS8 YES
EMP1 P2 SYS_SUBP70 TS8 NO
EMP1 P3 P3_S1 TS4 NO
EMP1 P3 P3_S2 TS5 NO
18 rows selected
给上面分区表创建局部索引
CREATE INDEX emp_ix ON emp1(deptno) LOCAL STORE IN (ts1, ts2, ts3);
和表象他，局部索引的主分区同样只是逻辑存在
SQL> select t.index_name,
2 t.partition_name,
3 t.tablespace_name,
4 t.segment_created
5 from user_ind_partitions t
6 where t.index_name = 'EMP_IX';
INDEX_NAME PARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ---------------
EMP_IX P1 N/A
EMP_IX P2 N/A
EMP_IX P3 N/A
而局部索引的子分区出来存储位置被DDL语句指定意外，子分区的个数完全与表分区对应
SQL> select t.index_name,
2 t.partition_name,
3 t.subpartition_name,
4 t.tablespace_name,
5 t.segment_created
6 from user_ind_subpartitions t
7 order by t.partition_name, t.tablespace_name;
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME SEGMENT_CREATED
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------
EMP_IX P1 SYS_SUBP59 TS1 NO
EMP_IX P1 SYS_SUBP62 TS1 NO
EMP_IX P1 SYS_SUBP65 TS1 NO
EMP_IX P1 SYS_SUBP60 TS2 NO
EMP_IX P1 SYS_SUBP63 TS2 NO
EMP_IX P1 SYS_SUBP66 TS2 NO
EMP_IX P1 SYS_SUBP61 TS3 NO
EMP_IX P1 SYS_SUBP64 TS3 NO
EMP_IX P2 SYS_SUBP67 TS1 NO
EMP_IX P2 SYS_SUBP73 TS1 NO
EMP_IX P2 SYS_SUBP70 TS1 NO
EMP_IX P2 SYS_SUBP74 TS2 YES
EMP_IX P2 SYS_SUBP71 TS2 NO
EMP_IX P2 SYS_SUBP68 TS2 NO
EMP_IX P2 SYS_SUBP72 TS3 NO
EMP_IX P2 SYS_SUBP69 TS3 NO
EMP_IX P3 P3_S1 TS1 NO
EMP_IX P3 P3_S2 TS2 NO
18 rows selected
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26239116/viewspace-1076278/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
分区表和索引
下一篇：
resource manager
请登录后发表评论
登录
全部评论
