oracle管理之 partitioned table(server.102 b14231)-优快云博客

本文详细介绍了数据库中的多种分区技术，包括范围分区(range partitioning)、哈希分区(hash partitioning)、列表分区(list partitioning)及复合分区(composite partitioning)，并提供了具体的创建分区表的SQL示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--分区表
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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/70612/viewspace-1024940/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/70612/viewspace-1024940/