本帖最后由 y54967892 于 2012-5-11 12:44 编辑
SQL语句如下:
select * from TB_PURFA fa
join cy2.TB_PUBFA fa1 on fa1.pubfa_id = fa.purfa002
where fa.purfa_id = 755
and fa.FLAG = 0;
用的是内连,返回一行数据,在pubfa002上面建有普通索引,但是没有外键约束。实际上这条数据:fa1.pubfa_id = fa.purfa002
是一对一的。
但是我用执行计划,发现rows的返回行是245行,执行计划如下:
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2210441224
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 208 | 3 (0)
| 1 | NESTED LOOPS | | 1 | 208 | 3 (0)
|* 2 | TABLE ACCESS BY INDEX ROWID| TB_PURFA | 1 | 141 | 2 (0)
|* 3 | INDEX UNIQUE SCAN | PK_TB_PURFA | 1 | | 1 (0)
| 4 | TABLE ACCESS BY INDEX ROWID| TB_PUBFA | 245 | 16415 | 1 (0)
|* 5 | INDEX UNIQUE SCAN | PK_TB_PUBFA | 1 | | 0 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FA"."FLAG"=0)
3 - access("FA"."PURFA_ID"=755)
5 - access("FA1"."PUBFA_ID"="FA"."PURFA002")
请问:为啥预计返回行数 和实际返回行数会相差这么大?
我刚刚把统计信息删了重建,貌似rows 变成了254,代码贴上:
exec dbms_stats.delete_table_stats('CY','TB_PUBFA');
exec dbms_stats.delete_table_stats('CY2','TB_PURFA');
analyze table TB_PUBFA delete statistics;
analyze table CY2.TB_PURFA delete statistics;
exec dbms_stats.gather_table_stats('CY','TB_PUBFA');
exec dbms_stats.gather_table_stats('CY2','TB_PURFA');
最新的执行计划:
1 Plan hash value: 2210441224
2
3 --------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 --------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 208 | 3 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS | | 1 | 208 | 3 (0)| 00:00:01 |
8 |* 2 | TABLE ACCESS BY INDEX ROWID| TB_PURFA | 1 | 141 | 2 (0)| 00:00:01 |
9 |* 3 | INDEX UNIQUE SCAN | PK_TB_PURFA | 1 | | 1 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS BY INDEX ROWID| TB_PUBFA | 254 | 17018 | 1 (0)| 00:00:01 |
11 |* 5 | INDEX UNIQUE SCAN | PK_TB_PUBFA | 1 | | 0 (0)| 00:00:01 |
12 --------------------------------------------------------------------------------------------
13
14 Predicate Information (identified by operation id):
15 ---------------------------------------------------
16
17 2 - filter("FA"."FLAG"=0)
18 3 - access("FA"."PURFA_ID"=755)
19 5 - access("FA1"."PUBFA_ID"="FA"."PURFA002")