字面成本比count低
SQL> select trunc(day,'mm'),count(*) from t group by day;
TRUNC(DAY,'MM' COUNT(*)
-------------- ----------
01-1月 -16 2
01-3月 -16 4
01-2月 -16 3
01-4月 -16 1
01-5月 -16 1
执行计划
----------------------------------------------------------
Plan hash value: 47235625
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 99 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 99 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 11 | 99 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
SQL> set lines 120
SQL> create index tday on t(day);
SQL> with a as(select add_months(date'2016-01-01',level-1)a from dual connect by level<=6)
2 select a.a,case when exists(select 1 from t where a.a=day) then 1 end x from a;
A X
-------------- ----------
01-1月 -16 1
01-2月 -16 1
01-3月 -16 1
01-4月 -16 1
01-5月 -16 1
01-6月 -16
执行计划
----------------------------------------------------------
Plan hash value: 3294269650
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN | TDAY | 1 | 9 | 1 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 6 | 2 (0)| 00:00:01 |
| 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DAY"=:B1)
Note
-----
- dynamic sampling used for this statement