通过Oracle虚拟索引决定是否建立索引

　　在应用过程中，我们偶尔会碰到想要在增加index前看看该index是否能带来预期的效果。为了达到这个目标，我们可以使用oem的virtual index的功能。为了能让大家比较简单地了解这个功能，我们为大家演示一下不在oem下virtual index的使用。另外，即然叫virtual index， 那么当建立它时，应当不耗资源的。这对于很大的表，当我们想看建某个index是否能改善当前语句的执行计划时，显然很有帮助。我们来看例子：
　　　
　　有一news表，400多M。有语句：
　　
　　　Code: [Copy to clipboard]　
　　
　　select count(*) from News　
　　　where Gmt_Origin >= trunc(sysdate + 0.5)
　　　 and Gmt_Origin < trunc(sysdate + 0.5) + 1　
　　　 and news_type_general = 'y'
　　　 and ((News_Category_Id IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9))　
　　　　 OR (News_Category_Id_2 IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9))　
　　　　 OR (News_Category_Id_3 IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9)));
　　
　　它使用了index，然而效果并不理想：
　　
　　　Code: [Copy to clipboard]　
　　
　　Operation　Object Name　Rows　Bytes　Cost　Object Node　In/Out　PStart　PStop
　　　
　　SELECT STATEMENT Hint=CHOOSE　　1　　　 267　　　　　　　
　　　 SORT AGGREGATE　　1　　28　　　　　　　　　
　　　　 FILTER　　　　　　　　　　　　
　　　　　 TABLE ACCESS BY INDEX ROWID　NEWS　1　　28　　267　　　　　　　
　　　　　　 INDEX RANGE SCAN　NEWS_RELATED3_IND　1　　　 266　　　　　　　
　　　
　　分析条件后，我们认为在gmt_origin上建index可能会有帮助，然而我们并不清楚有于其他indexes的影响，当我们建了基于gmt_origin的index时，是否的确会有帮助。
　　
　　于是我们准备在gmt_origin上建个virtual index:
　　
　　　Code: [Copy to clipboard]　
　　
　　SQL> alter session set sql_trace = true;
　　　
　　Session altered.
　　　
　　SQL>　create index news_gorigin_ind on news(gmt_origin)　nosegment ;　
　　　
　　Index created.
　　
　　trace是：
　　
　　　Code: [Copy to clipboard]　
　　
　　create index news_gorigin_ind on news(gmt_origin)　nosegment
　　　
　　call　　 count　　　 cpu　　elapsed　　　 disk　　　query　　current　　　　rows
　　------- ------　-------- ---------- ---------- ---------- ----------　----------
　　Parse　　　　1　　　0.06　　　 0.08　　　　　2　　　　　4　　　　　0　　　　　 0
　　Execute　　　1　　　0.02　　　 0.42　　　　　0　　　　　2　　　　　2　　　　　 0
　　Fetch　　　　0　　　0.00　　　 0.00　　　　　0　　　　　0　　　　　0　　　　　 0
　　------- ------　-------- ---------- ---------- ---------- ----------　----------
　　total　　　　2　　　0.08　　　 0.50　　　　　2　　　　　6　　　　　2　　　　　 0
　　　
　　Misses in library cache during parse: 1
　　Optimizer goal: CHOOSE
　　
　　我们可以从tracer看出，建立它的确是不耗多少资源。
　　　
　　为了能利用这个virtual index,我们还必须修改当前session参数：
　　
　　Code: [Copy to clipboard]　
　　
　　SQL>　alter　session set "_use_nosegment_indexes" = true;
　　　
　　Session altered.
　　
　　现在我们来看看效果：
　　
　　SQL> explain plan for　
　　　 2　select count(*) from News　
　　　 3　 where Gmt_Origin >= trunc(sysdate + 0.5)
　　　 4　　and Gmt_Origin < trunc(sysdate + 0.5) + 1　
　　　 5　　and news_type_general = 'y'
　　　 6　　and ((News_Category_Id IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9))　
　　　 7　　　OR (News_Category_Id_2 IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9))　
　　　 8　　　OR (News_Category_Id_3 IN (100901,5002640,5002641,100902,5000042,5001060,5002041,9)))
　　　 9　/
　　　
　　Explained.
　　　
　　SQL>　@$ORACLE_HOME/rdbms/admin/utlxpls
　　　
　　PLAN_TABLE_OUTPUT
　　------------------------------------------------------------------------------------------------------------------------
　　　
　　-----------------------------------------------------------------------------------
　　| Id　| Operation　　　　　　　　　　 |　Name　　　　　　 | Rows　| Bytes | Cost　|
　　-----------------------------------------------------------------------------------
　　|　 0 | SELECT STATEMENT　　　　　　　|　　　　　　　　　 |　　 1 |　　20 |　　 3 |
　　|　 1 |　SORT AGGREGATE　　　　　　　 |　　　　　　　　　 |　　 1 |　　20 |　　　 |
　　|　 2 |　 FILTER　　　　　　　　　　　|　　　　　　　　　 |　　　 |　　　 |　　　 |
　　|　 3 |　　TABLE ACCESS BY INDEX ROWID| NEWS　　　　　　　|　　 1 |　　20 |　　 3 |
　　|　 4 |　　 INDEX RANGE SCAN　　　　　| NEWS_GORIGIN_IND　|　　 3 |　　　 |　　 2 |
　　-----------------------------------------------------------------------------------
　　　
　　Note: cpu costing is off, 'plan_table' is old version
　　　
　　12 rows selected.
　　　
　　执行计划显示它的确用到了我们准备建的index。
　　
　　设置10053看看
　　
　　-- Index stats
　　　 INDEX NAME: NEWS_GMODIFIED_IND　COL#: 3
　　　　 TOTAL ::　LVLS: 2　 #LB: 1295　#DK: 21318　LB/K: 1　DB/K: 8　CLUF: 186911
　　　 INDEX NAME: NEWS_GORIGIN_IND　COL#: 7
　　　　 TOTAL ::　LVLS: 1　 #LB: 25　#DK: 100　LB/K: 1　DB/K: 1　CLUF: 800
　　...
　　　Access path: index (scan)
　　　　　 Index: NEWS_GORIGIN_IND
　　　 TABLE: NEWS
　　　　　 RSC_CPU: 0　 RSC_IO: 3
　　　 IX_SEL:　4.5000e-03　TB_SEL:　1.1250e-05
　　　...
　　Access path: index (index-only)
　　　　　 Index: NEWS_GORIGIN_IND
　　　 TABLE: NEWS
　　　　　 RSC_CPU: 0　 RSC_IO: 2
　　　 IX_SEL:　4.5000e-03　TB_SEL:　4.5000e-03　
　　

查看本文来源