建立檢視和淺析LOCAL INDEX和GLOBAL INDEX
1. 首先了解一下local index 和 global index的建立過程:
SQL> create table kl911_1 (no number, name varchar2(60))
2 partition by range (no)
3 (partition p1 values less than (10) pctfree 50,
4 partition p2 values less than (20) pctfree 50,
5 partition max_values values less than (maxvalue));
Table created.
SQL> create index idx_kl911_1 on kl911_1(no) local;
Index created.
SQL> select index_name from dba_ind_partitions where index_name='IDX_KL911_1';
INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_1
IDX_KL911_1
IDX_KL911_1
SQL> select index_name, locality from dba_part_indexes where index_name='IDX_KL911_1';
INDEX_NAME
--------------------------------------------------------------------------------
LOCALITY
------------------
IDX_KL911_1
LOCAL
----- 如果什麼都不加,預設是Global index
SQL> create index idx_kl911_2 on kl911_1(name);
Index created.
SQL> select index_name from dba_ind_partitions where index_name='IDX_KL911_2';
no rows selected
SQL> select index_name, locality from dba_part_indexes where index_name='IDX_KL911_2';
no rows selected
SQL> select index_name from dba_indexes where index_name='IDX_KL911_2';
INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_2
2. 接下來我想驗證一下為什麼對於分割槽表一定要用local index的benefit,聽說global index在每次交換分割槽以後需要重建,否則會出現錯誤提示:
SQL> select index_name from dba_indexes where table_name='KL911_1';
INDEX_NAME
--------------------------------------------------------------------------------
IDX_KL911_1
IDX_KL911_2
SQL> create table kl_temp (no number, name varchar2(60));
Table created.
SQL> insert into kl_temp values (15,'TOM');
1 row created.
SQL> insert into kl_temp values (14,'JON');
1 row created.
SQL> commit;
SQL> alter table kl911_1 exchange partition p2 with table kl_temp;
Table altered.
SQL> select * from kl911_1 partition (p2);
NO
----------
NAME
--------------------------------------------------------------------------------
15
TOM
14
JON
SQL> select * from kl911_1 where name='TOM';
NO
----------
NAME
-----------------------------------------------------------
15
TOM
發現並沒報錯,我用的是ORACLE 10.2.0.2的版本。呵呵,並沒有報錯。看看執行計劃,也許根本沒走索引:
SQL> select * from kl911_1 a where a.no=22;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 45 | 3 (0)| 00:00:01 | 3 | 3 |
|* 2 | TABLE ACCESS FULL | KL911_1 | 1 | 45 | 3 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------------------------
看起來並沒有走索引,因為資料量比較小,CBO選擇了一種更高效的方法:全表掃描
***使用hint試一下:
--- 謂詞是local index,沒有報錯:
SQL> select /*+ index(a idx_kl911_1) */ * from kl911_1 a where a.no=22;
no rows selected
--- 謂詞是global index, 報錯如下:
SQL> select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM';
select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM'
*
ERROR at line 1:
ORA-01502: index 'KL.IDX_KL911_2' or partition of such index is in unusable state
3. 然後看看如果都是正常狀態,LOCAL index的優勢在哪裡呢?
SQL> alter index idx_kl911_1 rebuild partition p1;
Index altered.
SQL> alter index idx_kl911_1 rebuild partition p2;
Index altered.
SQL> alter index idx_kl911_1 rebuild partition max_values;
Index altered.
SQL> select /*+ index(a idx_kl911_1) */ * from kl911_1 a where a.no=22;
Execution Plan
----------------------------------------------------------
Plan hash value: 1669532652
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 45 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 45 | 1 (0)| 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| KL911_1 | 1 | 45 | 1 (0)| 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_KL911_1 | 1 | | 1 (0)| 00:00:01 | 3 | 3 |
------------------------------------------------------------------------------------------------------------------
注意: PARTITION RANGE SINGLE,使用LOCAL INDEX是透過分割槽範圍的來走索引的,減少了結果集。
而接下來看GLOBAL INDEX,則無視分割槽表的特點,完完全全的按照普通索引範圍掃描來定義執行計劃,如下:
SQL> alter index idx_kl911_2 rebuild;
Index altered.
SQL> select /*+ index(a idx_kl911_2) */ * from kl911_1 a where name='TOM';
Execution Plan
----------------------------------------------------------
Plan hash value: 4155448299
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 90 | 3 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| KL911_1 | 2 | 90 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_KL911_2 | 2 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
4. 結論
4.1 我們可以透過local選項為分割槽表建立local index,並透過dba_part_indexes.locality來檢視其屬性,或者透過dba_ind_partitions來檢視索引分割槽的內容.
4.2 如果執行計劃選擇了走索引,那麼在每次交換分割槽以後,global index是不可用的,必須重建。
4.3 如果執行計劃選擇了走索引,local index會以PARTITION RANGE SINGLE的方式,進行索引範圍掃描,而Global則是單純的範圍掃描。
---- 還有不完整和不正確的希望大家補充糾正! (Jeff)
[@more@]來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/661919/viewspace-1006726/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- attempt to index local ‘result‘ (a nil value)Index
- 淺析oracle b-tree index搜尋原理OracleIndex
- [20190920]Asynchronous Global Index Maintenance and Recycle Bin.txtIndexAINaN
- INDEX REBUILD和INDEX REORGANIZE和UPDATE STATISTICS是否涉及Sch-M的案例分析IndexRebuild
- Git檢視暫存區index檔案內容GitIndex
- PostgreSQL如何檢視page、index的詳細資訊SQLIndex
- mysql建立索引和檢視MySql索引
- ES 筆記十四:Index Template 和 Dynamic Template筆記Index
- 淺析前端框架如何更新檢視前端框架
- Elasticsearch 6 建立索引報錯 invalid_index_name_exception Invalid index name [testDemo], must be lowercaseElasticsearch索引IndexException
- 深入淺出VACUUM核心原理(中): index by passIndex
- C# 使用 Index 和 Range 簡化集合操作C#Index
- INDEX建立方式對SQL的影響IndexSQL
- Index of /virtualboxIndex
- PostgreSQL:INDEXSQLIndex
- 淺析IOC 和 DI
- 淺析IPTV電視和有線電視有哪些不同
- oracle invisible index與unusable index的區別OracleIndex
- Python, pandas: how to sort dataframe by index// Merge two dataframes by indexPythonIndex
- 淺析 Flutter 與 iOS 的檢視橋樑FlutteriOS
- 淺析Convert,Parse和TryParse
- index.jspIndexJS
- null與indexNullIndex
- create index .. onlineIndex
- ES 筆記二十四:使用 Search Template 和 Index Alias筆記Index
- Z-index 層疊上下文和層疊水平Index
- 小程式報錯Invoke event bindViewTap in page: pages/index/indexViewIndex
- Artificial Intelligence Index:2018年AI Index報告出爐IntelIndexAI
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- 淺析Spring的IoC和DISpring
- 一文梳理z-index和層疊上下文Index
- Android APIs (Package Index)AndroidAPIPackageIndex
- z-index:autoIndex
- flag在index裡Index
- css z-indexCSSIndex
- enable_index_filterIndexFilter
- for while改變indexWhileIndex
- Index of /debian-cd/Index