Re: About CBO cost in oracle10g

On 21 May 2004 02:25:53 -0700, eygle_at_itpub.net (eygle) wrote:

>We know ,Oracle compute cost for CBO.
>And to NL,we have the below formula:
>
>NL - NESTED LOOP JOIN
>join cost = cost of accessing outer table
>+ (cardinality of outer table * cost of accessing inner table )
>
>But with Oracle10g,I find all of that is changed:
>For example:
>
>***************************************
>BASE STATISTICAL INFORMATION
>***********************
>Table stats Table: DEPT Alias: DEPT
> TOTAL :: CDN: 4 NBLKS: 5 AVG_ROW_LEN: 20
> COLUMN: DEPTNO(NUMBER) Col#: 1 Table: DEPT Alias: DEPT
> Size: 3 NDV: 4 Nulls: 0 Density: 2.5000e-01 Min: 10 Max: 40
> No Histogram: #BKT: 1
> (1 uncompressed buckets and 2 endpoint values)
>Index stats
> Index: PK_DEPT COL#: 1
> TOTAL :: LVLS: 0 #LB: 1 #DK: 4 LB/K: 1 DB/K: 1 CLUF: 1
>***********************
>Table stats Table: EMP Alias: EMP
> TOTAL :: CDN: 14 NBLKS: 5 AVG_ROW_LEN: 37
> COLUMN: DEPTNO(NUMBER) Col#: 8 Table: EMP Alias: EMP
> Size: 3 NDV: 3 Nulls: 0 Density: 3.3333e-01 Min: 10 Max: 30
> No Histogram: #BKT: 1
> (1 uncompressed buckets and 2 endpoint values)
>Index stats
> Index: PK_EMP COL#: 1
> TOTAL :: LVLS: 0 #LB: 1 #DK: 14 LB/K: 1 DB/K: 1 CLUF: 1
>_OPTIMIZER_PERCENT_PARALLEL = 0
>***************************************
>SINGLE TABLE ACCESS PATH
> TABLE: EMP Alias: EMP
> Original Card: 14 Rounded Card: 14 Computed Card: 14.00
> Access Path: table-scan Resc: 3 Resp: 3
> BEST_CST: 3.02 PATH: 2 Degree: 1
>***************************************
>SINGLE TABLE ACCESS PATH
> TABLE: DEPT Alias: DEPT
> Original Card: 4 Rounded Card: 4 Computed Card: 4.00
> Access Path: table-scan Resc: 3 Resp: 3
> BEST_CST: 3.02 PATH: 2 Degree: 1
>***************************************
>OPTIMIZER STATISTICS AND COMPUTATIONS
>***************************************
>GENERAL PLANS
>***********************
>Join order[1]: DEPT[DEPT]#0 EMP[EMP]#1
>Now joining: EMP[EMP]#1 *******
>NL Join
> Outer table: cost: 3 cdn: 4 rcz: 13 resp: 3
> Inner table: EMP Alias: EMP
> Access Path: table-scan Resc: 2
> Join: Resc: 9 Resp: 9
> Best NL cost: 9 resp: 9
>Join cardinality: 14 = outer (4) * inner (14) * sel (2.5000e-01) [flag=0]
>
>I want to know where the Best NL cost come from?
>And how can we get the '9' here?
>
>Thanks in advance.

Logic dictates it should be the cost for a single table scan inner table multiplied by the cost for a single table scan outer table. Doing so, you have to multiply 3.02 by 3.02. Apparently they rounded it down.

--
Sybrand Bakker, Senior Oracle DBA