【每日一摩斯】-Index Skip Scan Feature (212391.1)
INDEX Skip Scan,也就是索引快速掃描,一般是指謂詞中不帶複合索引第一列,但掃描索引塊要快於掃描表的資料塊,此時CBO會選擇INDEX SS的方式。
官方講的,這個概念也好理解,如果將複合索引看做是一個分割槽表,其中分割槽主鍵(這裡指的是複合索引的首列)定義了儲存於此的分割槽資料。在每個鍵(首列)下的每行資料都將按照此鍵排序。因此在SS,首列可以被跳過,非首列可以作為邏輯子索引訪問。因此一個“正常”的索引訪問可以忽略首列。
複合索引被邏輯地切分成更小的子索引。邏輯子索引的個數取決於初始列的cardinality。因此儘管首列未出現在謂詞中,也可能使用這個索引。、
另外,需要吧補充一點:當複合索引的第一個欄位的值重複率非常低時,掃描索引的效率會比全表掃描更高,這是CBO才可能會選擇使用INDEX Skip Scan的方式訪問資料。
這裡比較奇怪的是:
使用9i時,未使用INDEX Skip Scan:
SQL> create table at2(a varchar2(3),b varchar2(10),c varchar2(5));
Table created.
SQL> begin
2 for i in 1..1000
3 loop
4 insert into at2 values('M', i, 'M');
5 insert into at2 values('F', i, 'F');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> create index at2_i on at2(a,b,c);
Index created.
SQL> exec dbms_stats.gather_table_stats(OWNNAME => NULL, TABNAME => 'at2', CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly
SQL> select * from at2 where b='352';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=2 Bytes=14)
1 0 INDEX (FAST FULL SCAN) OF 'AT2_I' (NON-UNIQUE) (Cost=2 Car
d=2 Bytes=14)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
447 bytes sent via SQL*Net to client
587 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
使用10g的,則使用了INDEX Skip Scan:
SQL> select * from full_tbl where object_name='TEST';
Execution Plan
----------------------------------------------------------
Plan hash value: 1293869270
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 55 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| FULL_TBL | 2 | 58 | 55 (2)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='TEST')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
230 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
難道9i和10g在選擇INDEX Skip Scan還有什麼不同麼?
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/7192724/viewspace-772343/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- INDEX SKIP SCANIndex
- 理解index skip scanIndex
- [轉貼]Skip Scan IndexIndex
- 關於INDEX SKIP SCANIndex
- index range scan,index fast full scan,index skip scan發生的條件IndexAST
- 索引優化index skip scan索引優化Index
- index skip scan的一些實驗。Index
- INDEX SKIP SCAN適用場景Index
- oracle hint_skip scan_index_ssOracleIndex
- 高效的SQL(index skip scan使用條件)SQLIndex
- 跳躍式索引(Skip Scan Index)的淺析索引Index
- 跳躍式索引(Skip Scan Index)淺析 - 轉索引Index
- 跳躍式索引掃描(index skip scan) [final]索引Index
- INDEX UNIQUE SCAN,INDEX FULL SCAN和INDEX FAST FULL SCANIndexAST
- 【TUNE_ORACLE】列出走了INDEX SKIP SCAN的SQL參考OracleIndexSQL
- [20180725]index skip-scan operation.txtIndex
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- Index Full Scan vs Index Fast Full ScanIndexAST
- Index Full Scan 與 Index Fast Full ScanIndexAST
- Index的掃描方式:index full scan/index fast full scanIndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN區別IndexAST
- index full scan 和 index FAST full scan 區別IndexAST
- Index Full Scan 與 Index Fast Full Scan (Final)IndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN的區別IndexAST
- Clustered Index Scan and Clustered Index SeekIndex
- [20181201]奇怪的INDEX SKIP SCAN執行計劃.txtIndex
- 【INDEX_SS】使用HINT使SQL用索引跳躍掃描(Index Skip Scan)方式快速獲取資料IndexSQL索引
- skip_unusable_index parameterIndex
- index full scan 和 index fast full scan (IFS,FFS)的不同IndexAST
- Index Unique Scan (213)Index
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- Index Full Scan和Index Fast Full Scan行為差異分析(上)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(下)IndexAST
- Index Range Scan (214)Index
- 【SQL 提示 之二】index_ss Index Skip HintSQLIndex
- 關於Oracle 9i 跳躍式索引掃描(Index Skip Scan)的小測試 (轉)Oracle索引Index
- 索引唯一性掃描(INDEX UNIQUE SCAN)索引Index
- index fast full scan 和 nullIndexASTNull