可以看到两个语句的执行计划是完全相同的。

2、场景3也与前两个场景等价，因为id_je有NOT NULL约束

For Sel3, CBO does the same as for Sel1 and Sel2 since "id_je" has a NOT NULL constraint.

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.