绑定变量窥测

最新推荐文章于 2024-08-09 21:25:57 发布

cltt980248285

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量89

点赞数

文章标签：数据库

环境说明】

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)