和分割槽表相關的一點總結
使用分割槽表很多時候不一定能提高效能，主要是維護起來方便，如果我們能把訪問的資料集中
在一個或者有限的幾個分割槽裡，那麼效能肯定比訪問普通的全表要好，還是那句話
讓執行的sql儘可能的少讀、少寫，這樣才是提高sql效能的關鍵，就像anlinew提到的公式：
T=S/V,其實少讀、少寫的目的就是減小S...[@more@]
--建立2個結構完全相同，資料量幾乎相同的表，只不過是分割槽表，一個是普通的heap表，
然後簡單的做個訪問時的效能對比，從而更好的理解如何合理的使用分割槽表
SQL> create table t(object_id number,object_name varchar2(30))
2 partition by range(object_id)
3 (
4 partition p1 values less than(2000) tablespace users,
5 partition p2 values less than(4000) tablespace users,
6 partition p3 values less than(6000) tablespace users,
7 partition p4 values less than(8000) tablespace users,
8 partition p5 values less than(maxvalue) tablespace users
9 );
表已建立。
SQL> insert into t select object_id,object_name from dba_objects;
已建立9848行。
SQL> insert into t select * from t;
已建立9848行。
SQL> insert into t select * from t;
已建立19696行。
SQL> insert into t select * from t;
已建立39392行。
SQL> insert into t select * from t;
已建立78784行。
SQL> insert into t select * from t;
已建立157568行。
SQL> insert into t select * from t;
已建立315136行。
SQL> insert into t select * from t;
已建立630272行。
SQL> commit;
提交完成。
SQL> create table t1 tablespace users as select object_id,object_name from dba_o
bjects;
表已建立。
SQL> insert into t1 select *from t1;
已建立9849行。
SQL> insert into t1 select *from t1;
已建立19698行。
SQL> insert into t1 select *from t1;
已建立39396行。
SQL> insert into t1 select *from t1;
已建立78792行。
SQL> insert into t1 select *from t1;
已建立157584行。
SQL> insert into t1 select *from t1;
已建立315168行。
SQL> insert into t1 select *from t1;
已建立630336行。
SQL> commit;
提交完成。
SQL>
--=====================================
1.先來看不垮分割槽訪問時的效能對比，這裡我們主要關注邏輯度(consistent gets),
由於第一次訪問sql存在解析，因此我們對比時都看第二次訪問時的情況，
下面的試驗對比都是這樣...
注意：目前2個表上都沒有index
SQL> select * from t1 where object_id<2000;
已選擇249984行。
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 270K| 20M| 1069 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 270K| 20M| 1069 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
222 recursive calls
0 db block gets
21446 consistent gets
4719 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t1 where object_id<2000;
已選擇249984行。
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 270K| 20M| 1069 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 270K| 20M| 1069 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
21341 consistent gets
0 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t where object_id<2000;
已選擇249984行。
執行計劃
----------------------------------------------------------
Plan hash value: 2931986080
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 241K| 7063K| 238 (19)| 00:00:03 |
| |
| 1 | PARTITION RANGE SINGLE| | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
| 2 | TABLE ACCESS FULL | T | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
--------------------------------------------------------------------------------
---------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
1305 recursive calls
0 db block gets
17770 consistent gets
890 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select * from t where object_id<2000;
已選擇249984行。
執行計劃
----------------------------------------------------------
Plan hash value: 2931986080
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 241K| 7063K| 238 (19)| 00:00:03 |
| |
| 1 | PARTITION RANGE SINGLE| | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
| 2 | TABLE ACCESS FULL | T | 241K| 7063K| 238 (19)| 00:00:03 |
1 | 1 |
--------------------------------------------------------------------------------
---------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
17498 consistent gets
0 physical reads
0 redo size
7439711 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL>
--========================
效果比較明顯，很顯然是透過訪問分割槽表的效能更好一些，邏輯讀21341大於17498，並且都沒有發生物理讀，
同時我們從執行計劃也能看到訪問t1時訪問的是單個分割槽(PARTITION RANGE SINGLE),很明顯如果我們能把資料
控制在一個分割槽之內的話那麼分割槽表的效能要好於普通的heap表
--=============================
2.跨分割槽訪問,依然是分割槽表的效能好，因為資料集中在了2個分割槽中，還是比訪問
t1要少訪問資料...
SQL> select * from t1 where object_id<4000;
已選擇504448行。
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 547K| 41M| 1070 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 547K| 41M| 1070 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<4000)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
5 recursive calls
0 db block gets
38318 consistent gets
0 physical reads
0 redo size
15092711 bytes sent via SQL*Net to client
370304 bytes received via SQL*Net from client
33631 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
504448 rows processed
SQL> set timing on
SQL> select * from t1 where object_id<4000;
已選擇504448行。
已用時間: 00: 00: 09.79
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 547K| 41M| 1070 (4)| 00:00:13 |
|* 1 | TABLE ACCESS FULL| T1 | 547K| 41M| 1070 (4)| 00:00:13 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<4000)
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
38236 consistent gets
0 physical reads
0 redo size
15092711 bytes sent via SQL*Net to client
370304 bytes received via SQL*Net from client
33631 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
504448 rows processed
SQL> select * from t where object_id<4000;
已選擇504448行。
已用時間: 00: 00: 09.85
執行計劃
----------------------------------------------------------
Plan hash value: 1571388083
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 459K| 13M| 837 (53)| 00:00:11
| | |
| 1 | PARTITION RANGE ITERATOR| | 459K| 13M| 837 (53)| 00:00:11
| 1 | 2 |
| 2 | TABLE ACCESS FULL | T | 459K| 13M| 837 (53)| 00:00:11
| 1 | 2 |
--------------------------------------------------------------------------------
-----------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
69 recursive calls
1 db block gets
36078 consistent gets
946 physical reads
48432 redo size
15092711 bytes sent via SQL*Net to client
370304 bytes received via SQL*Net from client
33631 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
504448 rows processed
SQL> select * from t where object_id<4000;
已選擇504448行。
已用時間: 00: 00: 10.12
執行計劃
----------------------------------------------------------
Plan hash value: 1571388083
--------------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------
| 0 | SELECT STATEMENT | | 459K| 13M| 837 (53)| 00:00:11
| | |
| 1 | PARTITION RANGE ITERATOR| | 459K| 13M| 837 (53)| 00:00:11
| 1 | 2 |
| 2 | TABLE ACCESS FULL | T | 459K| 13M| 837 (53)| 00:00:11
| 1 | 2 |
--------------------------------------------------------------------------------
-----------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
35328 consistent gets
0 physical reads
0 redo size
15092711 bytes sent via SQL*Net to client
370304 bytes received via SQL*Net from client
33631 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
504448 rows processed
SQL>
--======================
3.訪問全部資料,我們發現訪問heap表要比訪問分割槽表是少了一些邏輯讀：
88429<88573,其實這點差別對效能來說是無關緊要的，重要的是說明了一個問題，
那就是儘量讓執行的sql少讀、少些，這也是sql調整的最終目的，相差的這100
多邏輯讀主要發生在查詢metadata上...
SQL> select * from t1 ;
已選擇1260672行。
已用時間: 00: 00: 26.70
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1482K| 111M| 1064 (3)| 00:00:13 |
| 1 | TABLE ACCESS FULL| T1 | 1482K| 111M| 1064 (3)| 00:00:13 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
5 recursive calls
0 db block gets
88511 consistent gets
0 physical reads
0 redo size
44940044 bytes sent via SQL*Net to client
924869 bytes received via SQL*Net from client
84046 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1260672 rows processed
SQL> select * from t1 ;
已選擇1260672行。
已用時間: 00: 00: 24.59
執行計劃
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1482K| 111M| 1064 (3)| 00:00:13 |
| 1 | TABLE ACCESS FULL| T1 | 1482K| 111M| 1064 (3)| 00:00:13 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
88429 consistent gets
0 physical reads
0 redo size
44940044 bytes sent via SQL*Net to client
924869 bytes received via SQL*Net from client
84046 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1260672 rows processed
SQL> select * from t ;
已選擇1260544行。
已用時間: 00: 00: 25.71
執行計劃
----------------------------------------------------------
Plan hash value: 3557914527
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pst
art| Pstop |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1317K| 37M| 1094 (3)| 00:00:14 |
| |
| 1 | PARTITION RANGE ALL| | 1317K| 37M| 1094 (3)| 00:00:14 |
1 | 5 |
| 2 | TABLE ACCESS FULL | T | 1317K| 37M| 1094 (3)| 00:00:14 |
1 | 5 |
--------------------------------------------------------------------------------
------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
7 recursive calls
2 db block gets
90275 consistent gets
2133 physical reads
114820 redo size
39895221 bytes sent via SQL*Net to client
924781 bytes received via SQL*Net from client
84038 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1260544 rows processed
SQL> select * from t ;
已選擇1260544行。
已用時間: 00: 00: 24.90
執行計劃
----------------------------------------------------------
Plan hash value: 3557914527
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pst
art| Pstop |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1317K| 37M| 1094 (3)| 00:00:14 |
| |
| 1 | PARTITION RANGE ALL| | 1317K| 37M| 1094 (3)| 00:00:14 |
1 | 5 |
| 2 | TABLE ACCESS FULL | T | 1317K| 37M| 1094 (3)| 00:00:14 |
1 | 5 |
--------------------------------------------------------------------------------
------------
Note
-----
- dynamic sampling used for this statement
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
88573 consistent gets
0 physical reads
0 redo size
39895221 bytes sent via SQL*Net to client
924781 bytes received via SQL*Net from client
84038 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1260544 rows processed
SQL>
--==========================
有index的情況,建立global index：
有index其實也是一樣的，首先透過index找到rowid，之後最終透過rowid
再去訪問表，如果訪問的資料儘可能的可以集中在一些分割槽內，那麼訪問
分割槽表的效能肯定要優於heap表
SQL> create index idx_t on t(object_id) tablespace users;
索引已建立。
已用時間: 00: 00: 10.21
SQL> create index idx_t1 on t1(object_id) tablespace users;
索引已建立。
已用時間: 00: 00: 06.89
SQL>
SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 06.00
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 03.21
SQL>
--有global index跨分割槽訪問
SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 07.75
執行計劃
----------------------------------------------------------
Plan hash value: 3317799281
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 368K| 7922K| 363K (1)
| 01:12:41 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 368K| 7922K| 363K (1)
| 01:12:41 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_T | 368K| | 986 (1)
| 00:00:12 | | |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
398707 consistent gets
0 physical reads
0 redo size
12757072 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 09.43
執行計劃
----------------------------------------------------------
Plan hash value: 3317799281
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 368K| 7922K| 363K (1)
| 01:12:41 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 368K| 7922K| 363K (1)
| 01:12:41 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_T | 368K| | 986 (1)
| 00:00:12 | | |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
398707 consistent gets
0 physical reads
0 redo size
12757072 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL>
--======================
--有global index不垮分割槽訪問：
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 05.57
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 245K| 5264K| 245K (1)| 00:4
9:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 245K| 5264K| 245K (1)| 00:4
9:12 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 245K| | 522 (2)| 00:0
0:07 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
266985 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 05.42
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 245K| 5264K| 245K (1)| 00:4
9:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 245K| 5264K| 245K (1)| 00:4
9:12 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 245K| | 522 (2)| 00:0
0:07 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
266985 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 04.93
執行計劃
----------------------------------------------------------
Plan hash value: 2063514567
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 259K| 5073K| 1241K (1)
| 04:08:20 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 259K| 5073K| 1241K (1)
| 04:08:20 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1260K| | 3366 (1)
| 00:00:41 | | |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
44 recursive calls
0 db block gets
262733 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL>
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 04.62
執行計劃
----------------------------------------------------------
Plan hash value: 2063514567
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 259K| 5073K| 1241K (1)
| 04:08:20 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| T | 259K| 5073K| 1241K (1)
| 04:08:20 | 1 | 1 |
|* 2 | INDEX RANGE SCAN | IDX_T | 1260K| | 3366 (1)
| 00:00:41 | | |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
262727 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL>
--======================
SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 05.82
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
已用時間: 00: 00: 03.17
SQL>
--local index:
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 09.46
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 370K| 7963K| 371K (1)| 01:1
4:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 370K| 7963K| 371K (1)| 01:1
4:16 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 370K| | 786 (2)| 00:0
0:10 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
150 recursive calls
0 db block gets
403726 consistent gets
0 physical reads
0 redo size
12757072 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 08.28
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 370K| 7963K| 371K (1)| 01:1
4:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 370K| 7963K| 371K (1)| 01:1
4:16 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 370K| | 786 (2)| 00:0
0:10 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
403706 consistent gets
0 physical reads
0 redo size
12757072 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 07.17
執行計劃
----------------------------------------------------------
Plan hash value: 3038871768
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 368K| 7566K| 363K (1)
| 01:12:40 | | |
| 1 | PARTITION RANGE ITERATOR | | 368K| 7566K| 363K (1)
| 01:12:40 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 368K| 7566K| 363K (1)
| 01:12:40 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | IDX_T | 368K| | 783 (2)
| 00:00:10 | 1 | 2 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
195 recursive calls
0 db block gets
398680 consistent gets
0 physical reads
0 redo size
5145251 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;
已選擇377984行。
已用時間: 00: 00: 07.42
執行計劃
----------------------------------------------------------
Plan hash value: 3038871768
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 368K| 7566K| 363K (1)
| 01:12:40 | | |
| 1 | PARTITION RANGE ITERATOR | | 368K| 7566K| 363K (1)
| 01:12:40 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 368K| 7566K| 363K (1)
| 01:12:40 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | IDX_T | 368K| | 783 (2)
| 00:00:10 | 1 | 2 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<3000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
398633 consistent gets
0 physical reads
0 redo size
5145251 bytes sent via SQL*Net to client
277563 bytes received via SQL*Net from client
25200 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
377984 rows processed
SQL>
--===============================
有local index不垮分割槽訪問：
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 04.93
執行計劃
----------------------------------------------------------
Plan hash value: 3965956311
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 254K| 4962K| 245K (1)
| 00:49:11 | | |
| 1 | PARTITION RANGE SINGLE | | 254K| 4962K| 245K (1)
| 00:49:11 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 254K| 4962K| 245K (1)
| 00:49:11 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IDX_T | 249K| | 528 (2)
| 00:00:07 | 1 | 1 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
24 recursive calls
0 db block gets
262679 consistent gets
0 physical reads
0 redo size
3406055 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 03.48
執行計劃
----------------------------------------------------------
Plan hash value: 3965956311
--------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT | | 254K| 4962K| 245K (1)
| 00:49:11 | | |
| 1 | PARTITION RANGE SINGLE | | 254K| 4962K| 245K (1)
| 00:49:11 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T | 254K| 4962K| 245K (1)
| 00:49:11 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IDX_T | 249K| | 528 (2)
| 00:00:07 | 1 | 1 |
--------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
262676 consistent gets
0 physical reads
0 redo size
3406055 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 05.40
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 247K| 5306K| 247K (1)| 00:4
9:29 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 247K| 5306K| 247K (1)| 00:4
9:29 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 247K| | 525 (2)| 00:0
0:07 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
266985 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;
已選擇249984行。
已用時間: 00: 00: 05.70
執行計劃
----------------------------------------------------------
Plan hash value: 50753647
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 247K| 5306K| 247K (1)| 00:4
9:29 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 247K| 5306K| 247K (1)| 00:4
9:29 |
|* 2 | INDEX RANGE SCAN | IDX_T1 | 247K| | 525 (2)| 00:0
0:07 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<2000)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
266985 consistent gets
0 physical reads
0 redo size
8439590 bytes sent via SQL*Net to client
183700 bytes received via SQL*Net from client
16667 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
249984 rows processed
SQL>
--=========================
SQL> create index idx_t_name on t(object_name) tablespace users;
索引已建立。
SQL>
--=========================
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';
INDEX_NAME STATUS
------------------------------ --------
IDX_T_NAME VALID
SQL> alter table t truncate partition p4;
表被截斷。
--在分割槽表上建立的global index(注意非分割槽)，即使我們truncate partition
其實這個global index是不會自動維護的。
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';
INDEX_NAME STATUS
------------------------------ --------
IDX_T_NAME UNUSABLE
SQL> COL OBJECT_NAME FORMAT A20
SQL> select object_name,status from dba_objects where object_name='IDX_T_NAME';
OBJECT_NAME STATUS
-------------------- -------
IDX_T_NAME VALID
SQL>
SQL> select bytes/1024/1024 m from dba_segments where segment_name='IDX_T_NAME';
M
----------
47
SQL> alter index idx_t_name rebuild;
索引已更改。
SQL> select bytes/1024/1024 m from dba_segments where segment_name='IDX_T_NAME';
M
----------
37
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';
INDEX_NAME STATUS
------------------------------ --------
IDX_T_NAME VALID
SQL>
--==========================
驗證一下dml對global index的影響,dml操作oracle會自動update global index(注意:並非global partition index)，這個
比較好理解，也應該這樣做...
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_NAME 256 10112
SQL>
SQL> delete from t where object_id=5002;
已刪除128行。
SQL> commit;
提交完成。
SQL> analyze index idx_t_name validate structure;
索引已分析
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_NAME 384 15616
SQL>
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';
INDEX_NAME STATUS
------------------------------ --------
IDX_T_NAME VALID
--=========================
驗證在分割槽表上建立主鍵和唯一索引的情況：
SQL> truncate table t ;
表被截斷。
SQL> alter table t add pk number;
表已更改。
SQL> insert into t(pk,object_id,object_name) select rownum,object_id,object_name
from t1;
已建立1260672行。
SQL> commit;
提交完成。
SQL> alter table t add constraint pk_t primary key (pk);
表已更改。
SQL> alter table t drop constraint pk_t;
表已更改。
SQL> create unique index idx_t_pk on t(pk) tablespace users;
索引已建立。
SQL> create unique index idx_t_pk on t(pk) local tablespace users;
create unique index idx_t_pk on t(pk) local tablespace users
*
第 1 行出現錯誤:
ORA-14039: 分割槽列必須構成 UNIQUE 索引的關鍵字列子集
SQL> create unique index idx_t_pk on t(pk,object_id) local tablespace users;
索引已建立。
SQL> drop index idx_t_pk;
索引已刪除。
SQL> alter table t add constraint pk_t primary key (pk) using index local;
alter table t add constraint pk_t primary key (pk) using index local
*
第 1 行出現錯誤:
ORA-14039: 分割槽列必須構成 UNIQUE 索引的關鍵字列子集
SQL> alter table t add constraint pk_t primary key (pk,object_id) using index l
ocal;
表已更改。
SQL> alter table t drop constraint pk_t;
表已更改。
SQL> create index idx_t_pk on t(pk) local tablespace users;
索引已建立。
SQL> drop index idx_t_pk;
索引已刪除。
SQL>
結論:不管我們在建立unique index還是新增primary key時，只要我們
想把index建立成local分割槽index，那麼就應該至少包含分割槽列...而不是象
網上有些人說的在分割槽表上建立primary key或者nuique index時必須包含分割槽列。
--=======================
ddl對local partition index的影響,oracle會自動維護ddl對local partition index的影響：
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
SQL> alter table t truncate partition p4;
表被截斷。
SQL> analyze index idx_t validate structure;
索引已分析
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
--看來透過analyze index idx_t validate structure;沒法驗證ddl對local partition index的維護，因為
在truncate partition前後index_stats裡的資料沒有變化
SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IDX_T P5
IDX_T P4
IDX_T P3
IDX_T P2
IDX_T P1
SQL> alter table t drop partition p4;
表已更改。
SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
IDX_T P5
IDX_T P3
IDX_T P2
IDX_T P1
--drop partition之後和該分割槽相關的local partition index也被drop了...
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T P1 2010/11/02 12:55:38
IDX_T P2 2010/11/02 12:55:38
IDX_T P3 2010/11/02 12:55:38
IDX_T P5 2010/11/02 12:55:38
IDX_T 2010/11/02 13:42:46
SQL> alter table t truncate partition p3;
表被截斷。
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T P1 2010/11/02 12:55:38
IDX_T P2 2010/11/02 12:55:38
IDX_T P3 2010/11/02 13:50:29
IDX_T P5 2010/11/02 12:55:38
IDX_T 2010/11/02 13:50:29
--透過ddl裡的時間我們可以清楚的發現在truncate partition時oracle維護了
其對應的local index，因為p3對應的ddl時間由原來的12:55:38變成了13:50:29
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T P1 5
IDX_T P2 5
IDX_T P3 .0625
IDX_T P5 6
SQL> alter table t truncate partition p2;
表被截斷。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T P1 5
IDX_T P2 .0625
IDX_T P3 .0625
IDX_T P5 6
SQL>
--當然透過local index的大小我們也可以清楚的觀查到，local partition index
p2的大小在truncate p2之前是5m，之後變成了0.625m，可見oracle維護了local index
--======================
dml對global partition index的影響：
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T 0 0
SQL> delete from t where object_id=100;
已刪除128行。
SQL> commit;
提交完成。
SQL> analyze index idx_t_g validate structure;
索引已分析
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;
NAME DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_G 127 2565
SQL>
--很顯然oracle會自動維護dml對global partition index的維護，這個其實
不用驗證也沒有問題，如果不能自動維護dml，那麼怎麼使用這種型別的index
--=========================
dll對global partition index的影響：
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G GP1 2010/11/02 14:17:49
IDX_T_G GP2 2010/11/02 14:17:49
IDX_T_G GP3 2010/11/02 14:17:49
IDX_T_G GP4 2010/11/02 14:17:49
IDX_T_G GP5 2010/11/02 14:17:49
IDX_T_G GP6 2010/11/02 14:17:49
IDX_T_G GP_MAX 2010/11/02 14:17:49
IDX_T_G 2010/11/02 14:17:49
已選擇8行。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T_G GP1 .0625
IDX_T_G GP2 .0625
IDX_T_G GP3 .0625
IDX_T_G GP4 .0625
IDX_T_G GP5 .0625
IDX_T_G GP6 .0625
IDX_T_G GP_MAX 12
已選擇7行。
SQL> alter table t truncate partition p5;
表被截斷。
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';
OBJECT_NAM SUBOBJECT_NAME LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G GP1 2010/11/02 14:17:49
IDX_T_G GP2 2010/11/02 14:17:49
IDX_T_G GP3 2010/11/02 14:17:49
IDX_T_G GP4 2010/11/02 14:17:49
IDX_T_G GP5 2010/11/02 14:17:49
IDX_T_G GP6 2010/11/02 14:17:49
IDX_T_G GP_MAX 2010/11/02 14:17:49
IDX_T_G 2010/11/02 14:17:49
已選擇8行。
SQL> select segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';
SEGMENT_NAME PARTITION_NAME M
-------------------- ------------------------------ ----------
IDX_T_G GP1 .0625
IDX_T_G GP2 .0625
IDX_T_G GP3 .0625
IDX_T_G GP4 .0625
IDX_T_G GP5 .0625
IDX_T_G GP6 .0625
IDX_T_G GP_MAX 12
已選擇7行。
SQL>
--global index partition GP_MAX記錄的資料>9000,而表分割槽p5的資料>8000,
在truncate p5之後對比GP_MAX前後的ddl修改時間和段大小，發現都沒有任何變化,
我就認為ddl對global index partition不自動維護吧，實在沒有太好的驗證辦法，當然
可以dump index的結構來看，感興趣的自己測試一下吧。
其實從下面查詢index的狀態就可以看出來，此時的index狀態全部變成了UNUSABLE：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 UNUSABLE
IDX_T_G GP2 UNUSABLE
IDX_T_G GP3 UNUSABLE
IDX_T_G GP4 UNUSABLE
IDX_T_G GP5 UNUSABLE
IDX_T_G GP6 UNUSABLE
IDX_T_G GP_MAX UNUSABLE
已選擇7行。
SQL>
嘗試重建這個global partition index ：
SQL> alter index idx_t_g rebuild;
alter index idx_t_g rebuild
*
第 1 行出現錯誤:
ORA-14086: 不能將分割槽索引作為整體重建
SQL>
--看來只能一個一個重建了：
SQL> alter index idx_t_g rebuild partition gp1 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp2 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp3 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp4 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp5 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp6 ;
索引已更改。
SQL> alter index idx_t_g rebuild partition gp_max ;
索引已更改。
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已選擇7行。
SQL>
--重建之後狀態變成了USABLE，注意只要我們truncate一個分割槽，那麼global
partition index的分割槽index的狀態全部變成UNUSABLE
如何自動維護global partition index：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已選擇7行。
SQL> alter table t truncate partition p1 update global indexes;
表被截斷。
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 USABLE
IDX_T_G GP2 USABLE
IDX_T_G GP3 USABLE
IDX_T_G GP4 USABLE
IDX_T_G GP5 USABLE
IDX_T_G GP6 USABLE
IDX_T_G GP_MAX USABLE
已選擇7行。
SQL>
顯然在ddl語句truncate後面加上update global indexes全域性分割槽index的狀態都變成了USABLE
，很顯然oracle自動為了index，不過如果分割槽表和分割槽index比較大的話，那麼update global indexes
的處理時間可能會很長，透過鎖定表的時間也會很長，所以使用update global indexes子句要慎重。
如果全域性分割槽index的狀態原來就是UNUSABLE，那麼即使加上update global indexes子句，oracle也不會
自動維護index：
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
IDX_T_G GP1 UNUSABLE
IDX_T_G GP2 UNUSABLE
IDX_T_G GP3 UNUSABLE
IDX_T_G
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1040768/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 轉：Mysql 分割槽 分表相關總結MySql
- SqlServer關於分割槽表的總結SQLServer
- 《RHEL6硬碟的分割槽和swap分割槽管理》——硬碟分割槽的大總結硬碟
- Hive中靜態分割槽和動態分割槽總結Hive
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 關於分割槽表Local索引Rebuild的一些總結索引Rebuild
- 分割槽表總結
- 表分割槽總結
- 分割槽表 總結
- 關於修改分割槽表的問題總結
- Oracle分割槽之五：建立分割槽索引總結Oracle索引
- MySQL分割槽表的分割槽原理和優缺點MySql
- oracle 分割槽表總結Oracle
- Oracle 分割槽表 總結Oracle
- 埋點表相關
- MySQL 分割槽介紹總結MySql
- oracle分割槽表總結(轉)Oracle
- 關於oracle的表空間，分割槽表，以及索引的總結Oracle索引
- Oracle Partition 分割槽詳細總結Oracle
- 關於分割槽表和分割槽索引(About Partitioned Tables and Indexes)索引Index
- 關於oracle的表空間，分割槽表，以及索引的總結(轉)Oracle索引
- 關於oracle的表空間，分割槽表，以及索引的總結 -- 轉Oracle索引
- 轉個分割槽表Local索引Rebuild的總結索引Rebuild
- 關於ORACLE的一點總結Oracle
- 有關role的一點總結!
- 關於 Oracle 分割槽索引的失效和重建Oracle索引
- Linux Swap交換分割槽介紹總結Linux
- 【學習筆記】分割槽表和分割槽索引——概念部分（一）筆記索引
- oracle分割槽表和分割槽表exchangeOracle
- oracle的表空間、分割槽表、以及索引的總結Oracle索引
- MySQL的分割槽（一）MySql
- 關於 Oracle 分割槽索引的建立和維護Oracle索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- 和外來鍵相關的一點效能問題總結！
- oracle分割槽表和非分割槽表exchangeOracle
- 關於分割槽表的操作
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL