搭建环境
构建一个表
CREATE TABLE TEST2 AS SELECT * FROM DBA_OBJECTS;
UPDATE SET OBJECT_ID = 11111 WHERE ROWNUM <=50000;
建立索引:
CREATE INDEX IND_TEST2_OBJECT_ID ON TEST2(OBJECT_ID);
收集统计信息,不收集直方图
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'TEST2',ESTIMATE_PERCENT=>100,cascade=>true,method_opt => 'for all columns size 1');
SQL> SELECT OBJECT_ID FROM TEST2 WHERE OBJECT_NAME = 'TEST';
OBJECT_ID
----------
52694
毋庸置疑index range scan
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=11111;
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=52694;
收集直方图
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST2',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
全表扫描
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=11111;
index range scan
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=52694;
9i,10g
alter system flush shared_pool;
var a number;
exec :a := 52694;
SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;
COUNT(*)
----------
1
SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;
COUNT(*)
----------
1
SQL> SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a;
COUNT(*)
----------
1
SQL> select sql_id,child_number from v$sql where sql_text like 'SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a%';
SQL_ID CHILD_NUMBER
------------- ------------
22gd2utvtttmj 0
SQL> select * from table(dbms_xplan.display_cursor('22gd2utvtttmj',0,'advanced peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 22gd2utvtttmj, child number 0
-------------------------------------
SELECT count(*) FROM TEST2 WHERE OBJECT_ID=:a
Plan hash value: 3600962442
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST2_OBJECT_ID | 1 | 5 | 1 (0)| 00:00:01 |
---------