MSQL优化基础(Join 选择率)

最新推荐文章于 2021-04-03 11:55:59 发布

夜归人_

最新推荐文章于 2021-04-03 11:55:59 发布

阅读量600

点赞数

分类专栏： MSQL优化

本文链接：https://blog.csdn.net/night_person_/article/details/46839589

版权

MSQL优化专栏收录该内容

6 篇文章 1 订阅

订阅专栏

案例一准备数据

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