[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的小.