案例一 准备数据
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选择率
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选择率
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" 是别名
/*+ cardinality(@"SEL$1",100000)*/ 指定结果集cardinality的大小 @"SEL$1" 是别名