在ORACLE里如果遇到特别大的表，可以使用分区的表来改变其应用程序的性能。
1.新建表分区
CREATE TABLE sales
(invoice_no NUMBER,
...
sale_date DATE NOT NULL )
PARTITION BY RANGE (sale_date)
(
PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD')
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD')
TABLESPACE ts_sale1999q2,
PARTITION sales1999_q3
VALUES LESS THAN (TO_DATE('1999-10-01','YYYY-MM-DD')
TABLESPACE ts_sale1999q3,
PARTITION sales1999_q4
VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')
TABLESPACE ts_sale1999q4
);
2.索引分区
CREATE INDEX month_ix ON sales(sales_month)
GLOBAL PARTITION BY RANGE(sales_month)
(PARTITION pm1_ix VALUES LESS THAN (2)
PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
3.分区维护
增加分区：
ALTER TABLE sales ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE(‘2000-04-01’,’YYYY-MM-DD’)
TABLESPACE ts_sale2000q1;
删除分区：
ALTER TABLE sales DROP PARTION sales1999_q1;
截短分区:
alter table sales truncate partiton sales1999_q2;
合并分区：
alter table sales merge partitons sales1999_q2, sales1999_q3 into sales1999_q23;
alter index ind_t2 rebuild partition p123 parallel 2;
分裂分区：
ALTER TABLE sales
SPLIT PARTITON sales1999_q4
AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’)
INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) ;
alter table t2 split partition p123 values (1,2) into (partition p12,partition p3);
交换分区:
alter table x exchange partition p0 with table bsvcbusrundatald ;
访问指定分区：
select * from sales partition(sales1999_q2)
EXPORT指定分区：
exp sales/sales_password tables=sales:sales1999_q1
file=sales1999_q1.dmp
IMPORT指定分区：
imp sales/sales_password FILE =sales1999_q1.dmp
TABLES = (sales:sales1999_q1) IGNORE=y
查看分区信息：
user_tab_partitions, user_segments
访问指定分区：
select * from sales partition(sales1999_q2)
4、普通表变为分区表
方法一：利用原表重建分区表。
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, TIME FROM T;
RENAME T TO T_OLD;
RENAME T_NEW TO T;
SELECT COUNT(*) FROM T;
COUNT(*)
----------
5000
SELECT COUNT(*) FROM T PARTITION (P1);
COUNT(*)
----------
2946
SELECT COUNT(*) FROM T PARTITION (P2);
COUNT(*)
----------
731
SELECT COUNT(*) FROM T PARTITION (P3);
COUNT(*)
----------
1096
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
方法二：使用交换分区的方法。
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (MAXVALUE));
ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;
RENAME T TO T_OLD;
RENAME T_NEW TO T;
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
方法三：Oracle9i以上版本，利用在线重定义功能
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T');
PL/SQL 过程已成功完成。
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
表已创建。
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW');
PL/SQL 过程已成功完成。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW');
PL/SQL 过程已成功完成。
SELECT COUNT(*) FROM T;
COUNT(*)
----------
5000
SELECT COUNT(*) FROM T PARTITION (P3);
COUNT(*)
----------
1096
优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
不足：实现上比上面两种略显复杂。
适用于各种情况。
这里只给出了在线重定义表的一个最简单的例子，详细的描述和例子可以参考下面两篇文章。
Oracle的在线重定义表功能：http://blog.itpub.net/post/468/12855
Oracle的在线重定义表功能（二）：http://blog.itpub.net/post/468/12962
5、把一个已存在数据的大表改成分区表：
第一种（表不是太大）：
1.把原表改名：
rename xsb1 to xsb2;
2.创建分区表：
CREATE TABLE xsb1
PARTITION BY LIST (c_test)
(PARTITION xsb1_p1 VALUES (1),
PARTITION xsb1_p2 VALUES (2),
PARTITION xsb1_p0 VALUES (default))
nologging AS SELECT * FROM xsb2;
3.将原表上的触发器、主键、索引等应用到分区表上；
4.删除原表：
drop table xsb2;
第二种(表很大)：
1. 创建分区表：
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
(PARTITION p0 VALUES [less than ](1) tablespace tbs1,
PARTITION p2 VALUES (2) tablespace tbs1,
PARTITION xsb1_p0 VALUES ([maxvalue]default))
AS SELECT * FROM xsb2 [where 1=2];
2. 交换分区 alter table x exchange partition p0 with table bsvcbusrundatald ;
3. 原表改名alter table bsvcbusrundatald rename to x0;
4. 新表改名alter table x rename to bsvcbusrundatald ;
5. 删除原表drop table x0;
6. 创建新表触发器和索引create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;
或者：
1. 规划原大表中数据分区的界限，原则上将原表中近期少量数据复制至另一表；
2. 暂停原大表中的相关触发器；
3. 删除原大表中近期数据；
4. 改名原大表名称；
5. 创建分区表；
6. 交换分区；
7. 重建相关索引及触发器（先删除之再重建）.
参考脚本：
select count(*) from t1 where recdate>sysdate-2
create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2)
alter triger trg_t1 disable
delete t1 where recdate>sysdate-2
commit
rename t1 to x1
create table t1 [nologging] partition by range(recdate)
(partition pbefore values less than (trunc(sysdate-2)),
partition pmax values less than (maxvalue))
as select * from x1 where 1=2
alter table t1 exchange partition pbefore with table x1
alter table t1 exchange partition pmax with table x2
drop table x2
[重建触发器]
drop table x1
本文载自:http://blog.tianya.cn/blogger/post_show.asp?BlogID=925829&PostID=15308448
分享到：
相关推荐
通过合理地利用分区技术，可以显著提高查询性能，简化数据管理任务，并加快数据加载速度。本文档提供了一个关于Oracle数据库表分区操作的具体实例，适用于学习如何对表进行分区。 #### 二、表分区的优点 1. **提高...
Oracle大表分区是一种高效的数据管理策略，用于处理大型数据集，提高查询性能并简化数据库维护。在Oracle数据库系统中，分区技术允许将一个大表分解为较小、更易管理的部分，每个部分称为一个分区。这样做有多种好处...
Oracle数据库提供了丰富的表分区功能，其中按月分区是一种常见的应用场景。本文将以具体的实例——报单表`USER_ORDER`为例，详细介绍如何实现Oracle表的自动按月分区，并创建相应的本地索引。 #### 二、准备工作 在...
4. **查询优化**：编写查询时考虑分区，利用分区键进行筛选，以充分利用分区优势。 在实际应用中，还需要关注分区表的索引、物化视图、分区维护操作（如添加、合并和拆分分区）等方面，以确保系统性能和可维护性。 ...
总结来说，"Oracle数据库表按年分区脚本实战例子"是学习和实践Oracle数据库分区的一个实用资源，通过这个例子，我们可以深入了解如何利用分区提升数据库性能，以及如何根据业务需求定制和管理分区策略。对于处理大量...
Oracle分区功能是Oracle数据库10g中的一项关键技术，它旨在提升数据库应用程序的可管理性、性能和可用性。分区功能允许将大型表、索引和索引...通过有效利用分区，可以显著提升数据库的运行效率，降低整体的运维成本。
在Oracle数据库中，分区是一种对大型表进行物理分割的方法，它可以显著提高查询性能并简化数据管理任务。通过将一个大表分成多个较小的部分（即分区），可以更快地执行查询操作，尤其是在处理大数据集时。此外，分区...
- **分区键的选择**：选择频繁出现在查询条件中的列作为分区键，可有效利用分区消除。 5. **实例分析** - `Oracle数据库(执行计划)第6讲.ppt`可能包含了讲解执行计划的第六部分内容，包括更深入的案例分析和技巧...
本文将深入探讨Oracle表分区的详细知识，包括其原理、类型、优势以及如何在实际操作中应用。 一、分区原理 表分区是Oracle数据库提供的一种高级数据组织方式，它将一个大表分成多个逻辑上独立但物理上存储在一起的...
总的来说，Oracle表分区是数据库性能优化的重要手段，它能提高查询速度，降低存储成本，简化数据管理。然而，合理规划和使用分区技术，以适应具体的应用场景和业务需求，是实现这些优势的关键。在实际应用中，开发者...
Oracle分区是一种将大表或索引分割成较小部分的技术，旨在提高查询性能、减少管理开销并简化数据维护操作。通过合理地将数据分成多个物理块，可以显著提升查询效率，特别是在处理大量历史数据或执行复杂的分析任务时...
Oracle数据库表分区技术是数据库管理中的一项高级功能，它允许将大表划分为更小、更易管理的部分，称为分区。...随着数据量的不断增长，合理利用分区策略可以帮助企业节省维护成本，同时保证应用性能和数据可用性。
Oracle数据库系统性能和应用性能优化是数据库管理员和开发人员关注的重要领域，它涉及到多个层面的调整和优化，以确保系统的高效运行。Oracle数据库是全球广泛使用的数据库管理系统，其性能直接影响到业务应用程序的...
在Oracle数据库中，分区表对于处理大量数据特别有用，因为它可以提高查询性能，简化维护，并允许针对不同分区执行独立的操作。下面我们将深入探讨Oracle分区表的概念、创建方法以及相关操作。 一、分区表的创建 ...
这使得分区对应用程序透明，无需修改现有查询即可利用分区的优势。 4. **分区类型** - **范围分区**：根据字段值的范围将数据分配到不同的分区，如日期范围分区。 - **列表分区**：根据预定义的列表值将数据分配...
- 分区策略应与索引策略相结合，以充分利用分区优势并优化查询性能。 总的来说，Oracle数据库表分区是一种强大的工具，尤其在处理大数据量时，能够显著提升系统性能。合理的设计和应用分区策略对于数据库的高效...
"在线重组Oracle数据库优化关键SAP应用性能"的主题聚焦于如何通过优化数据库结构和操作，提升SAP系统的运行效率。 首先，我们需要理解在线重组在Oracle数据库中的含义。在线重组是一种在不影响正常业务运行的情况下...
14. **数据库升级和补丁应用**：及时更新到最新版本或应用性能相关的补丁，可以利用Oracle的新特性并解决已知性能问题。 15. **硬件和操作系统优化**：硬件选择、磁盘阵列配置、内存分配以及操作系统的调优对Oracle...
- **Oracle分区表优势**: 分区表可以将大数据集分解成较小的、更易于管理的部分，显著提升数据读写性能，特别是在数据量巨大的情况下。 - **定期备份的重要性**: 定期备份是数据安全的重要保障，尤其对于关键业务...