partition table and partition index
l 增加可用性,一個分割槽有問題,不影響其他的分割槽
l 減少維護工作量
l 均衡I/O,減少爭用。
l 提高查詢速度
l 分割槽對user是透明
Table
1).range partition
create table part1(
id number primary key,
name varchar2(10),
dt date
)
partition by range(dt)
(
partition p1 values less than (to_date('2012/01/01','yyyy/mm/dd')) tablespace users,
partition p2 values less than (to_date('2013/01/01','yyyy/mm/dd')) tablespace test,
partition p3 values less than (maxvalue) tablespace users
)
SQL> insert into part1 values(4,'a',sysdate);
已建立 1 個資料列.
SQL> insert into part1 values(5,'b',sysdate-200);
已建立 1 個資料列.
SQL> insert into part1 values(6,'c',sysdate+200);
已建立 1 個資料列.
SQL> commit;
確認完成.
SQL> select * from part1 partition(p2);
ID NAME DT
---------- -------------------- --------------
4 a 01-7月 -12
1 a 01-7月 -12
Range partition可以是兩個欄位或多個欄位
create table part2(
id number primary key,
dt date,
des varchar2(20)
)
partition by range(id,dt)
(
partition p1 values less than (1,to_date('2012/01/01','yyyy/mm/dd')) tablespace users,
partition p2 values less than (10,to_date('2013/01/01','yyyy/mm/dd')) tablespace test,
partition p3 values less than (maxvalue,maxvalue) tablespace users
)
2).hash 分割槽 à雜湊分割槽
主要用去取值不確定的情況下采用的方法
create table part_hash(
id number primary key,
name varchar2(20),
dt date)
partition by hash(id)
(
partition p1 tablespace users,
partition p2 tablespace test
)
SQL> select * from part_hash partition (p1);
ID NAME DT
---------- ---------------------------------------- --------------
2 b 30-6月 -12
SQL> select * from part_hash partition (p2);
ID NAME DT
---------- ---------------------------------------- --------------
1 a 01-7月 -12
3 c 28-6月 -12
或者採用如下方法:
create table part_hash1(
id number primary key,
name varchar2(20),
dt date)
storage(initial 1024k) --表空間的初始分配1024k
partition by hash(id)
partitions 3
store in (users,test,system);
3).list partition à列表分割槽
主要是用於某個列的值是可以列舉的
create table part4
(id number(1),
name varchar2(10)
)
partition by list(id)
(partition p1 values (1) tablespace users,
partition p2 values (2) tablespace test,
partition p3 values (default) tablespace users
)
insert into part4 values(1,'A')
insert into part4 values(2,'B')
insert into part4 values(3,'B')
select * from part4 partition (p3)
4).組合分割槽
a.Range-hash partition
b.range-list partition
c.range-range partition
d.list-range partition
e.list-list partition
f.list-hash partition
eg:
create table part5
(id int,
name varchar2(10)
)
partition by range(id)
subpartition by hash(name)
subpartitions 2 store in (users,test)
(partition p1 values less than (10) tablespace users,
partition p2 values less than (20) tablespace test,
partition p3 values less than (maxvalue) tablespace users
)
先進行range partition,然後子分割槽按照hash進行。
5).interval分割槽11g新增)
Interval分割槽是range分割槽的一種加強版本。可以實現equi-sized範圍分割槽的自動化。隨著資料的增加會分配更多的分割槽,並自動建立新的分割槽和本地索引。
create table part6
(id int,
name varchar2(20),
dt date
)
partition by range(dt)
interval (numtoyminterval(1,'month'))
(
partition p1 values less than(to_date('2012/01/01','yyyy/mm/dd'))
)
6).外來鍵分割槽(11g新增)
SQL> create table part1
2 (id int,name varchar2(10))
3 partition by range(id)
4 (partition p1 values less than (100),
5 partition p2 values less than (200),
6 partition p3 values less than (maxvalue)
7 );
SQL> alter table part1 add primary key(id);
Table altered.
SQL> create table part7
2 (id int not null, à這個必須要,否則出現ora-14652
3 name varchar2(20),
4 constraint part7_fk foreign key(id) references part1(id)
5 )
6 partition by reference (part7_fk)
7 /
Table created.
7).虛擬列分割槽
11g以前的版本,只有分割槽鍵存在於表中才可以對該表實施分割槽操作。而11g則不需要。
create table part3
(id int,
name varchar2(20),
id1 int generated always as (to_number(substr(to_char(id),1,1)))
)
partition by list(id1)
( partition p1 values (3),
partition p2 values (5),
partition p3 values (default)
)
8).表分割槽的管理
Range partition:
增加partition:
|
Range Partition |
Hash Partition |
Add Partition |
組合 Partition |
增加分割槽 |
Add partition |
Add partition |
Add partition |
Add partition |
合併分割槽 |
|
Coalesce Partition |
|
Modify partition Coalesce Subpartition… |
刪除分割槽 |
Drop partition |
|
Drop Partition |
Drop partition |
交換分割槽 |
Exchange Partition |
Exchange Partition |
Exchange Partition |
Exchange Partition Exchange Subpartition |
融合分割槽 |
Merge partitions |
|
Merge Partitions |
Merge partitions |
修改分割槽增加值 |
|
|
Modify partition Add values |
|
修改分割槽刪除值 |
|
|
Modify Partition Drop values |
|
移動分割槽 |
Move partition |
Move partition |
Move partition |
Move partition |
更名分割槽 |
Rename partition |
Rename partition |
Rename partition |
Rename partition |
分割分割槽 |
Split partition |
|
Splite partition |
Split partition |
捨棄分割槽 |
Truncate partition |
Truncate partition |
Truncate partition |
Truncate partition Truncate subpartition |
增加分割槽
SQL> alter table part1 add partition p3 values less than (to_date('2014/01/01','yyyy/mm/dd')) [tablespace xxx];
已更改表格.
SQL> alter table part1 add partition p4 values less than (maxvalue) [tablespace xxx];
合併分割槽(hash partition)
SQL> alter table part_hash coalesce partition;
SQL> alter table part5 modify partition p1 coalesce subpartition;
刪除分割槽
SQL> alter table part1 drop partition p3;
如果有全域性index,刪除partition之後,index會標識為unusable,需要重建index。Alter index xxx rebuild
如果有約束:alter table xxx disable constraints cons_xxx
Alter table xxxx drop partition px;
Alter table xxx enable constraints cons_xxx
融合分割槽
create index part1_index on part1(id,name)
local
(partition p1,
partition p2,
partition p3,
partition p4
)
SQL> alter table part1 merge partitions p3,p4 into partition p5;
索引變成無效
SQL> select status from user_indexes where index_name='PART1_INDEX'
2 ;
STATUS
----------------
N/A
SQL> alter table part1 modify partition p5 rebuild unusable local indexes;
非分割槽錶轉換為分割槽表
1).檢測是否可以轉換
SQL> exec dbms_redefinition.can_redef_table('scott','test')
已順利完成 PL/SQL 程式.
2).建立臨時表並化分割槽
create table test_temp
(owner varchar2(30) not null,
object_name varchar2(30) not null,
object_id number not null,
object_type varchar2(30)
)
partition by range(object_id)
(partition p1 values less than (400) tablespace users,
partition p2 values less than (950) tablespace test,
partition p3 values less than (maxvalue) tablespace users
)
3).開始重定義表
SQL> exec dbms_redefinition.start_redef_table('scott','test','test_temp')
已順利完成 PL/SQL 程式.
4).建立索引限制及觸發器
SQL> var v_num number
SQL> exec dbms_redefinition.copy_table_dependents('scott','test','test_temp',dbms_redefinition.cons_orig_params,true,true,true,true,:v_num,true);
已順利完成 PL/SQL 程式.
http://yangtingkun.itpub.net/post/468/494878 (其中遇見問題參考)
5).同步表內容
如果同步的資料很大,則可以考慮透過prebuilt table的方法建立物化檢視
Exec dbms_redefinition.sync_interim_table(‘scott’,’test’,’test_temp’)
6).結束轉換過程
SQL> exec dbms_redefinition.finish_redef_table('scott','test','test_temp')
已順利完成 PL/SQL 程式.
7).刪除臨時表test_temp
Drop table test_temp
其中可以使用dbms_redefinition.abort_redef_table(‘scott’,’test’,’test_temp’)中途退出該過程。
Index
Local index:
l 本地索引一定是分割槽索引,分割槽鍵等同於表的分割槽鍵,分割槽數等同於表的分割槽數目,本地索引的分割槽機制和表的分割槽機制一樣。
l 如果本地索引的的索引列伊分割槽鍵開頭,則稱之為字首索引
l 如果本地索引的索引列不是以分割槽鍵開頭,不包含分割槽鍵之,稱之為非字首索引
l 點陣圖索引只能為本地分割槽索引
l 本地所有多應用於資料倉儲環境中olap。
l 本地所有隻支援分割槽內的唯一性,無法支援表上的唯一性,如果要使用本地索引給表做唯一性約束,則約束中必須要包括分割槽鍵列。
create index test_index on test(object_id)
local
(
partition p1 tablespace users,
partition p2 tablespace test,
partition p3 tablespace tbs
)
alter table test add primary key(object_Id) using index test_index
或者:
create index test_index1 on test(object_id) local
select dbms_metadata.get_ddl('INDEX','TEST_INDEX1','SCOTT') from dual
create index test_index2 on test(object_type) local
非字首索引,索引中不包含分割槽鍵
select dbms_metadata.get_ddl('INDEX','TEST_INDEX2','SCOTT') from dual
SQL> select index_name,table_name,partitioning_type,locality,alignment from user_part_indexes where table_name='TEST';
INDEX_NAME TABLE_NAME PARTITIONING_T LOCALITY ALIGNMENT
--------------- -------------------- -------------- ------------ ------------------------
TEST_INDEX1 TEST RANGE LOCAL PREFIXED
TEST_INDEX2 TEST RANGE LOCAL NON_PREFIXED
Global index:
l 全域性索引的分割槽鍵和分割槽數和表的分割槽鍵和分割槽數可能都不相同,表和全域性索引的分割槽機制不一樣。
l 全域性索引可以分割槽,也可以不分割槽索引。全域性索引必須是字首索引,即全域性索引的索引列必須是一索引分割槽鍵作為前幾列。
l 全域性分割槽索引的索引條目可能指向若干個分割槽,因此,對於全域性分割槽索引,即使只截斷一個分割槽中的資料,都需要rebuild若干個分割槽甚至是整個索引。
l 全域性索引多用於oltp系統中。
l 全域性分割槽索引只按範圍或者雜湊hash分割槽,hash分割槽是10g以後才支援。
l 9i後對分割槽表組move或者truncate時可以用update global indexes語句來同步更新全域性分割槽索引,用消耗一定資源來換可用性。
l 全域性分割槽索引只能是B樹索引,而且只能是字首索引。
Eg:
create table test1
(id int,
name varchar2(20),
desc1 varchar2(20)
)
partition by range(id)
(
partition p1 values less than (1000) tablespace users,
partition p2 values less than (5000) tablespace test,
partition p3 values less than (maxvalue) tablespace tbs
)
建立索引:
create index test1_global_index on test1(id) global
partition by range(id)
(partition p1 values less than (4000) tablespace users,
partition p2 values less than (maxvalue) tablespace test
)
SQL> alter table test1 drop partition p3;
已更改表格.
索引的狀態發生變化
SQL> select index_name,partition_name,status from user_ind_partitions where index_name='TEST1_GLOBAL_INDEX';
INDEX_NAME PARTITION_ STATUS
-------------------- ---------- ----------------
TEST1_GLOBAL_INDEX P1 UNUSABLE
TEST1_GLOBAL_INDEX P2 UNUSABLE
必須加上前置碼
SQL> l
1 create index test1_global_index1 on test1(object_type) global
2 partition by range(id)
3 (partition p1 values less than (4000),
4 paritition p2 less than (maxvalues)
5* )
SQL> /
partition by range(id)
*
ERROR 在行 2:
ORA-14038: GLOBAL 分割索引必須加上前置碼
不能和點陣圖索引使用
SQL> l
1 create bitmap index test1_global_index1 on test1(object_type) global
2 partition by range(id)
3 (partition p1 values less than (4000),
4 paritition p2 less than (maxvalues)
5* )
SQL> /
create bitmap index test1_global_index1 on test1(object_type) global
*
ERROR 在行 1:
ORA-25113: GLOBAL 可能無法與點陣圖索引一起使用
操作索引部分
|
索引型別 |
range |
hash/list |
組合 |
刪除索引分割槽 |
global |
drop partition |
||
local |
|
|
|
|
重建索引分割槽 |
global |
rebuild partition |
||
local |
rebuild partition |
rebuild partition |
rebuild subpartition |
|
重新命名索引分割槽 |
global |
rename partition |
||
local |
rename partition |
rename partition |
rename subpartition |
|
分割索引分割槽 |
global |
split partition |
||
local |
|
|
|
SQL> alter index test1_global_index drop partition p1;
已更改索引.
SQL> alter index test1_global_index rebuild partition p2;
已更改索引.
SQL> alter index test1_global_index rename partition p2 to p1;
已更改索引.
user_part_tables 分割槽表資訊
user_tab_partitions 分割槽分層資訊
user_tab_subpartitions 子分割槽分層資訊
user_part_key_columns 分割槽表的分割槽鍵列
user_subpart_key_columns 組合分割槽表的子分割槽鍵列
表的分割槽的列統計資訊和直方圖
user_part_col_statistics
user_part_histograms 表的分割槽上的直方圖的直方圖的直方圖資料
user_subpart_histograms
user_part_indexes 分割槽索引的分割槽資訊
user_ind_partitions 分割槽層次的分割槽資訊,分割槽的儲存引數
user_ind_subpartitions
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-734534/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- partition table test
- partition table(1)
- partition table(2)
- When to Partition a Table
- 拆分Table 為Partition Table
- [Oracle] Partition table exchange Heap tableOracle
- partition table update partition-key result in changing tablespace
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- 分割槽表PARTITION table
- Oracle 普通table 轉換為partition tableOracle
- create a partition table using a exsit table
- db2 partition table testDB2
- Interval Partition table 11G
- Oracle分割槽表(Partition Table)Oracle
- How to partition a non-partitioned table
- 分割槽表PARTITION table(轉)
- partition table中truncate應用
- partition_global index補疑(一)Index
- best practice of rebuild your partition table local index online by using: "update indexes"RebuildIndex
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(二)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(三)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(四)Oracle索引Index
- PARTITION partition01
- PARTITION partition02
- PARTITION partition04
- mysql partition table use to_days bugMySql
- 關於partition table import的問題Import
- doesn't contain a valid partition tableAI
- add hash partition , default tablespace for patitioned table
- oracle分割槽及分割槽索引partition_partition index_維護(五)_快捷方法Oracle索引Index
- Pruning、Reference Partition、Exchange Partition
- partition timestamp(0) not use partition
- 那上邊的到底是 global index還是partition indexIndex
- 【DB】Direct Path EXP Corrupts The Dump If An Empty Table Partition Exists
- Applying “Incremental Statistic” for Oracle Big Partition TableAPPREMOracle
- PARTITION SPILT
- hive partitionHive