oracle的表空間、分割槽表、以及索引的總結

zhanglincon發表於2009-04-30
Oracle索引
 有關表分割槽的一些維護性操作： 
一、新增分割槽 
以下程式碼給SALES表新增了一個P3分割槽 
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD')); 
注意：以上新增的分割槽界限應該高於最後一個分割槽界限。 

以下程式碼給SALES表的P3分割槽新增了一個P3SUB1子分割槽 
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 

二、刪除分割槽 
以下程式碼刪除了P3表分割槽： 
ALTER TABLE SALES DROP PARTITION P3; 

在以下程式碼刪除了P4SUB1子分割槽： 
ALTER TABLE SALES DROP SUBPARTITION P4SUB1; 
注意：如果刪除的分割槽是表中唯一的分割槽，那麼此分割槽將不能被刪除，要想刪除此分割槽，必須刪除表。 

三、截斷分割槽 
截斷某個分割槽是指刪除某個分割槽中的資料，並不會刪除分割槽，也不會刪除其它分割槽中的資料。當表中即使只有一個分割槽時，也可以截斷該分割槽。通過以下程式碼截斷分割槽： 
ALTER TABLE SALES TRUNCATE PARTITION P2; 
通過以下程式碼截斷子分割槽： 
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; 

四、合併分割槽 
合併分割槽是將相鄰的分割槽合併成一個分割槽，結果分割槽將採用較高分割槽的界限，值得注意的是，不能將分割槽合併到界限較低的分割槽。以下程式碼實現了P1 P2分割槽的合併： 
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; 

五、拆分分割槽 
拆分分割槽將一個分割槽拆分兩個新分割槽，拆分後原來分割槽不再存在。注意不能對HASH型別的分割槽進行拆分。 
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) 
INTO (PARTITION P21,PARTITION P22); 

六、接合分割槽(coalesca) 
結合分割槽是將雜湊分割槽中的資料接合到其它分割槽中，當雜湊分割槽中的資料比較大時，可以增加雜湊分割槽，然後進行接合，值得注意的是，接合分割槽只能用於雜湊分割槽中。通過以下程式碼進行接合分割槽： 
ALTER TABLE SALES COALESCA PARTITION; 

七、重新命名錶分割槽 
以下程式碼將P21更改為P2 
ALTER TABLE SALES RENAME PARTITION P21 TO P2; 

九、跨分割槽查詢 
select sum( *) from ( 
(select count(*) cn from t_table_SS PARTITION (P200709_1) 
union all 
select count(*) cn from t_table_SS PARTITION (P200709_2)); 

十、查詢表上有多少分割槽 
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName' 

十一、查詢索引資訊 
select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc 


--顯示資料庫所有分割槽表的資訊： 
select * from DBA_PART_TABLES 

--顯示當前使用者可訪問的所有分割槽表資訊: 
select * from ALL_PART_TABLES 

--顯示當前使用者所有分割槽表的資訊： 
select * from USER_PART_TABLES 

--顯示錶分割槽資訊 顯示資料庫所有分割槽表的詳細分割槽資訊： 
select * from DBA_TAB_PARTITIONS 

--顯示當前使用者可訪問的所有分割槽表的詳細分割槽資訊： 
select * from ALL_TAB_PARTITIONS 

--顯示當前使用者所有分割槽表的詳細分割槽資訊： 
select * from USER_TAB_PARTITIONS 

--顯示子分割槽資訊 顯示資料庫所有組合分割槽表的子分割槽資訊： 
select * from DBA_TAB_SUBPARTITIONS 

--顯示當前使用者可訪問的所有組合分割槽表的子分割槽資訊： 
select * from ALL_TAB_SUBPARTITIONS 

--顯示當前使用者所有組合分割槽表的子分割槽資訊： 
select * from USER_TAB_SUBPARTITIONS 

--顯示分割槽列 顯示資料庫所有分割槽表的分割槽列資訊： 
select * from DBA_PART_KEY_COLUMNS 

--顯示當前使用者可訪問的所有分割槽表的分割槽列資訊： 
select * from ALL_PART_KEY_COLUMNS 

--顯示當前使用者所有分割槽表的分割槽列資訊： 
select * from USER_PART_KEY_COLUMNS 

--顯示子分割槽列 顯示資料庫所有分割槽表的子分割槽列資訊： 
select * from DBA_SUBPART_KEY_COLUMNS 

--顯示當前使用者可訪問的所有分割槽表的子分割槽列資訊： 
select * from ALL_SUBPART_KEY_COLUMNS 

--顯示當前使用者所有分割槽表的子分割槽列資訊： 
select * from USER_SUBPART_KEY_COLUMNS 

--怎樣查詢出oracle資料庫中所有的的分割槽表 
select * from user_tables a where a.partitioned='YES' 

--刪除一個表的資料是 
truncate table table_name; 

--刪除分割槽表一個分割槽的資料是 
alter table table_name truncate partition p5; 

注：分割槽根據具體情況選擇。 

表分割槽有以下優點： 
1、資料查詢：資料被儲存到多個檔案上，減少了I/O負載，查詢速度提高。 
2、資料修剪：儲存歷史資料非常的理想。 
3、備份：將大表的資料分成多個檔案，方便備份和恢復。 
4、並行性：可以同時向表中進行DML操作，並行性效能提高。 
================================================ 

索引： 
1、一般索引： 
create index index_name on table(col_name); 
2、Oracle 分割槽索引詳解 
語法：Table Index 
CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name 
ON [schema.]table_name [tbl_alias] 
(col [ASC | DESC]) index_clause index_attribs 

index_clauses: 
分以下兩種情況 

1. Local Index 
就是索引資訊的存放位置依賴於父表的Partition資訊，換句話說建立這樣的索引必須保證父表是Partition 
1.1 索引資訊存放在父表的分割槽所在的表空間。但是僅可以建立在父表為HashTable或者composite分割槽表的。 
LOCAL STORE IN (tablespace) 
1.2 僅可以建立在父表為HashTable或者composite分割槽表的。並且指定的分割槽數目要與父表的分割槽數目要一致 
LOCAL STORE IN (tablespace) (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace]]]]) 

1.3 索引資訊存放在父表的分割槽所在的表空間，這種語法最簡單，也是最常用的分割槽索引建立方式。 
Local 
1.4 並且指定的Partition 數目要與父表的Partition要一致 
LOCAL (PARTITION [partition 
[LOGGING|NOLOGGING] 
[TABLESPACE {tablespace|DEFAULT}] 
[PCTFREE int] 
[PCTUSED int] 
[INITRANS int] 
[MAXTRANS int] 
[STORAGE storage_clause] 
[STORE IN {tablespace_name|DEFAULT] 
[SUBPARTITION [subpartition [TABLESPACE tablespace]]]]) 

Global Index 
索引資訊的存放位置與父表的Partition資訊完全不相干。甚至父表是不是分割槽表都無所謂的。語法如下： 
GLOBAL PARTITION BY RANGE (col_list) 
( PARTITION partition VALUES LESS THAN (value_list) 
[LOGGING|NOLOGGING] 
[TABLESPACE {tablespace|DEFAULT}] 
[PCTFREE int] 
[PCTUSED int] 
[INITRANS int] 
[MAXTRANS int] 
[STORAGE storage_clause] ) 
但是在這種情況下，如果父表是分割槽表，要刪除父表的一個分割槽都必須要更新Global Index ,否則索引資訊不正確 
ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes 

--查詢索引 
select object_name,object_type,tablespace_name,sum(value) 
from v$segment_statistics 
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX' 
group by object_name,object_type,tablespace_name 
order by 4 desc

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8554499/viewspace-592386/，如需轉載，請註明出處，否則將追究法律責任。

相關文章