環境說明】

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系統併發查詢少，但是每次查詢大量資料的情況下，可以透過取消繫結變數來提升系統的執行效率；

繫結變數窺測

相關文章