sys@ORCL>select count(id_je) from journal_entries;
COUNT(ID_JE)
------------
9
sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b1p4v15dwx7hs, child number 0
-------------------------------------
select count(id_je) from journal_entries
Plan hash value: 42135099
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_ID_JE | 1 | 9 | 9 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------
14 rows selected.
可以看到执行计划与前两个也是完全相同的。
4、场景4跟前边3个不同，因为balanced列上没有NOT NULL约束，但是balanced列上有索引，那会走这个列上的索引么？我们来看一下执行计划：
sys@ORCL>select count(balanced) from journal_entries;
COUNT(BALANCED)
---------------
9
sys@ORCL>select * from table(dbms_xplan.display_cursor(null,null,'runstats_last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bc3bc8c0fg14z, child number 0
-------------------------------------
select count(balanced) from journal_entries
Plan hash value: 3638043346
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| INDX_ECR_DATE_JE_BALANCED | 1 | 9 | 9 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------
14 rows selected.
我们看到这个执行计划没有走balanced列上的索引，而是走了和date_je的联合索引。这个可以查看另一篇文档：Note:67522.1 Why is my index not used?
小结一下：