oracle壓縮分區表

A. oracle11g怎麼建立壓縮表

在數據量變的很大的情況下，可以考慮表壓縮，減少磁碟的存儲，減少buffer cache的消耗，加快查詢的速度，壓縮需要在數據載入和dml的時候消耗一些cpu.表壓縮對於應用是透明的。

你可以在表空間，表或分區級別指定壓縮，如果在表空間級別指定壓縮，那麼這個表空間中的所有表默認都是壓縮的。

壓縮會發生在數據插入，更新或批量載入到表時。有幾種類型的壓縮。基本的，oltp的，dss的，歸檔的。他們的cpu消耗見文檔。

當使用基本壓縮，數據倉庫壓縮，或歸檔壓縮的時候，壓縮僅僅是在批量載入到一個表的時候發生。當你使用了oltp壓縮，壓縮發生在數據插入，更新，批量載入。

如果你使用了alter table命令讓表啟用壓縮，那麼對於已存在的數據是不受影響的，只有在啟用壓縮後的插入，或更新會受影響。也可以使用ALTER TABLE…NOCOMPRESS將一個表設置成非壓縮，對於已經的壓縮的表是沒有影響的，壓縮的數據還是壓縮的，只是新插入的數據是不被壓縮。

創建一個oltp壓縮類型的表
CREATE TABLE orders … COMPRESS FOR OLTP;

如果你不指定壓縮的類型，那麼默認的情況下是基本壓縮類型。下面的2個語句是等價的。
CREATE TABLE sales_history … COMPRESS BASIC;

CREATE TABLE sales_history … COMPRESS;

下面的表是一個數據倉庫壓縮類型的表，對於經常查詢，並且沒有dml的表適用。
CREATE TABLE sales_history … COMPRESS FOR QUERY;

創建一個歸檔壓縮類型的表
CREATE TABLE sales_history … COMPRESS FOR ARCHIVE;

表可以包含壓縮和非壓縮的分區，並且不同的分區的壓縮的類型可以是不同的，如果在分區上指定的壓縮的類型與表上指定的壓縮的類型不同，那麼分區上的優先。

查看錶是否是壓縮的，及分區是否是壓縮的。
sql> SELECT table_name, compression, compress_for FROM user_tables;

SELECT table_name, partition_name, compression,compress_for
FROM user_tab_partitions;

查看錶中的行是什麼壓縮類型
SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
ownname => 『HR』,
tabname => 『EMPLOYEES』,
row_id => 『AAAVEIAAGAAAABTAAD』),
1, 『No Compression』,
2, 『Basic or OLTP Compression』,
4, 『Hybrid Columnar Compression for Query High』,
8, 『Hybrid Columnar Compression for Query Low』,
16, 『Hybrid Columnar Compression for Archive High』,
32, 『Hybrid Columnar Compression for Archive Low』,
『Unknown Compression Type』) compression_type
FROM DUAL;

修改壓縮的級別

如果表是分區表，使用在線重定義可以修改表的壓縮級別。如果表是分分區表，那麼可以使用alter table ..move.. compress for語句修改表的壓縮級別，這個語句會阻塞dml操作。

對於壓縮表的限制：
1基本壓縮，你不能為添加的列指定默認的值。刪除列是不被支持的。
2oltp壓縮，如果要添加一個帶默認值的列，那麼需要指定not null，可以刪除列，但是數據只是在內部做了一個不被使用的標記而已。

3在線段收縮是不被支持的。

4上面的壓縮的方式不適合於lobs類型，他們有自己的壓縮方式。

5基本壓縮類型的表的pct_free參數自動的設置成0.

在上面的描述中可以看到對於基本類型的壓縮，沒有使用直接路徑插入的行及更新的行是不會被壓縮的。對於我們的oltp系統基本是沒有用處的，只測試下oltp的壓縮方式，這種方式對於沒有使用直接路徑插入的行及被更新的行都會壓縮。

SQL> select count(*) from xyu2;

COUNT(*)

22096384

SQL> set serveroutput on
SQL> exec show_space(『BAIXYU2』,』T』,』AUTO』);
Total Blocks……………………….334336
Total Bytes………………………..2738880512
Unused Blocks………………………1916
Unused Bytes……………………….15695872
Last Used Ext FileId………………..4
Last Used Ext BlockId……………….671872
Last Used Block…………………….6276

CREATE TABLE xyu3 COMPRESS FOR OLTP as select * from xyu2;

SQL> exec show_space(『BAIXYU3』,』T』,』AUTO』);
Total Blocks……………………….107520
Total Bytes………………………..880803840
Unused Blocks………………………112
Unused Bytes……………………….917504
Last Used Ext FileId………………..4
Last Used Ext BlockId……………….116224
Last Used Block…………………….912

看到使用的塊由33w下降到10w。壓縮效果還是不錯，但是是否使用還要結果上面的限制來根據實際情況評估。

B. oracle表壓縮後索引還有效不

通過PL/SQL可以直接查看某表是否建索引，通過SQL查詢select status,T.* from user_indexes Twhere table_name='表名'

oracle查看有效索引是這個：select status,T.* from user_indexes T，where table_name='TABLE1'

最好弄個圖像界面軟體，就能知道，比如：PL/SQLDeveloper

資料庫中的失效的索引、索引分區、子分區：如果不是失效的索引，那麼都是有效的。

C. oracle 11g分區表最多支持多少分區

Oracle11g有關分區的限制如下：
Partitions Maximum length of linear partitioning key 4 KB - overhead
Partitions Maximum number of columns in partition key 16 columns
Partitions Maximum number of partitions allowed per table or index 1024K - 1
一個表或索引最多支持1024*1024-1個分區

D. oracle分區表有什麼作用

(1) 表空間及分區表的概念
表空間：
是一個或多個數據文件的集合，所有的數據對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。
分區表：
當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程序的性能就會下降，這時就應該考慮對表進行分區。表進行分區後，邏輯上表仍然是一張完整的表，只是將表中的數據在物理上存放到多個「表空間」(物理文件上)，這樣查詢數據時，不至於每次都掃描整張表而只是從當前的分區查到所要的數據大大提高了數據查詢的速度。

(2).表分區的具體作用
Oracle的表分區功能通過改善可管理性、性能和可用性，從而為各式應用程序帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的性能大大提高。此外,分區還可以極大簡化常見的管理任務，分區是構建千兆位元組數據系統或超高可用性系統的關鍵工具。 分區功能能夠將表、索引或索引組織表進一步細分為段，這些資料庫對象的段叫做分區。每個分區有自己的名稱，還可以選擇自己的存儲特性。從資料庫管理員的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理員在管理分區後的對象時有相當大的靈活性。但是，從應用程序的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 命令訪問分區後的表時，無需任何修改。
什麼時候使用分區表：
1、表的大小超過2GB。
2、表中包含歷史數據，新的數據被增加到新的分區中。

(3).表分區的優缺點
表分區有以下優點：
1、改善查詢性能：對分區對象的查詢可以僅搜索自己關心的分區，提高檢索速度。
2、增強可用性：如果表的某個分區出現故障，表在其他分區的數據仍然可用；
3、維護方便：如果表的某個分區出現故障，需要修復數據，只修復該分區即可；
4、均衡I/O：可以把不同的分區映射到不同磁碟以平衡I/O，改善整個系統性能。
缺點：
分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了在線重定義表的功能。

(4).表分區的幾種類型及操作方法

一.范圍分區：
范圍分區將數據基於范圍映射到每一個分區，這個范圍是你在創建分區時指定的分區鍵決定的。這種分區方式是最為常用的，並且分區鍵經常採用日期。舉個例子：你可能會將銷售數據按照月份進行分區。
當使用范圍分區時，請考慮以下幾個規則：
1、每一個分區都必須有一個VALUES LESS THAN子句，它指定了一個不包括在該分區中的上限值。分區鍵的任何值等於或者大於這個上限值的記錄都會被加入到下一個高一些的分區中。
2、所有分區，除了第一個，都會有一個隱式的下限值，這個值就是此分區的前一個分區的上限值。
3、在最高的分區中，MAXVALUE被定義。MAXVALUE代表了一個不確定的值。這個值高於其它分區中的任何分區鍵的值，也可以理解為高於任何分區中指定的VALUE LESS THAN的值，同時包括空值。
例一：
假設有一個CUSTOMER表，表中有數據200000行，我們將此表通過CUSTOMER_ID進行分區，每個分區存儲100000行，我們將每個分區保存到單獨的表空間中，這樣數據文件就可以跨越多個物理磁碟。下面是創建表和分區的代碼，如下：

CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

例二：按時間劃分

CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER(7) NOT NULL,
ORDER_DATE DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)

例三：MAXVALUE

CREATE TABLE RangeTable
(
idd INT PRIMARY KEY ,
iNAME VARCHAR(10),
grade INT
)
PARTITION BY RANGE (grade)
(
PARTITION part1 VALUES LESS THAN (1000) TABLESPACE Part1_tb,
PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE Part2_tb
);

二.列表分區：

該分區的特點是某列的值只有幾個，基於這樣的特點我們可以採用列表分區。
例一

CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000),
CUSTOMER_ID NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02)

例二

CREATE TABLE ListTable
(
id INT PRIMARY KEY ,
name VARCHAR (20),
area VARCHAR (10)
)
PARTITION BY LIST (area)
(
PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb,
PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb
);

三.散列分區（也稱HASH分區）：
這類分區是在列值上使用散列演算法，以確定將行放入哪個分區中。當列的值沒有合適的條件時，建議使用散列分區。
散列分區為通過指定分區編號來均勻分布數據的一種分區類型，因為通過在I/O設備上進行散列分區，使得這些分區大小一致。
例一：

CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)

簡寫：

CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER
)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (tbs01,tbs02,tbs03,tbs04,tbs05,tbs06,tbs07,tbs08);

hash分區最主要的機制是根據hash演算法來計算具體某條紀錄應該插入到哪個分區中,hash演算法中最重要的是hash函數，Oracle中如果你要使用hash分區，只需指定分區的數量即可。建議分區的數量採用2的n次方，這樣可以使得各個分區間數據分布更加均勻。

四.組合范圍列表分區
這種分區是基於范圍分區和列表分區，表首先按某列進行范圍分區，然後再按某列進行列表分區，分區之中的分區被稱為子分區。

CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)

五.復合范圍散列分區：
這種分區是基於范圍分區和散列分區，表首先按某列進行范圍分區，然後再按某列進行散列分區。

create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(『2006-01-01』,』yyyy-mm-dd』)),
partition part_02 values less than(to_date(『2010-01-01』,』yyyy-mm-dd』)),
partition part_03 values less than(maxvalue)
);

(5).有關表分區的一些維護性操作：
一、添加分區
以下代碼給SALES表添加了一個P3分區

ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN (TO_DATE('2003-06-01','YYYY-MM-DD')) TABLESPACE SPACE_NAME;

注意：增加一個分區的時候，增加的分區的條件必須大於現有分區的最大值，否則系統將提示ORA-14074 partition bound must collate higher than that of the last partition 錯誤。
以下代碼給SALES表的P3分區添加了一個P3SUB1子分區

ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE') TABLESPACE SPACE_NAME;

二、刪除分區
以下代碼刪除了SALES表中名為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;

五、拆分分區
拆分分區將一個分區拆分兩個新分區，拆分後原來分區不再存在但是如果表存在PMAX分區那麼原來的分區還是可以存在的。注意不能對 HASH類型的分區進行拆分。

ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
此拆分語句是將P2分區拆分為PARTITION P21與PARTITION P22兩個分區，日期小於2003-02-01的數據存於 P21分區中否則存於 P22分區中。

--包含PMAX分區的表拆分分區
ALTER TABLE hs_his.HISHOLDSINFO SPLIT PARTITION PMAX AT(20100900) INTO (partition P201008, partition PMAX);
此拆分語句是將 PMAX 分區拆分為P201008與 PMAX 兩個分區，數據小於20100900 的數據存於 P201008 分區中否則存於 PMAX 分區中。

六、接合分區(coalesca)
結合分區是將散列分區中的數據接合到其它分區中，當散列分區中的數據比較大時，可以增加散列分區，然後進行接合，值得注意的是，接合分區只能用於散列分區中。通過以下代碼進行接合分區：

ALTER TABLE SALES COALESCA PARTITION;

七、重命名表分區
以下代碼將P21更改為P2

ALTER TABLE SALES RENAME PARTITION P21 TO P2;

E. oracle 分區交換難道不支持索引壓縮

不應該吧，我這邊版本Oracle11g，測試沒有問題。步驟如下：

_id,object_namefromuser_objectsWHEREobject_id<87500;
createindexobjects1_idxonobjects1(object_id)compress1;
createtableobjects2
(
object_idnumber,
object_namevarchar2(128)
)
partitionbyrange(object_id)
(
partitionT1valueslessthan(87500),
partitionT2valueslessthan(87600),
partitionT3valueslessthan(maxvalue));
createindexobjects2_idxonobjects2(object_id)LOCALcompress1;
;
SELECT*FROMuser_indexesWHEREtable_nameIN('OBJECTS1','OBJECTS2');

F. 如何將Oracle資料庫的普通表轉換成分區表

在一個高可用系統中，如果需要改變一個表的定義是一件比較棘手的問題，尤其是對於7×24系統。Oracle提供的基本語法基本可以滿足一般性修改，但是對於把普通堆表改為分區表，把索引組織表修改為堆表等操作就無法完成了。而且，對於被大量DML語句訪問的表，幸運的是，Oracle從9i版本開始提供了在線重定義表功能，通過調用DBMS_REDEFINITION包，可以在修改表結構的同時允許DML操作。

在線重定義表具有以下功能：

修改表的存儲參數；

可以將表轉移到其他表空間；

增加並行查詢選項；

增加或刪除分區；

重建表以減少碎片；

將堆表改為索引組織表或相反的操作；

增加或刪除一個列。

調用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，還需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的許可權。

在線重定義表的步驟如下：

1.選擇一種重定義方法：

存在兩種重定義方法，一種是基於主鍵、另一種是基於ROWID。ROWID的方式不能用於索引組織表，而且重定義後會存在隱藏列M_ROW$$。默認採用主鍵的方式。

2.調用DBMS_REDEFINITION.CAN_REDEF_TABLE()過程，如果表不滿足重定義的條件，將會報錯並給出原因。

3.在用一個方案中建立一個空的中間表，根據重定義後你期望得到的結構建立中間表。比如：採用分區表，增加了COLUMN等。

4.調用DBMS_REDEFINITION.START_REDEF_TABLE()過程，並提供下列參數：被重定義的表的名稱、中間表的名稱、列的映射規則、重定義方法。

如果映射方法沒有提供，則認為所有包括在中間表中的列用於表的重定義。如果給出了映射方法，則只考慮映射方法中給出的列。如果沒有給出重定義方法，則認為使用主鍵方式。

5.在中間表上建立觸發器、索引和約束，並進行相應的授權。任何包含中間表的完整性約束應將狀態置為disabled。

當重定義完成時，中間表上建立的觸發器、索引、約束和授權將替換重定義表上的觸發器、索引、約束和授權。中間表上disabled的約束將在重定義表上enable。

6.（可選）如果在執行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執行了大量的DML操作，那麼可以選擇執行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最後一步執行FINISH_REDEF_TABLE()過程時的鎖定時間。

7.執行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程完成表的重定義。這個過程中，原始表會被獨占模式鎖定一小段時間，具體時間和表的數據量有關。

執行完FINISH_REDEF_TABLE()過程後，原始表重定義後具有了中間表的屬性、索引、約束、授權和觸發器。中間表上disabled的約束在原始表上處於enabled狀態。

8.（可選）可以重命名索引、觸發器和約束。對於採用了ROWID方式重定義的表，包括了一個隱含列M_ROW$$。推薦使用下列語句經隱含列置為UNUSED狀態或刪除。

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

下面是進行重定義操作後的結果：

原始表根據中間表的屬性和特性進行重定義；

START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之間在中間表上建立的觸發器、索引、約束和授權，現在定義在原始表上。中間表上disabled的約束在原始表上處於enabled狀態。

原始表上定義的觸發器、索引、約束和授權建立在中間表上，並會在刪除中間表時刪除。原始表上原來enabled狀態的索引，建立在中間表上，並處於disabled狀態。

任何定義在原始表上的存儲過程和游標都會變為INVALID，當下次調用時後自動進行編譯。

如果執行過程中出現錯誤或者人為選擇退出的話，可以執行DBMS_REDEFINITION.ABORT_REDEF_TABLE()過程。

其中UNAME 參數是指用戶；

Oracle的普通表沒有辦法通過修改屬性的方式直接轉化為分區表，必須通過重建的方式進行轉變，下面介紹三種效率比較高的方法，並說明它們各自的特點。

方法一：利用原表重建分區表

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE))

6 AS SELECT ID, TIME FROM T;

表已創建。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)

----------

6264

SQL> SELECT COUNT(*) FROM T PARTITION (P1);

COUNT(*)

----------

0

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

COUNT(*)

----------

6246

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

COUNT(*)

----------

18

優點：

方法簡單易用，由於採用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成後數據已經在分布到各個分區中了。

不足：

對於數據的一致性方面還需要額外的考慮。由於幾乎沒有辦法通過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句後對數據進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。

適用於修改不頻繁的表，在閑時進行操作，表的數據量不宜太大。

方法二：使用交換分區的方法

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創建6264行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (MAXVALUE));

表已創建。

SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;

表已更改。

SQL> RENAME T TO T_OLD;

表已重命名。

SQL> RENAME T_NEW TO T;

表已重命名。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)

----------

6264

優點：

只是對數據字典中分區和表的定義進行了修改，沒有數據的修改或復制，效率最高。如果對數據在分區中的分布沒有進一步要求的話，實現比較簡單。在執行完RENAME操作後，可以檢查T_OLD中是否存在數據，如果存在的話，直接將這些數據插入到T中，可以保證對T插入的操作不會丟失。

不足：

仍然存在一致性問題，交換分區之後RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到數據。如果要求數據分布到多個分區中，則需要進行分區的SPLIT操作，會增加操作的復雜度，效率也會降低。

適用於包含大數據量的表轉到分區表中的一個分區的操作。應盡量在閑時進行操作。

方法三：Oracle9i以上版本，利用在線重定義功能

步驟：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

表已創建。

SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

已創建6264行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER』, 'T', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL 過程已成功完成。

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)

2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

3 PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

4 PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

5 PARTITION P4 VALUES LESS THAN (MAXVALUE));

表已創建。

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(『USER』, 'T', 'T_NEW', -

> 'ID ID, TIME TIME', DBMS_REDEFINITION.CONS_USE_PK);

可以改為：

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(『USER』, 'T', 'T_NEW')

PL/SQL 過程已成功完成。

SQL> EXEC dbms_redefinition.sync_interim_table(『USER』, 'T', 'T_NEW')

現在，將中間表與原始表同步。（僅當要對表 T 進行更新時才需要執行該操作。）

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER』, 'T', 'T_NEW');

PL/SQL 過程已成功完成。

如果重組織失敗，那麼你就必須採取特殊的步驟來讓它重新開始。由於重定義過程需要創建表格的快照，因此為了重新開始這一過程，你必須調用DBMS_REDEFINITION.ABORT_REDEF_TABLE來釋放快照。

DBMS_REDEFINITION.ABORT_REDEF_TABLE過程有三個參數，即用戶（schema）、原始表格（original table name）名稱以及持有表格名稱（holding table name）。它「出棧」並允許你開始重組織表格。

SQL> SELECT COUNT(*) FROM T;

SQL> SELECT COUNT(*) FROM T PARTITION (P2);

SQL> SELECT COUNT(*) FROM T PARTITION (P3);

需要說明的是完成後，原表和中間表的結構也同時進行了交換，並且中間表裡面有原表的數據備份。

優點：

保證數據的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權並建立各種約束，可以做到切換完成後不再需要任何額外的管理操作。

不足：實現上比上面兩種略顯復雜,適用於各種情況。

然而，在線表格重定義也不是完美無缺的。下面列出了Oracle9i重定義過程的部分限制:

你必須有足以維護兩份表格拷貝的空間。

你不能更改主鍵欄。

表格必須有主鍵。

必須在同一個大綱中進行表格重定義。

在重定義操作完成之前，你不能對新加欄加以NOT NULL約束。

表格不能包含LONG、BFILE以及用戶類型（UDT）。

不能重定義鏈表（clustered tables）。

不能在SYS和SYSTEM大綱中重定義表格。

不能用具體化視圖日誌（materialized view logs）來重定義表格；不能重定義含有具體化視圖的表格。

不能在重定義過程中進行橫向分集（horizontal subsetting）。

G. 如何管理oracle 的分區表和索引（續

一、創建分區表
1、范圍分區
根據數據表欄位值的范圍進行分區
舉個例子，根據學生的不同分數對分數表進行分區，創建一個分區表如下：create table range_fraction(
id number(8),
name varchar2(20),
fraction number(3),
grade number(2)
)
partition by range(fraction)
(
partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --優秀)創建完分區表後向表中添加一些數據：declarename varchar2(10);
fraction number(5);
grade number(5);
i number(8):=1;
begin
for i in 1..100000 LOOP
SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);END LOOP;end;
查詢分區表：--分別查詢所有的，不及格的，中等的，優秀的成績select * from range_fraction;select * from range_fraction partition(fraction_60) ;select * from range_fraction partition(fraction_80) ;select * from range_fraction partition(fraction_100) ;當我們的查詢語句不指定分區的時候，如果分區欄位出現在where條件之後，Oracle會自動根據欄位值的范圍掃描響應的分區：
select * from range_fraction where fraction<30; 這句SQL執行的時候只會掃描不及格的分區select * from range_fraction where fraction<80; 這句SQL執行的時候會掃描不及格和中等兩個分區2、散列分區在范圍分區中，分區欄位的連續值通常出現在一個分區內，而在散列分區中，連續的欄位值不一定存儲在相同的分區中。散列分區把記錄分布在比范圍分區更多的分區上，這減少了I/O爭用的可能性。
為了創建一個散列分區，應該用partition by hash語句代替partition by range子句，如下所示：
第一種為各個分區指定不同的表空間，表空間數量不用等於分區數量，當表空間數量大於分區數量的時候會循環寫入各個表空間：
create table range_fraction1
(
id number(8),
name varchar2(20),
fraction number(3),
grade number(2)
)
partition by hash(fraction)
partitions 8
store in (users,tbs_haicheng)
第二種為每個分區指定一個分區名稱並為其指定表空間：create table range_fraction1(
id number(8),
name varchar2(20),
fraction number(3),
grade number(1)
)
partition by hash(fraction)
(
partition p1 tablespace tbs_haicheng ,
partition p2 tablespace users
);
3、列表分區
還可以使用列表分區代替范圍分區和散列分區。在列表分區中，告訴Oracle所有可能的值，並指定應當插入相應行的分區。
我們將1、2、3、4班級的數據放在一個分區，將6、7、8的數據放在一個分區，將其他的再放在一個分區，建表如下：
create table range_fraction1
(
id number(8),
name varchar2(20),
fraction number(3),
grade number(2)
)
partition by list(grade)
(
partition p1 values(1,2,3,4) tablespace tbs_haicheng ,partition p2 values(5,6,7,8) tablespace users,?
1
partition p3 values(default)
4、組合分區(創建子分區)
即分區的分區。例如可以先進行范圍分區，再對各個范圍分區創建列表分區。
對於非常大的表來說，這種組合分區是一種把數據分成可管理和可調整的組成部分的有效方法。
舉個例子：按照分數范圍分區後再將ID散列分區：
create table range_fraction1
(
id number(8),
name varchar2(20),
fraction number(3),
grade number(1)
)
partition by range(fraction)
subpartition by hash(id)
subpartitions 4
(
partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --優秀)二、索引分區
在分區表上可以建立三種類型的索引：1和普通表一樣的全局索引；2.全局分區索引；3.本地分區索引1.建立普通的索引create index index_fraction on range_fraction(fraction);2.建立本地分區索引（就是一個索引分區只能對應一個表分區）?
create index local_index_fraction on range_fraction(fraction) local;3.建立全局分區索引（屬於散列索引分區，就是一個索引分區可能指向多個表分區）?
create index global_index_fraction on range_fraction(fraction)GLOBAL partition by range(fraction)(
part_01 values less than(1000),
part_02 values less than(MAXVALUE)
);
三、管理分區表
1、增加分區
對於范圍分區來說，添加一個分區，必須該分區劃定的界限高於原來的最大界限，也就是說只能往上加，不能往下加。那麼對於用maxvalue關鍵字創建的范圍分區就不能增加分區了舉例：
create table range_fraction
(
id number(8),
name varchar2(20),
fraction number(3),
grade number(2)
)
partition by range(fraction)
(
partition fraction_60 values less than(40), --不及格partition fraction_80 values less than(60), --及格partition fraction_100 values less than(80) --優秀)對於該分區我們增加一個分區：
ALTER TABLE range_fraction ADD PARTITION fraction_100 VALUES LESS THAN (100);為列表分區添加一個分區：
create table range_fraction
(
id number(8),
name varchar2(20),
fraction number(3),
grade number(2)
)
partition by list(grade)
(
partition p1 values(1,2,3) tablespace tbs_haicheng ,partition p2 values(4,5,6) tablespace users);ALTER TABLE range_fraction ADD partition p3 VALUES (7,8);我們再為p3分區新增兩個表分區值：
ALTER TABLE range_fraction MODIFY PARTITION p3 ADD VALUES(9,10);然後再將p3分區的表分區值中的10刪掉：
ALTER TABLE range_fraction MODIFY PARTITION p3 DROP VALUES(10);為哈希分區添加一個子分區：
ALTER TABLE TABLENAME ADD PARTITION PARTNAME;添加一個子分區的格式：
ALTER TABLE TABLENAME MODIFY PARTITION PARTNAME ADD SUBPARTITION SUBPARTNAME;2、刪除分區刪除分區比較簡單，格式如下：
ALTER TABLE ... DROP PARTITION part_name;3、分區合並合並父分區格式：ALTER TABLE TABLENAME MERGE PARTITIONS p1-1, p1-2 INTO PARTITION p1 UPDATE INDEXES;如果省略了UPDATE INDEXES 的話需要為受影響的分區重建索引合並子分區的格式：
ALTER TABLE TABLENAME
MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;4、轉換分區可以將分區表轉換成非分區表，或者幾種不同分區表之間的轉換。如下：
CREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;這時，分區表hash_example中的part02分區的資料將被轉移到hash_part02這個非分區表中。

H. oracle分區表的分區有幾種類型

oracle分區表的分區有四種類型：范圍分區、散列分區、列表分區和復合分區。

特點如下：

1、范圍分區

就是根據資料庫表中某一欄位的值的范圍來劃分分區。

數據中有空值，Oracle機制會自動將其規劃到maxvalue的分區中。

2、散列分區

根據欄位的hash值進行均勻分布，盡可能地實現各分區所散列的數據相等。

散列分區即為哈希分區，Oracle採用哈希碼技術分區，具體分區如何由Oracle說的算，也可能我下一次搜索就不是這個數據了。

3、列表分區

列表分區明確指定了根據某欄位的某個具體值進行分區，而不是像范圍分區那樣根據欄位的值范圍來劃分的。

4、復合分區

根據范圍分區後，每個分區內的數據再散列地分布在幾個表空間中，這樣我們就要使用復合分區。復合分區是先使用范圍分區，然後在每個分區同再使用散列分區的一種分區方法。

比如將part_date的記錄按時間分區，然後每個分區中的數據分三個子分區，將數據散列地存儲在三個指定的表空間中。

(8)oracle壓縮分區表擴展閱讀：

分區的恢復方法：

如果資料庫運行在archive 模式下，那麼一旦資料庫損壞則可以通過冷備份(熱備份)和歸檔備份將資料庫恢復到斷點狀態。

資料庫控制文件恢復（假設所有控制文件均被破壞）：

資料庫基於文件系統： 利用操作系統的tar、cp等命令即可。

資料庫基於裸設備：dd if=$ORACLE_BASE/con.bak of=/dev/rdrd/drd1 seek=12

I. oracle怎麼更新分區表的數據

默認情況下，oracle的分區表對於分區欄位是不允許進行update操作的，如果有對分區欄位行進update，就會報錯——ORA-14402: 更新分區關鍵字列將導致分區的更改。但是可以通過打開表的row movement屬性來允許對分區欄位的update操作。
例：創建分區表test_part進行實驗
create table TEST_PART
(
A1 NUMBERnot null,
A2 DATE not null,
A3 VARCHAR2(6) not null,
A4 DATE not null,
A5 NUMBER not null,
)
partition by range (A1)
(
partition P1 values less than (1000),
partition P2 values less than (2000),
partition P3 values less than (3000),
partition P4 values less than (4000),
partition P5 values less than (5000),
partition P6 values less than (MAXVALUE)
);
插入如下的數據
SQL> select * from test_part;
A1 A2 A3 A4 A5
---------- ----------- ------ ----------- ----------
123 2006-06-30 123456 2006-06-30 123
456 2006-06-30 asdfgh 2006-06-30 456
1 2006-06-30 234123 2006-06-30 1
2 2006-06-30 234234 2006-06-30 2
1234 2006-06-30 456789 2006-06-30 1234
1111 2006-06-30 ewrqwe 2006-06-30 1111
2222 2006-06-30 fdafda 2006-06-30 2222
3333 2006-06-30 342342 2006-06-30 3333
5678 2006-06-30 qwerty 2006-06-30 5678
9 rows selected
分區P1、P2的數據分別為：
SQL> select rowid,t.* from test_part partition(p1) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLoAAGAAAtsEAAB 456 2006-06-30 asdfgh 2006-06-30 456
AAAGLoAAGAAAtsEAAC 1 2006-06-30 234123 2006-06-30 1
AAAGLoAAGAAAtsEAAD 2 2006-06-30 234234 2006-06-30 2
AAAGLoAAGAAAtsEAAE 123 2006-06-30 123456 2006-06-30 123
SQL> select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
直接update提示錯誤
SQL> update test_part set a1=1123 where a1=123;
update test_part set a1=1123 where a1=123
ORA-14402: 更新分區關鍵字列將導致分區的更改
打開row movement屬性
SQL> alter table test_part enable row movement;
Table altered
再次執行update操作
SQL> update test_part set a1=1123 where a1=123;
1 row updated
執行是成功的並遷移到分區P2上了，且這時候rowid也發生了變化
SQL> select rowid,t.* from test_part partition(p2) t;
ROWID A1 A2 A3 A4 A5
------------------ ---------- ----------- ------ ----------- ----------
AAAGLwAAGAAA+8MAAC 1234 2006-06-30 456789 2006-06-30 1234
AAAGLwAAGAAA+8MAAD 1111 2006-06-30 ewrqwe 2006-06-30 1111
AAAGLwAAGAAA+8PAAB 1123 2006-06-30 123456 2006-06-30 123
SQL>
enable row movement可以允許數據段的壓縮、update分區欄位的數據（跨分區的）

J. oracle 壓縮表空間有什麼作用

主要是節省空間，不過查詢的時候貌似更消耗cpu資源，對歷史數據比較適合。