[20140802]cost=0.txt

--我记得以前看基于成本优化的书提到,如果计算返回的行0,一般计算选择返回1行,除非查询条件为假.
--但是如果计算cost=0会出现什么情况呢?
--转载并测试(部分):

http://rajeshwaranbtech.blogspot.com/2014/07/cbo-estimates-cost-0.html

SCOTT@test01p> @ver

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t1 as select * from all_objects;
Table created.

SCOTT@test01p> create table t2 as select * from all_objects;
Table created.

SCOTT@test01p> alter table t2 add constraint t2_pk primary key(object_id);
Table altered.

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);
PL/SQL procedure successfully completed.

SCOTT@test01p> alter session set statistics_level=all;
Session altered.

SCOTT@test01p> select count(*) from t1, t2 where t1.object_id = t2.object_id ;
  COUNT(*)
----------
     89709

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  a2dvtjh6t4p0t, child number 0
-------------------------------------
select count(*) from t1, t2 where t1.object_id = t2.object_id
Plan hash value: 1249090617
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |        |   424 (100)|      1 |00:00:00.13 |    2878 |
|   1 |  SORT AGGREGATE     |       |      1 |      1 |            |      1 |00:00:00.13 |    2878 |
|   2 |   NESTED LOOPS      |       |      1 |  89709 |   424   (2)|  89709 |00:00:00.12 |    2878 |
|   3 |    TABLE ACCESS FULL| T1    |      1 |  89709 |   420   (1)|  89709 |00:00:00.02 |    1513 |
|*  4 |    INDEX UNIQUE SCAN| T2_PK |  89709 |      1 |     0   (0)|  89709 |00:00:00.05 |    1365 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

--我们可以发现因为id=4选择INDEX UNIQUE SCAN,cost=0,导致执行计划趋向于 NESTED LOOPS.最后总的Buffers=2878.
--使用提示看看:

SCOTT@test01p> select /*+ use_hash(t1,t2) */ count(*) from t1, t2 where t1.object_id = t2.object_id ;
  COUNT(*)
----------
     89709

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  f0yyx5m29hb3x, child number 0
-------------------------------------
select /*+ use_hash(t1,t2) */ count(*) from t1, t2 where t1.object_id =
t2.object_id

Plan hash value: 2219068312

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |      1 |        |   618 (100)|      1 |00:00:00.22 |    1707 |       |       |          |
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.22 |    1707 |       |       |          |
|*  2 |   HASH JOIN            |       |      1 |  89709 |   618   (1)|  89709 |00:00:00.22 |    1707 |  5536K|  3056K| 5645K (0)|
|   3 |    INDEX FAST FULL SCAN| T2_PK |      1 |  89710 |    52   (0)|  89710 |00:00:00.04 |     194 |       |       |          |
|   4 |    TABLE ACCESS FULL   | T1    |      1 |  89709 |   420   (1)|  89709 |00:00:00.01 |    1513 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

--虽然cost=618比424高,但是Buffers=1707明显比前面2878的小.