SQL> variable b1 number
SQL> exec :b1 :=3;
PL/SQL procedure successfully completed.
SQL> select * from factor where object_id=:b1;
当使用绑定变量时CBO计算cardicary时使用 rows为table的5%
4983185%5=2500左右
SQL> select count(*) from factor;
COUNT(*)
----------
49831
exec :b1 :=900
SQL> select * from factor where object_id<:b1>
853 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4037279301
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 2492 | 226K| 12 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FACTOR | 2492 | 226K| 12 (0)| 00:0
0:01 |
|* 2 | INDEX RANGE SCAN | FACIDX | 448 | | 2 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
128 consistent gets
0 physical reads
0 redo size
81683 bytes sent via SQL*Net to client
1001 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
853 rows processed
SQL> select * from factor where object_id<900;
853 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4037279301
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 0 | SELECT STATEMENT | | 860 | 79980 | 22 (0)| 00:0
0:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| FACTOR | 860 | 79980 | 22 (0)| 00:0
0:01 |
|* 2 | INDEX RANGE SCAN | FACIDX | 860 | | 3 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<900)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
128 consistent gets
0 physical reads
0 redo size
81683 bytes sent via SQL*Net to client
1001 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
853 rows processed
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7301064/viewspace-448602/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7301064/viewspace-448602/