NESTED LOOPS 中OUT TABLE CBO是如何选择的

anbob

现在有可问题，观察执行计划时发现在NL join 中驱动表选择有点问题，默认运行是1分40秒，加了ordered hint更换驱动表后大概是40秒，做了10053 level 1 trace ,我的问题是如果找出cbo 是如何判断在NL JOIN中用哪个表做out table的。
默认cbo的执行计划


select /*+ gather_plan_statistics*/  count(*) total,count(distinct stu.ic_code)
hav_sc_stucn,sum(ns.class_score),stu.ic_code  from   icme_student stu join icme_noproject_score ns  on
stu.ic_code=ns.ic_code group by  stu.ic_code

Plan hash value: 1102099122

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT GROUP BY         |                    |      1 |    608K|    172K|00:00:30.77 |      41M|    98M|    15M|   87M (0)|
|   2 |   NESTED LOOPS         |                    |      1 |     20M|     20M|00:02:03.45 |      41M|       |       |          |
|   3 |    INDEX FAST FULL SCAN| UK_NOPROJECT_SCORE |      1 |     20M|     20M|00:00:00.01 |     219K|       |       |          |
|*  4 |    INDEX RANGE SCAN    | PK_ICME_STUDENT    |     20M|      1 |     20M|00:01:25.71 |      41M|       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

select /*+ ordered */ count(*) total,count(distinct stu.ic_code) hav_sc_stucnt,sum(ns.class_score),stu.ic_code  from   icme_student stu
join icme_noproject_score ns  on stu.ic_code=ns.ic_code                                                                              
group by stu.ic_code   

854409 rows selected.

Elapsed: 00:00:34.78

Execution Plan
----------------------------------------------------------
Plan hash value: 2339117530

-----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                    |   608K|    13M|       |   129K  (2)| 00:26:00 |
|   1 |  SORT GROUP BY         |                    |   608K|    13M|   648M|   129K  (2)| 00:26:00 |
|*  2 |   HASH JOIN            |                    |    20M|   470M|    24M| 75642   (1)| 00:15:08 |
|   3 |    INDEX FAST FULL SCAN| PK_ICME_STUDENT    |  1179K|    11M|       |   956   (1)| 00:00:12 |
|   4 |    INDEX FAST FULL SCAN| UK_NOPROJECT_SCORE |    20M|   274M|       | 47986   (1)| 00:09:36 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NS"."IC_CODE"="STU"."IC_CODE")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     222813  consistent gets

Maclean Liu(刘相兵

Because of the structure of
this pseudo-code, the two tables in a nested loop are commonly referred to as the outer table
and the inner table. The outer table is also commonly referred to as the driving table (although
I don’t think I’ve ever heard the inner table referred to as the driven table).


FOR Nested Loop

outer table = > driving table
inner table => source table


“OUTER” AND “INNER” TABLES
The terms outer and inner are really only appropriate to nested loop joins. When talking about hash joins, you
ought to refer to the build table and probe table; and for merge joins, the terms first table and second table are
sufficient. However, you will find that the 10053 trace file always uses the terms outer and inner to identify
the first and second tables respectively in a join operation. I will revisit this point in the relevant chapters.

FROM CBO

在10053 TRACE中 ， 总是用 outer table 代表 join order 中 第一位的表，  而用 inner table 代表 join order  中第二位的表。  
对于 所有的 join method 包括 hash join 、SM join、 NL join 都是这样。

10053 中 optimizer 会考虑多种 join order 配合 多种 join method 的组合。





Considering cardinality-based initial join order.
***********************
Join order[1]:  ICME_STUDENT[STU]#0  ICME_NOPROJECT_SCORE[NS]#1
***************
Now joining: ICME_NOPROJECT_SCORE[NS]#1
***************
NL Join
  Outer table: Card: 1179881.00  Cost: 955.76  Resp: 955.76  Degree: 1  Bytes: 10
  Inner table: ICME_NOPROJECT_SCORE  Alias: NS
  Access Path: TableScan
    NL Join:  Cost: 110013839115.47  Resp: 110013839115.47  Degree: 0
      Cost_io: 109623924540.00  Cost_cpu: 8664492175899741
      Resp_io: 109623924540.00  Resp_cpu: 8664492175899741




在LZ 的TRACE中 包括：

NL:

Join order[1]:  ICME_STUDENT[STU]#0  ICME_NOPROJECT_SCORE[NS]#1              Best NL cost: 1410028.31
Join order[2]:  ICME_NOPROJECT_SCORE[NS]#1  ICME_STUDENT[STU]#0              Best NL cost: 66742.49

所以 CBO 会选择   ICME_NOPROJECT_SCORE 做outer table  ，  ICME_STUDENT 做inner table

anbob

ok,well,thanks for  your help