环境说明】
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系统并发查询少，但是每次查询大量数据的情况下，可以通过取消绑定变量来提升系统的运行效率；
