oracle中分區常用命令2

1.2.2. 查詢分區表記錄：

SQL> select * from dinya_test partition(part_01);

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

--------------------------------------------------------------------------------

1 12 BOOKS 2005-1-14 14:19:

2 12 BOOKS 2005-2-13 14:19:

SQL>

SQL> select * from dinya_test partition(part_02);

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

--------------------------------------------------------------------------------

3 12 BOOKS 2006-5-30

4 12 BOOKS 2007-6-23

SQL>

SQL> select * from dinya_test partition(part_03);

TRANSACTION_ID ITEM_ID ITEM_DESCRIPTION TRANSACTION_DATE

--------------------------------------------------------------------------------

5 12 BOOKS 2011-2-26

6 12 BOOKS 2011-4-30

SQL>

　　 從查詢的結果可以看出，插入的資料已經根據交易時間範圍儲存在不同的分區中。這裡是指定了分區的查詢，當然也可以不指定分區，直接執行select * from dinya_test查詢全部記錄。

　　 在也檢索的資料量很大的時候，指定分區會大大提高檢索速度。

　　 1.2.3. 更新分區表的記錄：

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where

t.transaction_id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>

　　 這裡將第一個分區中的交易ID=1的記錄中的item_description欄位更新為“DESK”，可以看到已經成功更新了一條記錄。但是當更新的時候指定了分區，而根據查詢的記錄不在該分區中時，將不會更新資料，請看下面的例子：

SQL> update dinya_test partition(part_01) t set t.item_description=’DESK’ where

t.transaction_id=6;

0 rows updated.

SQL> commit;

Commit complete.

SQL>

　　 指定了在第一個分區中更新記錄，但是條件中限制交易ID為6，而查詢全表，交易ID為6的記錄在第三個分區中，這樣該條語句將不會更新記錄。

　　 1.2.4. 刪除分區表記錄：

SQL> delete from dinya_test partition(part_02) t where t.transaction_id=4;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

　　 上面例子刪除了第二個分區part_02中的交易記錄ID為4的一條記錄，和更新資料相同，如果指定了分區，而條件中的資料又不在該分區中時，將不會刪除任何資料。

　　1.3. 分區表索引的使用：

　　 分區表和一般表一樣可以建立索引，分區表可以建立局部索引和全域索引。當分區中出現許多事務並且要保證所有分區中的資料記錄的唯一性時採用全域索引。

　　 1.3.1. 局部索引分割區的建立：

SQL> create index dinya_idx_t on dinya_test(item_id)

2 local

3 (

4 partition idx_1 tablespace dinya_space01,

5 partition idx_2 tablespace dinya_space02,

6 partition idx_3 tablespace dinya_space03

7 );

Index created.

SQL>

　　 看查詢的執行計畫，從下面的執行計畫可以看出，系統已經使用了索引：

SQL> select * from dinya_test partition(part_01) t where t.item_id=12;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)

1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF ’DINYA_TEST’ (Cost=

2 Card=1 Bytes=187)

2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1

Card=1)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets

0 physical reads

0 redo size

334 bytes sent via SQL*Net to client

309 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

2 rows processed

SQL>

　　 1.3.2. 全域索引分割區的建立

　　 全域索引建立時global 子句允許指定索引的範圍值，這個範圍值為索引欄位的範圍值：

SQL> create index dinya_idx_t on dinya_test(item_id)

2 global partition by range(item_id)

3 (

4 partition idx_1 values less than (1000) tablespace dinya_space01,

5 partition idx_2 values less than (10000) tablespace dinya_space02,

6 partition idx_3 values less than (maxvalue) tablespace dinya_space03

7 );

Index created.

SQL>

　　 本例中對錶的item_id欄位建立索引分割區，當然也可以不指定索引分割區名直接對整個表建立索引，如：

SQL> create index dinya_idx_t on dinya_test(item_id);

Index created.

SQL>

　　 同樣的，對全域索引根據執行計畫可以看出索引已經可以使用：

SQL> select * from dinya_test t where t.item_id=12;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)

1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF ’DINYA_TEST’ (Cost

=2 Card=3 Bytes=561)

2 1 INDEX (RANGE SCAN) OF ’DINYA_IDX_T’ (NON-UNIQUE) (Cost=1

Card=3)

Statistics

----------------------------------------------------------

5 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

420 bytes sent via SQL*Net to client

309 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

5 rows processed

SQL>

　　1.4. 分區表的維護：

　　 瞭解了分區表的建立、索引的建立、表和索引的使用後，在應用的還要經常對分區進行維護和管理。日常維護和管理的內容包括：增加一個分區，合并一個分區及刪除分區等等。下面以定界分割為例說明增加、合并、刪除分區的一般操作：

　　 1.4.1. 增加一個分區:

SQL> alter table dinya_test

2 add partition part_04 values less than(to_date(’2012-01-01’,’yyyy-mm-dd’))

tablespace dinya_spa

ce03;

Table altered.

SQL>

　　 增加一個分區的時候，增加的分區的條件必須大於現有分區的最大值，否則系統將提示ORA-14074 partition bound must collate higher than that of the last partition 錯誤。

增加分區後，新分區上的索引預設建立在分區的預設資料表空間上，如果希望放在自己定義的資料表空間上，可以用以下命令：

alter index idx_ssn rebuild partition log_3 tablespace log_idx_p3;

 

　　 1.4.2. 合并一個分區：

SQL> alter table dinya_test merge partitions part_01,part_02 into partition part_02;

Table altered.

SQL>

　　 在本例中將原有的表的part_01分區和part_02分區進行了合并，合并後的分區為part_02,如果在合并的時候把合并後的分區定為part_01的時候，系統將提示ORA-14275 cannot reuse lower-bound partition as resulting partition 錯誤。

　　 1.4.3. 刪除分區：

SQL> alter table dinya_test drop partition part_01;

Table altered.

SQL>

　　 刪除分區表的一個分區後，查詢該表的資料時顯示，該分區中的資料已全部丟失，所以執行刪除分區動作時要謹慎，確保先備份資料後再執行，或將分區合并。

  1.4.3. split 分區：

對rest分區進行拆分
alter table zhaozhenlong_partition split partition rest at (to_date('200608','yyyymm')) into (partition p200608, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200609','yyyymm')) into (partition p200609, partition rest);

　　1.5. 總結：

　　 需要說明的是，本文在舉例說名分區表事務操作的時候，都指定了分區，因為指定了分區，系統在執行的時候則只操作該分區的記錄，提高了資料處理的速度。不要指定分區直接操作資料也是可以的。在分區表上建索引及多索引的使用和非分區表一樣。此外，因為在維護分區的時候可能對分區的索引會產生一定的影響，可能需要在維護之後重建索引，相關內容請參考分區表索引部分的文檔