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博客 ” ，链接：https://blog.itpub.net/25099483/viewspace-769637/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
