Oracle分割槽表总结.docx

Oracle分割槽表总结 create table emp ( empno number(4), ename varchar2(30), location varchar2(30)) partition by list (location) (partition p1 values (北京), partition p2 values (上海,天津,重庆), partition p3 values (广东,福建) partition p0 values (default) /*值列表中未显示列出的所有值都会放到这个default分割槽中，关于default的使用，有一点要注意：一旦列表分割槽表有一个default分割槽，就不能再向这个表中增加更多的分割槽了，此时必须删除default分割槽，然后增加新分割槽，再加回default分割槽 );杂凑(hash partitioning)分割槽： 杂凑分割槽数应该是2的幂，从而利于各行平均的杂凑与各分割槽 create table emp ( empno number(4), ename varchar2(30), sal number) partition by hash (empno) partitions 8 store in (emp1,emp2,e***,emp4,emp5,emp6,emp7,emp8); 组合分割槽： 範围杂凑组合分割槽： create table emp ( empno number(4), ename varchar2(30), hiredate date) partition by range (hiredate) subpartition by hash (empno) subpartitions 2 (partition e1 values less than (to_date(20210501,yyyymmdd)), partition e2 values less than (to_date(20211001,yyyymmdd)), partition e3 values less than (maxvalue)); 範围列表组合分割槽（***posite partitioning）： 在组合分割槽中，顶层分割槽机制总是区间分割槽，第二级分割槽机制可能是列表分割槽或者杂凑分割槽，使用组合分割槽时，并没有分割槽段，只有子分割槽段，分割槽本身并没有段（这就类似于分割槽表没有段），资料物理的储存在子分割槽段上，分割槽成为一个逻辑容器，或者指向实际子分割槽的容器。

每个区间分割槽不需要有相同数目的子分割槽 create table customers_part ( customer_id number(6), cust_first_name varchar2(20), cust_last_name varchar2(20), nls_territory varchar2(30), credit_limit number(9,2)) partition by range (credit_limit) subpartition by list (nls_territory) subpartition template (subpartition east values (china, japan, india, thailand), subpartition west values (america, germany, italy, switzerland), subpartition other values (default)) (partition p1 values less than (1000), partition p2 values less than (2500), partition p3 values less than (maxvalue)); 索引分割槽： 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)); 1.1.2 分割槽表的维护： 增加分割槽： alter table sales add partition sales2000_q1 values less than (to_date(‘2000-04-01’,’yyyy-mm-dd’) tablespace ts_sale2000q1; 如果已有maxvalue分割槽，不能增加分割槽，可以採取**分割槽的办法增加分割槽！ 删除分割槽： 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 注：若分割槽表跨不同表空间，做汇出、汇入时目标资料库必须预建这些表空间。

分表区各区所在表空间在做汇入时目标资料库一定要预建这些表空间！ 这些表空间不一定是使用者的预设表空间，只要存在即可如果有一个不存在，就会报错！ 预设时，对分割槽表的许多表维护操作会使全域性索引不可用，标记成unusable 那么就必须重建整个全域性索引或其全部分割槽如果已被分割槽，oracle 允许在用于维护操作的alter table 语句中指定update global indexes 来过载这个预设特性，指定这个子句也就告诉oracle 当它执行维护操作的ddl 语句时更新全域性索引，这提供了如下好处： 1.在操作基础表的同时更新全域性索引这就不需要后来单独地重建全域性索引； 2.因为没有被标记成unusable， 所以全域性索引的可用性更高了，甚至正在执行分割槽的ddl 语句时仍然可用索引来访问表中的其他分割槽,避免了查询所有失效的全域性索引的名字以便重建它们； 另外在指定update global indexes 之前还要考虑如下效能因素: 1.因为要更新事先被标记成unusable 的索引，所以分割槽的ddl 语句要执行更长时间，当然这要与先不更新索引而执行ddl 然后再重建索引所花的时间做个比较，一个适用的规则是如果分割槽的大小小于表的大小的5% ，则更新索引更快一点； 2.drop truncate 和exchange 操作也不那么快了，同样这必须与先执行ddl 然后再重建所有全域性索引所花的时间做个比较； 3.要登记对索引的更新併产生重做记录和撤消记录，重建整个索引时可选择nologging； 4.重建整个索引产生一个更有效的索引，因为这更利于使用空间，再者重建索引时允许修改储存选项。

注意分割槽索引结构表不支援update global indexes 子句 1.1.3 普通表变为分割槽表 将已存在资料的普通錶转变为分割槽表，没有办法通过修改属性的方式直接转化为分割槽表，必须通过重建的方式进行转变，一般可以有三种方法，视不同场景使用： 用例：方法一：利用原表重建分割槽表 create table t (id number primary key, time date); insert into t select rownum, sysdate - rownum from dba_objects where rownum <= 5000; ***mit; 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(2021-1-1, yyyy-mm-dd)), partition p3 values less than (to_date(2021-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; co。