3. 이 책에서는 Calculated Cardinality + Estimated Cardinality
SQL> explain plan for
2 select count(*)
3 from t1
4 where c1 between 1 and 10
5 ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 14 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 10 | 40 | 14 (0)| 00:00:01 |
---------------------------------------------------------------------------
SQL> select /*+ gather_plan_statistics */
2 count(*)
3 from t1
4 where c1 between 1 and 10
5 ;
COUNT(*)
----------
10
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 14 (0)| 10 |
--------------------------------------------------------------------------------
SQL> explain plan for
2 select count(*)
3 from t1
4 where c2 = 1
5 ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 14 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 1000 | 3000 | 14 (0)| 00:00:01 |
---------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
--------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1000 | 14 (0)| 960 |
--------------------------------------------------------------------------------
TABLE_NAME : T1
COLUMN_NAME : C1
NUM_DISTINCT : 10000
NUM_NULLS : 0
DENSITY : .0001
LOW_VALUE : C102
HIGH_VALUE : C302
HISTOGRAM : NONE
-----------------
TABLE_NAME : T1
COLUMN_NAME : C2
NUM_DISTINCT : 10
NUM_NULLS : 0
DENSITY : .1
LOW_VALUE : 80
HIGH_VALUE : C10A
HISTOGRAM : NONE
-----------------
TABLE_NAME : T1
COLUMN_NAME : C3
NUM_DISTINCT : 100
NUM_NULLS : 0
DENSITY : .01
LOW_VALUE : 786B0B17010101
HIGH_VALUE : 786C0301010101
HISTOGRAM : NONE
-----------------
TABLE_NAME : T1
COLUMN_NAME : C4
NUM_DISTINCT : 100
NUM_NULLS : 0
DENSITY : .01
LOW_VALUE : 3230303731313233
HIGH_VALUE : 3230303830333031
HISTOGRAM : NONE
h6 Density
SQL> explain plan for
2 select *
3 from t1
4 where c1 = 1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 23 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c2 = 1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 23000 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1000 | 23000 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c1 = :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 23 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c2 = :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 23000 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1000 | 23000 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c1 between 1 and 100
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
*Cardinality* = Base Cardinality * (Selectivity(C1 BETWEEN X AND Y))
= Base Cardinality * (Selectivity(C1 = X) + Selectivity(C1 = Y) + Selectivity(X < C1 < Y))
= Base Cardinality * (Selectivity(C1 = X) + Selectivity(C1 = Y) + (Y - X)/(MAX - MIN))
= Base Cardinality * (Selectivity(C1 = 1) + Selectivity(C1 = 100) + (100 - 1)/(10000 - 1))
= 10000 * (0.0001 + 0.0001 + (99)/(9999))
= 10000 * (0.0001 + 0.0001 + 0.00990099)
= 10000 * 0.01000099
= 100
SQL> explain plan for
2 select *
3 from t1
4 where c1 between 1 and 100 and
5 c2 = 1
6 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 230 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 10 | 230 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
*Cardinality* = Base Cardinality * (Selectivity(C1 BETWEEN X AND Y)) * (SELECTIVITY(C2 = 1))
= 10000 * 0.01 * 0.1
= 0.001
SQL> explain plan for
2 select *
3 from t1
4 where c1 between 1 and 100 or
5 c2 = 1
6 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1090 | 25070 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1090 | 25070 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
*Cardinality* = Base Cardinality * (Selectivity(P1 OR P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) - Selectivity(P1 AND P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) - (Selectivity(P1) * Selectivity(P2)))
= 10000 * (0.01 + 0.1 - (0.01 * 0.1))
= 10000 * (0.109) = 1090
h6 3. date Type
SQL> explain plan for
2 select *
3 from t1
4 where c3 = to_date('20080201','yyyymmdd')
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c4 = '20080201'
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c3 between to_date('20071231','yyyymmdd')
5 and to_date('20080110','yyyymmdd')
6 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1210 | 27830 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1210 | 27830 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
*Cardinality* = Base Cardinality * (Selectivity(C3 BETWEEN P1 AND P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) + Selectivity(P1 < C1 < P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) + (P2 - P1)/(MAX - MIN))
= 10000 * (0.01 + 0.01 + (2008/01/10 - 2007/12/31) / (2008/03/01 - 2007/11/23))
= 10000 * (0.01 + 0.01 + (10)/(99))
= 10000 * 0.121010101
= 1210
SQL> explain plan for
2 select *
3 from t1
4 where c4 between '20071231'
5 and '20080110'
6 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9874 | 221K| 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 9874 | 221K| 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
*Cardinality* = Base Cardinality * (Selectivity(C4 BETWEEN P1 AND P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) + Selectivity(P1 < C4 < P2))
= Base Cardinality * (Selectivity(P1) + Selectivity(P2) + (P2 - P1)/(MAX - MIN))
= 10000 * (0.01 + 0.01 + (20080110 - 20071231) / (20080301 - 20071123))
= 10000 * (0.01 + 0.01 + (8879) / (9178))
= 10000 * 0.987422096
= 9874
SQL> explain plan for
2 select *
3 from t1
4 where c1 > :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c1 >= :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c2 > :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
Selectivity = 5%* 5% = 0.25%가 된다.
Cardinality = 10000 * 25% = 10000 * 0.0025 = 25
SQL> explain plan for
2 select *
3 from t1
4 where c1 between :b1 and :b2
5 ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 575 | 15 (7)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 25 | 575 | 15 (7)| 00:00:01 |
---------------------------------------------------------------------------
SQL> create or replace function f1(v1 int)
2 return int
3 is
4 begin
5 return v1;
6 end f1;
7 /
함수가 생성되었습니다.
SQL> explain plan for
2 select *
3 from t1
4 where f1(c1) = 1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 19 (27)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 19 (27)| 00:00:01 |
--------------------------------------------------------------------------
Cardinality = 10000 * 1% = 10000 * 0.01 = 100
SQL> explain plan for
2 select *
3 from t1
4 where f1(c2) = 1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 19 (27)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 19 (27)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where f1(c1) > 1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 19 (27)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 19 (27)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where f1(c1) between 1 and 100
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 575 | 20 (30)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 25 | 575 | 20 (30)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where f1(c1) between :b1 and :b2
5 ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 575 | 20 (30)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T1 | 25 | 575 | 20 (30)| 00:00:01 |
---------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c1 = f1(c1)
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 19 (27)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 23 | 19 (27)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c4 like '20080101'
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2300 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 2300 | 14 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c4 like :b1
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select /*+ opt_param('_like_with_bind_as_equality', 'true') */
3 *
4 from t1
5 where c4 like :b1
6 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 2323 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 101 | 2323 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c4 like '%2008'
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
SQL> explain plan for
2 select *
3 from t1
4 where c4 like '%2008%'
5 ;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 11500 | 15 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 500 | 11500 | 15 (7)| 00:00:01 |
--------------------------------------------------------------------------
- 강좌 URL : http://www.gurubee.net/lecture/3943
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.