组合索引应该怎么选取引导列？

[html]  view plain copy print ?

1. 有这样一个SQL  
2. select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';  
3. id列选择性很高，owner选择性很低  
4. 要优化它很简单，只需要在t1表上建立一个组合索引(owner,id)，在t2表上建立一个索引(id)  
5. 现在要讨论的是我们应该怎么建立组合索引，哪一列(owner,id)应该放在最前面？  
6.   
7. 现在来做个实验  
8.   
9. SQL> desc t1  
10. Name        Type          Nullable Default Comments   
11. ----------- ------------- -------- ------- --------   
12. ID          NUMBER        Y                           
13. OBJECT_NAME VARCHAR2(128) Y                           
14. OWNER       VARCHAR2(30)  Y                           
15.   
16. SQL> desc t2  
17. Name      Type        Nullable Default Comments   
18. --------- ----------- -------- ------- --------   
19. ID        NUMBER      Y                           
20. STATUS    VARCHAR2(7) Y                           
21. TEMPORARY VARCHAR2(1) Y      
22.   
23.   
24. SQL> create index inx_id on t2(id);  
25.   
26. Index created.  
27.   
28. SQL> create index inx_id_owner on t1(id,owner);  
29.   
30. Index created.  
31.   
32. SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';  
33.   
34. Elapsed: 00:00:00.02  
35.   
36. Execution Plan  
37. ----------------------------------------------------------  
38. Plan hash value: 2432674005  
39.   
40. ---------------------------------------------------------------------------------------  
41. | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
42. ---------------------------------------------------------------------------------------  
43. |   0 | SELECT STATEMENT       |              |     1 |    16 |    88   (2)| 00:00:02 |  
44. |   1 |  SORT AGGREGATE        |              |     1 |    16 |            |          |  
45. |*  2 |   HASH JOIN            |              |  2416 | 38656 |    88   (2)| 00:00:02 |  
46. |*  3 |    INDEX FAST FULL SCAN| INX_ID_OWNER |  2416 | 26576 |    50   (0)| 00:00:01 |  
47. |   4 |    INDEX FAST FULL SCAN| INX_ID       | 72475 |   353K|    37   (0)| 00:00:01 |  
48. ---------------------------------------------------------------------------------------  
49.   
50. Predicate Information (identified by operation id):  
51. ---------------------------------------------------  
52.   
53.    2 - access("T1"."ID"="T2"."ID")  
54.    3 - filter("T1"."OWNER"='SCOTT')  
55.   
56.   
57. Statistics  
58. ----------------------------------------------------------  
59.           0  recursive calls  
60.           0  db block gets  
61.         392  consistent gets  
62.           0  physical reads  
63.           0  redo size  
64.         422  bytes sent via SQL*Net to client  
65.         420  bytes received via SQL*Net from client  
66.           2  SQL*Net roundtrips to/from client  
67.           0  sorts (memory)  
68.           0  sorts (disk)  
69.           1  rows processed  
70.                         
71. SQL> create index inx_owner_id on t1(owner,id);  
72.   
73. Index created.  
74.   
75. SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';  
76.   
77. Elapsed: 00:00:00.03  
78.   
79. Execution Plan  
80. ----------------------------------------------------------  
81. Plan hash value: 277464349  
82.   
83. ---------------------------------------------------------------------------------------  
84. | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
85. ---------------------------------------------------------------------------------------  
86. |   0 | SELECT STATEMENT       |              |     1 |    16 |    47   (3)| 00:00:01 |  
87. |   1 |  SORT AGGREGATE        |              |     1 |    16 |            |          |  
88. |*  2 |   HASH JOIN            |              |  2416 | 38656 |    47   (3)| 00:00:01 |  
89. |*  3 |    INDEX RANGE SCAN    | INX_OWNER_ID |  2416 | 26576 |     9   (0)| 00:00:01 |  
90. |   4 |    INDEX FAST FULL SCAN| INX_ID       | 72475 |   353K|    37   (0)| 00:00:01 |  
91. ---------------------------------------------------------------------------------------  
92.   
93. Predicate Information (identified by operation id):  
94. ---------------------------------------------------  
95.   
96.    2 - access("T1"."ID"="T2"."ID")  
97.    3 - access("T1"."OWNER"='SCOTT')  
98.   
99.   
100. Statistics  
101. ----------------------------------------------------------  
102.           0  recursive calls  
103.           0  db block gets  
104.         169  consistent gets  
105.           0  physical reads  
106.           0  redo size  
107.         422  bytes sent via SQL*Net to client  
108.         420  bytes received via SQL*Net from client  
109.           2  SQL*Net roundtrips to/from client  
110.           0  sorts (memory)  
111.           0  sorts (disk)  
112.           1  rows processed  
113.   
114. 如果OWNER作为引导列，逻辑读由以前的392变成现在的169，并且由以前的 index fast full scan 变成index range scan  
115.   
116. 如果强制指定走索引 inx_id_owner   
117.             
118. SQL>  select /*+ index(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';  
119.   
120. Elapsed: 00:00:00.03  
121.   
122. Execution Plan  
123. ----------------------------------------------------------  
124. Plan hash value: 3161475902  
125.   
126. ---------------------------------------------------------------------------------------  
127. | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
128. ---------------------------------------------------------------------------------------  
129. |   0 | SELECT STATEMENT       |              |     1 |    16 |   259   (1)| 00:00:04 |  
130. |   1 |  SORT AGGREGATE        |              |     1 |    16 |            |          |  
131. |*  2 |   HASH JOIN            |              |  2416 | 38656 |   259   (1)| 00:00:04 |  
132. |*  3 |    INDEX FULL SCAN     | INX_ID_OWNER |  2416 | 26576 |   221   (1)| 00:00:03 |  
133. |   4 |    INDEX FAST FULL SCAN| INX_ID       | 72475 |   353K|    37   (0)| 00:00:01 |  
134. ---------------------------------------------------------------------------------------  
135.   
136. Predicate Information (identified by operation id):  
137. ---------------------------------------------------  
138.   
139.    2 - access("T1"."ID"="T2"."ID")  
140.    3 - access("T1"."OWNER"='SCOTT')  
141.        filter("T1"."OWNER"='SCOTT')  
142.   
143.   
144. Statistics  
145. ----------------------------------------------------------  
146.           0  recursive calls  
147.           0  db block gets  
148.         387  consistent gets  
149.           0  physical reads  
150.           0  redo size  
151.         422  bytes sent via SQL*Net to client  
152.         420  bytes received via SQL*Net from client  
153.           2  SQL*Net roundtrips to/from client  
154.           0  sorts (memory)  
155.           0  sorts (disk)  
156.           1  rows processed  
157.   
158. 依然要387个逻辑读。  
159.   
160. 为什么要以owner为引导列？因为ID是join列，并且ID列上面没过滤条件，如果以ID列作为引导列，由于没过滤条件  
161. 那么CBO只能走 index full scan,或者index fast full scan，因为引导列没过滤条件，走不了index range scan,  
162. 最多走index skip scan，不过index skip scan代价过高，因为index skip scan要求 引导列选择性很低，但是ID这里选择性很高  
163.   
164. SQL> select /*+ index_ss(t1 inx_id_owner) */ count(*) from t1,t2 where t1.id=t2.id and t1.owner='SYS';  
165.   
166. Elapsed: 00:00:00.10  
167.   
168. Execution Plan  
169. ----------------------------------------------------------  
170. Plan hash value: 3493079762  
171.   
172. ---------------------------------------------------------------------------------------  
173. | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
174. ---------------------------------------------------------------------------------------  
175. |   0 | SELECT STATEMENT       |              |     1 |    16 | 72529   (1)| 00:14:31 |  
176. |   1 |  SORT AGGREGATE        |              |     1 |    16 |            |          |  
177. |*  2 |   HASH JOIN            |              |  2416 | 38656 | 72529   (1)| 00:14:31 |  
178. |*  3 |    INDEX SKIP SCAN     | INX_ID_OWNER |  2416 | 26576 | 72491   (1)| 00:14:30 |  
179. |   4 |    INDEX FAST FULL SCAN| INX_ID       | 72475 |   353K|    37   (0)| 00:00:01 |  
180. ---------------------------------------------------------------------------------------  
181.   
182. Predicate Information (identified by operation id):  
183. ---------------------------------------------------  
184.   
185.    2 - access("T1"."ID"="T2"."ID")  
186.    3 - access("T1"."OWNER"='SYS')  
187.        filter("T1"."OWNER"='SYS')  
188.   
189.   
190. Statistics  
191. ----------------------------------------------------------  
192.           1  recursive calls  
193.           0  db block gets  
194.         387  consistent gets  
195.           0  physical reads  
196.           0  redo size  
197.         424  bytes sent via SQL*Net to client  
198.         420  bytes received via SQL*Net from client  
199.           2  SQL*Net roundtrips to/from client  
200.           0  sorts (memory)  
201.           0  sorts (disk)  
202.           1  rows processed  
203.   
204. 如果owner列作为引导列，那么优化器就可以选择index range scan,这样相比index full scan, index fast full scan  
205. 肯定要少扫描很多leaf block,逻辑读就会相对较少。  
206.   
207. 其实到这里，是否可以总结一下建立组合索引的原则呢？  
208. 引导列要选择过滤条件的列作为引导列，比如 where a.xxx='xxx' 或者 a.xxx> 或者 a.xxx<  
209. 引导列的选择性越高越好，因为选择性越高，扫描的leaf block就越少，效率就越高  
210. 尽量把join列放到组合索引最后面  
211.   
212. 这里选择以owner列作为引导列，由于owner选择性很低，所以测试索引压缩对于性能的提升  
213.   
214. SQL> analyze  index inx_owner_id validate structure;  
215.   
216. Index analyzed.  
217.   
218. SQL> select height,  
219.   2         blocks,  
220.   3         lf_blks,  
221.   4         br_blks,  
222.   5         OPT_CMPR_COUNT,  
223.   6         OPT_CMPR_PCTSAVE  
224.   7    from index_stats  
225.   8   where name = 'INX_OWNER_ID';  
226.   
227.     HEIGHT     BLOCKS    LF_BLKS    BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE  
228. ---------- ---------- ---------- ---------- -------------- ----------------  
229.          2        256        219          1              1               26  
230.    
231. SQL> drop index inx_owner_id;  
232.   
233. Index dropped  
234.   
235. SQL> create index inx_owner_id on t1(owner,id) compress 1;  
236.   
237. Index created  
238.   
239. SQL> analyze  index inx_owner_id validate structure;  
240.   
241. Index analyzed  
242.   
243. SQL>   
244. SQL> select height,  
245.   2         blocks,  
246.   3         lf_blks,  
247.   4         br_blks,  
248.   5         OPT_CMPR_COUNT,  
249.   6         OPT_CMPR_PCTSAVE  
250.   7    from index_stats  
251.   8   where name = 'INX_OWNER_ID';  
252.   
253.     HEIGHT     BLOCKS    LF_BLKS    BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE  
254. ---------- ---------- ---------- ---------- -------------- ----------------  
255.          2        256        161          1              1                0  
256.            
257. 索引压缩之后，Leaf block 由原来的219降低到161个，节约了58个block 现在再来看一看执行计划+统计信息  
258.   
259. SQL> select count(*) from t1,t2 where t1.id=t2.id and t1.owner='SCOTT';  
260.   
261. Elapsed: 00:00:00.03  
262.   
263. Execution Plan  
264. ----------------------------------------------------------  
265. Plan hash value: 277464349  
266.   
267. ---------------------------------------------------------------------------------------  
268. | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
269. ---------------------------------------------------------------------------------------  
270. |   0 | SELECT STATEMENT       |              |     1 |    16 |    45   (3)| 00:00:01 |  
271. |   1 |  SORT AGGREGATE        |              |     1 |    16 |            |          |  
272. |*  2 |   HASH JOIN            |              |  2416 | 38656 |    45   (3)| 00:00:01 |  
273. |*  3 |    INDEX RANGE SCAN    | INX_OWNER_ID |  2416 | 26576 |     7   (0)| 00:00:01 |  
274. |   4 |    INDEX FAST FULL SCAN| INX_ID       | 72475 |   353K|    37   (0)| 00:00:01 |  
275. ---------------------------------------------------------------------------------------  
276.   
277. Predicate Information (identified by operation id):  
278. ---------------------------------------------------  
279.   
280.    2 - access("T1"."ID"="T2"."ID")  
281.    3 - access("T1"."OWNER"='SCOTT')  
282.   
283.   
284. Statistics  
285. ----------------------------------------------------------  
286.           1  recursive calls  
287.           0  db block gets  
288.         169  consistent gets  
289.           0  physical reads  
290.           0  redo size  
291.         422  bytes sent via SQL*Net to client  
292.         420  bytes received via SQL*Net from client  
293.           2  SQL*Net roundtrips to/from client  
294.           0  sorts (memory)  
295.           0  sorts (disk)  
296.           1  rows processed  
297.   
298. 由此可见，索引压缩之后，逻辑读并没有下降，还是169，但是索引的leaf blcok显著减少了，这样减少了存储空间，能降低物理IO