oracle管理之 partitioned table(server.102 b14231)

--分割槽表
Range partitioning
Hash partitioning
List partitioning
Composite range-hash partitioning
Composite range-list partitioning
--range分割槽；可以有多個欄位組成partition key
CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE (sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01)
TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01)
TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01)
TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01)
TABLESPACE tsd );
--hash 分割槽；分散i/o,提高效能；不能控制資料的分佈
CREATE TABLE scubagear
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (gear1, gear2, gear3, gear4);
--list分割槽 只能有一個欄位
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'));
--range hash分割槽
CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER)
PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

--分割槽表相關檢視
DBA_PART_TABLES
ALL_PART_TABLES
USER_PART_TABLES
DBA_TAB_PARTITIONS
ALL_TAB_PARTITIONS
USER_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS
USER_TAB_SUBPARTITIONS
DBA_PART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
DBA_SUBPART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
DBA_PART_COL_STATISTICS
ALL_PART_COL_STATISTICS
USER_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
ALL_SUBPART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS
DBA_PART_HISTOGRAMS
ALL_PART_HISTOGRAMS
USER_PART_HISTOGRAMS
DBA_SUBPART_HISTOGRAMS
ALL_SUBPART_HISTOGRAMS
USER_SUBPART_HISTOGRAMS
DBA_PART_INDEXES
ALL_PART_INDEXES
USER_PART_INDEXES
DBA_IND_PARTITIONS
ALL_IND_PARTITIONS
USER_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
ALL_IND_SUBPARTITIONS
USER_IND_SUBPARTITIONS
DBA_SUBPARTITION_TEMPLATES
ALL_SUBPARTITION_TEMPLATES
USER_SUBPARTITION_TEMPLATES
總結：分割槽表，分割槽索引，可以 exchange、split、merge、move等像操作普通表一樣操作分割槽和子分割槽[@more@]