验证Join选择率 谓词 与不等值

谓词选择率

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

create table t100 as select * from dba_objects;
create table t200 as select * from dba_tables;

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'OTEST',
tabname => 'T100',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'OTEST',
tabname => 'T200',
estimate_percent => 100,
method_opt => 'for all columns size 1',
degree => DBMS_STATS.AUTO_DEGREE,
cascade=>TRUE);
END;
/

 

SQL> set linesize 200 pagesize 500
SQL> select a.table_name,b.column_name,b.num_distinct,b.num_nulls,a.num_rows,b.histogram from user_t
ables a,user_tab_columns b where a.table_name = b.table_name and b.table_name = 'T100' and b.column_
name = 'OBJECT_NAME';

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_ROWS HIS
TOGRAM
------------------------------ ------------------------------ ------------ ---------- ---------- ---
------------
T100 OBJECT_NAME 71376 0 149783 NON
E

SQL> select a.table_name,b.column_name,b.num_distinct,b.num_nulls,a.num_rows,b.histogram from user_t
ables a,user_tab_columns b where a.table_name = b.table_name and b.table_name = 'T200' and b.column_
name = 'TABLE_NAME';

TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_ROWS HIS
TOGRAM
------------------------------ ------------------------------ ------------ ---------- ---------- ---
------------
T200 TABLE_NAME 5532 0 13068 NON
E

 

Join Sel = 1/Greatest(RS1.j1.NDV*RS1.j2.NDV, RS2.j1.NDV*RS2.j2.NDV) 

 

select 1/greatest(71376,5532) from dual ;  --值取NUM_DISTINCT 

SQL> select 1/greatest(71376,5532) from dual ;

1/GREATEST(71376,5532)
----------------------
.00001401

Join Card = RS1.Comp_Card * Rs2.Comp_Card*Join Sel 

 

select 149783*13068*(1/greatest(71376,5532)) from dual; 

 

149783*13068*(1/GREATEST(71376,5532))
-------------------------------------
27423.283

 

查看 执行计划中值对比 

 

SQL> set linesize 200 pagesize 500
SQL> explain plan for select * from t100 a,t200 b where a.object_name=b.table_name;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------

Plan hash value: 2412991213

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27423 | 8971K| | 1648 (1)| 00:00:20 |
|* 1 | HASH JOIN | | 27423 | 8971K| 3192K| 1648 (1)| 00:00:20 |
| 2 | TABLE ACCESS FULL| T200 | 13068 | 3037K| | 127 (1)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T100 | 149K| 13M| | 593 (1)| 00:00:08 |
-----------------------------------------------------------------------------------

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

1 - access("A"."OBJECT_NAME"="B"."TABLE_NAME")

已选择15行。

 

与HASH JOIN ROWS 值一致为27423 

 

-不等值选择率--------------------------------------------------------------------------------------------------------------------------

Join Sel = 0.05 
Join Card = RS1.Comp_Card * Rs2.Comp_Card*Join Sel 

SQL> explain plan for select * from t100 a,t200 b where a.object_name!=b.table_name;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

Plan hash value: 1074152267

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1957M| 610G| 7724K (1)| 25:44:54 |
| 1 | NESTED LOOPS | | 1957M| 610G| 7724K (1)| 25:44:54 |
| 2 | TABLE ACCESS FULL| T200 | 13068 | 3037K| 127 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL| T100 | 149K| 13M| 591 (1)| 00:00:08 |
---------------------------------------------------------------------------

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

3 - filter("A"."OBJECT_NAME"<>"B"."TABLE_NAME")

已选择15行。
Join Card = RS1.Comp_Card * Rs2.Comp_Card*Join Sel 

Join Card = 149783*13068=1957364244 得出结果 没有乘以Join Sel  即与 执行计划结果一致 

NESTED LOOPS | | 1957M

难道内部算法 有改变 ？