目录
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表分区的具体作用
Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。
什么时候使用分区表：
表的大小超过2GB。
表中包含历史数据，新的数据被增加都新的分区中。
表分区的优缺点
优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：
已经存在的表没有方法直接转化为分区表。
分区修剪行为：如果查询首先访问索引，他是否能够修建分区完全取决于查询中的谓词。
分区修剪是数据仓库的一个重要特性。当访问分区表的时候，优化器分析FROM和WHERE子句来消除不必要的分区。
分区修剪行为示例
1.造数
create table t1 as select * from dba_objects;
create table t2
partition by range(object_id)(
partition t2_parti_p1 values less than( 10000),
partition t2_parti_p2 values less than( 20000),
partition t2_parti_p3 values less than( 30000),
partition t2_parti_p4 values less than( 40000),
partition t2_parti_p5 values less than( 50000),
partition t2_parti_p6 values less than( 60000),
partition t2_parti_p7 values less than( 70000),
partition t2_parti_p8 values less than( 80000))
as select * from t1;| SQL> select count(1) from t1; COUNT(1) ---------- 75269 SQL> select count(2) from t2; COUNT(2) ---------- 75269 t1和t2 数据一样，只是t2采用了分区 |
2.打开执行计划
SQL> set autotrace traceonly;
SQL> set linesize 120;3.相同条件查询 t1,t2;对比执行计划结果
SQL> select count(1) from t1 where object_id=10;
SQL> select count(1) from t2 where object_id=10;
4.小结
可用看出查询t2表时，效率大幅度得到了提升。
传递表空间方式导出数据
创建测试环境
create tablespace ts_tp_2019_01 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_01.DBF' SIZE 50M;
create tablespace ts_tp_2019_02 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_02.DBF' SIZE 50M;
create tablespace ts_tp_2019_03 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_03.DBF' SIZE 50M;
create tablespace ts_tp_2019_04 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_04.DBF' SIZE 50M;
create tablespace ts_tp_2019_05 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_05.DBF' SIZE 50M;
create tablespace ts_tp_2019_06 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_06.DBF' SIZE 50M;
create tablespace ts_tp_2019_07 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_07.DBF' SIZE 50M;
create tablespace ts_tp_2019_08 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_08.DBF' SIZE 50M;
create tablespace ts_tp_2019_09 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_09.DBF' SIZE 50M;
create tablespace ts_tp_2019_10 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_10.DBF' SIZE 50M;
create tablespace ts_tp_2019_11 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_11.DBF' SIZE 50M;
create tablespace ts_tp_2019_12 DATAFILE'/home/oracle/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_12.DBF' SIZE 50M;
--创建分区表
create table TEST_PARTITION
(
diseases VARCHAR2(100),
data_sources VARCHAR2(20),
treat_method VARCHAR2(20),
see_id VARCHAR2(50),
hospital_id VARCHAR2(50),
hospital_name VARCHAR2(100),
discharge_time DATE,
diagnosis_code VARCHAR2(20),
diagnosis_name VARCHAR2(50),
day NUMBER(10),
total_amount NUMBER(14,2),
bmi_pay_amount NUMBER(14,2),
self_pay_in NUMBER(14,2),
self_pay_out NUMBER(14,2),
cash NUMBER(14,2),
xyf NUMBER(14,2),
ssf NUMBER(14,2),
clf NUMBER(14,2),
jyjcf NUMBER(14,2),
zlf NUMBER(14,2),
bmi_pay_pct NUMBER,
self_pay_in_pct NUMBER,
medicine_pct NUMBER,
inspection_pct NUMBER,
clf_pct NUMBER,
zlf_pct NUMBER,
ssf_pct NUMBER
)partition by range(discharge_time)
(
partition ts_tp_2019_01 values less than(to_date('2019-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_01,
partition ts_tp_2019_02 values less than(to_date('2019-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_02,
partition ts_tp_2019_03 values less than(to_date('2019-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_03,
partition ts_tp_2019_04 values less than(to_date('2019-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_04,
partition ts_tp_2019_05 values less than(to_date('2019-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_05,
partition ts_tp_2019_06 values less than(to_date('2019-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_06,
partition ts_tp_2019_07 values less than(to_date('2019-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_07,
partition ts_tp_2019_08 values less than(to_date('2019-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_08,
partition ts_tp_2019_09 values less than(to_date('2019-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_09,
partition ts_tp_2019_10 values less than(to_date('2019-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_10,
partition ts_tp_2019_11 values less than(to_date('2019-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_11,
partition ts_tp_2019_12 values less than(to_date('2020-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace ts_tp_2019_12
);
--创建分区表的分区索引
create index INDEX_TPAR_DISTIME on test_partition(discharge_time)
local
(
partition ts_tp_2019_01 tablespace ts_tp_2019_01,
partition ts_tp_2019_02 tablespace ts_tp_2019_02,
partition ts_tp_2019_03 tablespace ts_tp_2019_03,
partition ts_tp_2019_04 tablespace ts_tp_2019_04,
partition ts_tp_2019_05 tablespace ts_tp_2019_05,
partition ts_tp_2019_06 tablespace ts_tp_2019_06,
partition ts_tp_2019_07 tablespace ts_tp_2019_07,
partition ts_tp_2019_08 tablespace ts_tp_2019_08,
partition ts_tp_2019_09 tablespace ts_tp_2019_09,
partition ts_tp_2019_10 tablespace ts_tp_2019_10,
partition ts_tp_2019_11 tablespace ts_tp_2019_11,
partition ts_tp_2019_12 tablespace ts_tp_2019_12
); 传递表空间导出数据，至少满足下面几个条件
- 源和目标数据库必须使用相同的字符集和国家字符集
- 目标数据库不能已经包含同名的表空间。
- 表空间上的对象必须是自包含的。
自包含的意思是说，对于需要做传递的表空间中的对象，它不会引用到表空间之外的对象，比如：
索引在这个表空间里，但是它引用的表在需要传递的表空间以外的其他表空间上；
表的部分分区在需要传递表空间以外的其他表空间上；
表中包含LOB对象存储在需要传递表空间的其他表空间上；
对于当前案例，每个表空间只存储一个表分区。这样我们就不能对单个表空间进行表空间传递操作，因为它上面储存的对象不是自包含的
可用通过下面的方式进行检测是否符合表空间传递的方式导出
SQL> exec dbms_tts.transport_set_check('ts_tp_2019_01',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
VIOLATIONS
-------------------------------------------------------------------------------
ORA-39921: Default Partition (Table) Tablespace SJFX_DATA for TEST_PARTITION not
contained in transportable set.
ORA-39901: Partitioned table SJFX.TEST_PARTITION is partially contained in the t
ransportable set.基于上面两点原因，我们不能直接对分区的表空间进行移植，而这种数据存储设计是合理的，所以我们要考虑一致其他的方式来解决这个问题--（分区交换）
它的方法是这样的:
创建一个临时表。
在临时表上创建索引。
将需要导出的分区数据和分区索引与临时表进行分区交换
将临时表和索引所在的表空间导出
创建用于交换的表及表空间
create tablespace tmp_ts_tp_2019_01 datafile '/home/oracle/app/oracle/oradata/orcl21c/pdb1/test_partition/tmp_ts_2019_01.dbf' size 50m;
create table tmp_ts_tp_2019_01 tablespace tmp_ts_tp_2019_01 as select * from test_partition where 1=2;
create index tmp_index_tpar_distime on tmp_ts_tp_2019_01(discharge_time)最初看到各个分区对应各自的分区表
select partition_name,tablespace_name,segment_type from user_segments where segment_name='TEST_PARTITION';
SQL> select segment_name,tablespace_name,segment_type from user_segments where segment_name='TMP_TS_TP_2019_01';
进行分区交换
SQL> alter table test_partition exchange partition ts_tp_2019_01 with table tmp_ts_tp_2019_01 including indexes with validation;
验证交换结果
SQL> select count(1) from TMP_TS_TP_2019_01 t;
SQL> select count(1) from TEST_PARTITION partition (ts_tp_2019_01);
再次验证表空间转换操作
SQL> exec dbms_tts.transport_set_check('TS_TP_2019_01',true);
SQL> select * from transport_set_violations;
进行表空间传输导出前需要将操作的表空间设置为只读
SQL> alter tablespace TS_TP_2019_01 read only;
导出表空间元数据
expdp sjfx/sjfx@10.0.250.207/pdb1 directory=data_dir dumpfile=tts.dmp logfile=tts.log transport_tablespaces=ts_tp_2019_01 transport_full_check=y这里导expdp导出的数据仅仅是该表空间的原始数据，而真实的数据还在表空间上，需要将表空间与导出的元数据一并到离线的数据上
现在将数据导到离线数据库上
impdp sjfx/sjfx@10.0.250.206/pdb1 directory=data_dir dumpfile=tts.dmp transport_datafiles='/u01/app/oracle/oradata/ORCL21C/pdb1/test_partition/ts_tp_2019_01.DBF'
998
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
