关闭

MSQL优化基础(Join 选择率)

198人阅读 评论(0) 收藏 举报
分类:

案例一 准备数据

drop table t1;
drop table t2;
create table t1 (id number ,c1 number);
create table t2 (id number , c1 number);
begin 
   for i in 1000..10000 loop
     for j in 1..100 loop
      insert into t1 values (mod(i,10),i);
	  insert into t2 values (mod(i,8),i);
	 end loop;
	end loop;
	commit;
end;

exec dbms_stats.gather_table_stats(null, 'T1', method_opt=>'for all columns size 1');
exec dbms_stats.gather_table_stats(null, 'T2',method_opt=>'for all columns size 1');


select table_name, column_name, num_distinct ,histogram  from user_tab_columns where table_name in ('T1','T2');
TABLE_NAME                     COLUM NUM_DISTINCT HISTOGRAM
------------------------------ ----- ------------ ---------------
T1                             ID              10 NONE
T1                             C1            9002 NONE
T2                             ID               8 NONE
T2                             C1            8999 NONE


select table_name ,num_rows from user_tables where table_name in ('T1','T2');
TABLE_NAME                       NUM_ROWS
------------------------------ ----------
T1                                 896690
T2                                 895692


案例二 join选择率 =

join选择率= 1/最大值(RS1.J1.NDV, RS2.J1.NDV)
Join Card = RS1.Card*RS2.Card*oin选择率


set autot on 
select * from t1, t2 where t1.c1=1000 and t2.c1=1001 and t1.id=t2.id;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   991 | 11892 |   690   (7)| 00:00:09 |
|*  1 |  HASH JOIN         |      |   991 | 11892 |   690   (7)| 00:00:09 |
|*  2 |   TABLE ACCESS FULL| T2   |   100 |   600 |   344   (7)| 00:00:05 |
|*  3 |   TABLE ACCESS FULL| T1   |   100 |   600 |   346   (7)| 00:00:05 |
---------------------------------------------------------------------------

join选择率= 1/最大值(RS1.J1.NDV, RS2.J1.NDV)  = 1/10
Join Card = RS1.Card*RS2.Card*oin选择率
          896690*1/9002*895692*1/8999*1/10  = 991.443024
 
和 rows 991 一致   试验环境    10.2g



案例三  join选择率 <,<= , > , >=

join选择率= 0.05
Join Card = RS1.Card*RS2.Card*oin选择率

explain plan for select * from t1, t2 where t1.c1=1000 and t2.c1=1001 and t1.id<t2.id;
select * from table(dbms_xplan.display())

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   496 |  5952 |   692   (7)| 00:00:09 |
|   1 |  MERGE JOIN         |      |   496 |  5952 |   692   (7)| 00:00:09 |
|   2 |   SORT JOIN         |      |   100 |   600 |   345   (7)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL| T2   |   100 |   600 |   344   (7)| 00:00:05 |
|*  4 |   SORT JOIN         |      |   100 |   600 |   347   (7)| 00:00:05 |
|*  5 |    TABLE ACCESS FULL| T1   |   100 |   600 |   346   (7)| 00:00:05 |


896690*1/9002*895692*1/8999*0.05= 495.721512  

和rows一样  正确   试验环境 10.2g 


补充

/*+ cardinality(t1,100000)*/  可以用这个hint 指定表的cardinality的大小
/*+ cardinality(@"SEL$1",100000)*/   指定结果集cardinality的大小    @"SEL$1" 是别名




0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:30099次
    • 积分:1465
    • 等级:
    • 排名:千里之外
    • 原创:122篇
    • 转载:0篇
    • 译文:0篇
    • 评论:1条
    最新评论