如果所示,请问rows代表什么意思啊 有人说是预计返回的 row (实际返回243行),怎么差别那么大呢 我在执行计划之前,已经用一下语句分析过改表了:
exec dbms_stats.gather_schema_stats( -
ownname => 'GAOXT', --mt_201005表属于gaoxt用户
options => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt => 'for all columns size repeat', -
degree => 15 -
); 什么时候执行计划中的 rows才和实际返回的rows相等 啊
对比下面两个测试
SQL> explain plan for Select * from sizes s where s.struct_id=24 and rownum <10 order by s.measure_size desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3459659780
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 432 | 3 (0)| 00:00:01 |
| 1 | SORT ORDER BY | | 9 | 432 | 3 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | TABLE ACCESS FULL| SIZES | 47 | 2256 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<10)
3 - filter("S"."STRUCT_ID"=24)
16 rows selected
SQL> explain plan for Select * from sizes s where s.struct_id=24 and s.measure_size is not null order by s.measure_size desc;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2279842070
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47 | 2256 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 47 | 2256 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| SIZES | 47 | 2256 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("S"."STRUCT_ID"=24 AND "S"."MEASURE_SIZE" IS NOT NULL)
14 rows selected