index fast full scan 和 null
當索引中的列和 需要取出資料列 一致時,並一定會走 index fast full scan.
需要至少有一個列具有非空約束的條件。
理由很簡單: B-tree index不記錄null 值
除非是bitmap索引,但是bitmap索引有一個BITMAP CONVERSION TO ROWIDS,cost較高
SQL> drop table t_test purge;
Table dropped.
SQL> create table t_test as selct * from dba_objects;
create table t_test as selct * from dba_objects
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> create table t_test as select * from dba_objects;
Table created.
SQL> create index idx_t_test on t_test (object_id,object_name);
Index created.
SQL> set autot trace
SQL> set line 200
SQL> select object_id,object_name from t_test;
74761 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2796558804
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82067 | 6331K| 298 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T_TEST | 82067 | 6331K| 298 (1)| 00:00:04 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
6066 consistent gets
1283 physical reads
0 redo size
3196705 bytes sent via SQL*Net to client
55347 bytes received via SQL*Net from client
4986 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
74761 rows processed
SQL> alter table t_test modify (object_id not null);
Table altered.
SQL> select object_id,object_name from t_test;
74761 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 604450970
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82067 | 6331K| 122 (1)| 00:00:02 |
| 1 | INDEX FAST FULL SCAN| IDX_T_TEST | 82067 | 6331K| 122 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
34 recursive calls
0 db block gets
5497 consistent gets
546 physical reads
0 redo size
3196705 bytes sent via SQL*Net to client
55347 bytes received via SQL*Net from client
4986 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
74761 rows processed
SQL>
SQL> drop index idx_t_test;
Index dropped.
SQL> create bitmap index idx_t_test on t_test (object_id,object_name);
Index created.
SQL> select object_id,object_name from t_test;
74761 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2796558804
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82067 | 6331K| 298 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T_TEST | 82067 | 6331K| 298 (1)| 00:00:04 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
6069 consistent gets
1065 physical reads
0 redo size
3196705 bytes sent via SQL*Net to client
55347 bytes received via SQL*Net from client
4986 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
74761 rows processed
SQL> /
74761 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2796558804
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82067 | 6331K| 298 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T_TEST | 82067 | 6331K| 298 (1)| 00:00:04 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5990 consistent gets
1065 physical reads
0 redo size
3196705 bytes sent via SQL*Net to client
55347 bytes received via SQL*Net from client
4986 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
74761 rows processed
SQL> select /*+ index (t_test)*/ object_id,object_name from t_test;
74761 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3390314349
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82067 | 6331K| 537 (1)| 00:00:07 |
| 1 | BITMAP CONVERSION TO ROWIDS| | 82067 | 6331K| 537 (1)| 00:00:07 |
| 2 | BITMAP INDEX FULL SCAN | IDX_T_TEST | | | | |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
5543 consistent gets
516 physical reads
0 redo size
3196705 bytes sent via SQL*Net to client
55347 bytes received via SQL*Net from client
4986 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
74761 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25099483/viewspace-769637/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- INDEX UNIQUE SCAN,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 vs Index Fast Full ScanIndexAST
- Index Full Scan 與 Index Fast Full ScanIndexAST
- INDEX FULL SCAN和INDEX FAST FULL SCAN的區別IndexAST
- rowid,index,INDEX FULL SCAN,INDEX FAST FULL SCAN|IndexAST
- index full scan 和 index fast full scan (IFS,FFS)的不同IndexAST
- Index Full Scan 與 Index Fast Full Scan (Final)IndexAST
- Index的掃描方式:index full scan/index fast full scanIndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(上)IndexAST
- Index Full Scan和Index Fast Full Scan行為差異分析(下)IndexAST
- Fast full index scan 淺析ASTIndex
- index range scan,index fast full scan,index skip scan發生的條件IndexAST
- Index Full Scans和Index Fast Full ScansIndexAST
- [總結]關於index range scans & INDEX (FAST FULL SCAN)IndexAST
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- index fast full scan不能使用並行的實驗IndexAST並行
- oracle實驗記錄(INDEX fast full scan 的成本計算)OracleIndexAST
- 收集full table / index scan sqlIndexSQL
- Fast Full Index Scans的特點!ASTIndex
- FBI? MAX? INDEX FULL SCAN (MIN/MAX)?Index
- Oracle學習系列—資料庫最佳化—Full Scans和Fast Full Index ScansOracle資料庫ASTIndex
- (轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- 轉)索引掃描還是全表掃描(Index Scan Or Full Table Scan)索引Index
- 【優化】INDEX FULL SCAN (MIN/MAX)訪問路徑優化Index
- 【最佳化】INDEX FULL SCAN (MIN/MAX)訪問路徑Index
- 【TUNE_ORACLE】列出走了INDEX FULL SCAN的SQL參考OracleIndexSQL
- 20180316不使用INDEX FULL SCAN (MIN/MAX)Index
- INDEX SKIP SCANIndex
- Clustered Index Scan and Clustered Index SeekIndex
- 理解index skip scanIndex
- Index Unique Scan (213)Index
- oracle index unique scan/index range scan和mysql range/const/ref/eq_ref的區別OracleIndexMySql
- Index Range Scan成本 Histogram 和 10053IndexHistogram
- null與indexNullIndex
- PostgreSQL DBA(119) - pgAdmin(LIMIT:Index Scan vs Bitmap Index Scan)SQLMITIndex
- [轉貼]Skip Scan IndexIndex