oracle單列索引和複合式索引效能測試

標籤：單列和複合式索引

共有兩張表t1,t2

t1的表結構

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM7aDh9FgMAAFNXvJU8Ms226.jpg" title="1.png" alt="wKiom1SM7aDh9FgMAAFNXvJU8Ms226.jpg" />

t2的表結構

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/56/C3/wKiom1SM7dSCS6nFAAE5Jn1wIfw943.jpg" title="2.png" alt="wKiom1SM7dSCS6nFAAE5Jn1wIfw943.jpg" />

t1和t2通過

insert into t1 values (‘www‘,‘aaa‘,‘bbb‘);

insert into t1 values (‘www1‘,‘aaa1‘,‘bbb1‘);

insert into t1 values (‘www2‘,‘aaa2‘,‘bbb2‘);

insert into t1 values (‘www3‘,‘aaa3‘,‘bbb3‘);

t2同理構建

insert into t1 select * from t1;

insert into t2 select * from t2;

構建資料共：

t1共262144行

t2共524288行

為保證查詢測試準確（不受緩衝結果的影響）

在執行下一次查詢前都會執行以下語句

ALTER SYSTEM FLUSH SHARED_POOL （清空語句，執行計畫緩衝）

ALTER SYSTEM FLUSH  BUFFER_CACHE （清空資料緩衝）

select t1.a1,t2.a1 from t1,t2 where t1.a3=t2.a3 and t2.a1=‘www‘;

select t1.a1,t2.a1 from t1 join t2 using(a3) where t2.a1=‘www‘;

我們先用

select t1.a1,t2.a1 from t1,t2 where t1.a3=t2.a3 and t2.a1=‘www‘;

測試

explain plan for select t1.a1,t2.a1 from t1,t2 where t1.a3=t2.a3 and t2.a1=‘www‘;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 

1、不建任何索引

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/56/C0/wKioL1SM7rPiO8jSAAN0dADWmzI552.jpg" title="3.png" alt="wKioL1SM7rPiO8jSAAN0dADWmzI552.jpg" />

t1,t2都是全表掃描

2、在t2.a1建索

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/56/C3/wKiom1SM7mKSS2wIAACc1VVeDwI540.jpg" title="4.png" alt="wKiom1SM7mKSS2wIAACc1VVeDwI540.jpg" />

看查詢結果

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/56/C0/wKioL1SM7zTD3BkzAAOw_trWR6o878.jpg" title="5.png" alt="wKioL1SM7zTD3BkzAAOw_trWR6o878.jpg" />

t1全表掃描，t2範圍索引掃描

3、在t2.a1和t1.a3建非唯一索引

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM73myy97bAAPeTdOSTNU819.jpg" title="6.png" alt="wKiom1SM73myy97bAAPeTdOSTNU819.jpg" />

4、在t2.a1，t2.a3和t1.a3建非唯一索引

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/56/C1/wKioL1SM8FPyW4-5AAPRLGpzcfU661.jpg" title="7.png" alt="wKioL1SM8FPyW4-5AAPRLGpzcfU661.jpg" />

5，在t1.a1，t1.a3建立複合式索引，在t2.a3建非唯一索引

select t1.a1,t2.a1 from t1,t2 where t1.a3=t2.a3 and t2.a1=‘www‘;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/56/C3/wKiom1SM7_XytUXfAAMh97QNIMo660.jpg" title="8.png" alt="wKiom1SM7_XytUXfAAMh97QNIMo660.jpg" />

6、在t1,t2的a1和t3列建立複合式索引

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/56/C1/wKioL1SM8LySdB6EAAMlbMJNn0w656.jpg" title="9.png" alt="wKioL1SM8LySdB6EAAMlbMJNn0w656.jpg" />

根據索引的類型與where限制條件的不同，有4種類型的索引掃描：

索引唯一掃描（index unique scan）

索引範圍掃描（index range scan）

索引全掃描（index full scan）

索引快速掃描（index fast full scan）

後續逐步完善測試結果！

本文出自 “Oracle” 部落格，請務必保留此出處http://axlewire.blog.51cto.com/1418858/1589683

oracle單列索引和複合式索引效能測試