oracle版本：11.2.0
【一】进行测试环境的配置
1. 创建表test
create table test as select rownum id ,a.* from all_objects a;
2. 创建索引
CREATE INDEX EKPJ."TEST~ID" ON EKPJ.TEST(ID) NOLOGGING
3. 进行统计信息的收集
Execute DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TEST');
4. 进行字段ID分布情况的收集
SELECT count(id), count(DISTINCT id), min(id), max(id) FROM TEST;
COUNT(ID) COUNT(DISTINCT ID) MIN(ID) MAX(ID)
1069299 83539 1 83539
5. 查看执行计划的SQL
alter session set STATISTICS_LEVEL=ALL ----不设置无法获得A-ROWS等信息；
select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, 'ALLSTATS'));
【二】进行测试
2.1 测试当id值小于4的时候，这个时候结果集只有4，应该走索引
点击(此处)折叠或打开
-
select count(object_name) from test where id <= :id; #此处ID输入4
-
2.
-
3.SQL_ID 6jq05u5z8k9bn, child number 0
-
4.-------------------------------------
-
5.select count(object_name) from test where id <= :id
-
6.
-
7.Plan hash value: 885655847
-
8.
-
9.------------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-
------------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 1 |
-
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 | 1 |
-
|* 2 | INDEX RANGE SCAN| TEST~ID | 1 | 534K| 512 |00:00:00.01 | 4 | 1 |
-
------------------------------------------------------------------------------------------------
-
10.
-
11.Predicate Information (identified by operation id):
-
12.---------------------------------------------------
-
13. - access("ID"<=:ID)
- 14. 总结：此处使用的是走索引的执行计划，效果很好；
2.2 测试当id值小于83000的时候，几乎是整个表的数据了，这个时候走全表扫描会更快
点击(此处)折叠或打开
-
select count(object_name) from test where id <= :id; #此处ID输入83000
-
2.2.SQL_ID 6jq05u5z8k9bn, child number 1
-
3.3.-------------------------------------
-
4.4.select count(object_name) from test where id <= :id
-
5.5.
-
6.6.-----------------------------------------------------------------------------------------------------------
-
7.| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-
8.-----------------------------------------------------------------------------------------------------------
-
9.| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:18.57 | 10M| 172K|
-
10.| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:18.57 | 10M| 172K|
-
11.| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 534K| 10M|00:00:17.46 | 10M| 172K|
-
12.|* 3 | INDEX RANGE SCAN | TEST~ID | 1 | 96237 | 10M|00:00:01.97 | 22664 | 2886 |
-
13.-----------------------------------------------------------------------------------------------------------
-
14.7.Predicate Information (identified by operation id):
-
15.---------------------------------------------------
-
16.
-
17. 3 - access("ID"<=:ID)
- 18.8. 当前显示还是走索引
2.3 测试在ID为83000时候，走全表扫描的速度（通过hint来强制走全表扫描）
点击(此处)折叠或打开
-
SQL_ID duyq44cmbt2hm, child number 0
-
-------------------------------------
-
select /*+FULL(TEST)*/ count(object_type) from test where id <= :id
-
2.select count(object_type) from test where id <= 83000 #或直接用数字 Plan hash value: 1950795681
-
3.----------------------------------------------------------------------------------------------
-
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-
----------------------------------------------------------------------------------------------
-
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.88 | 158K| 158K|
-
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.88 | 158K| 158K|
-
|* 2 | TABLE ACCESS FULL| TEST | 1 | 534K| 10M|00:00:01.92 | 158K| 158K|
-
----------------------------------------------------------------------------------------------
-
-
Predicate Information (identified by operation id):
-
---------------------------------------------------
-
- 2 - filter("ID"<=:ID)
显示为2秒88，完成；
总结：
绑定变量产生的执行计划会以第一次的执行计划为标准，当第一次输入一个值的时候，会对这个值进行一个窥探，从而产生一个执行计划；
在OLAP系统并发查询少，但是每次查询大量数据的情况下，可以通过取消绑定变量来提升系统的运行效率；