在應(yīng)用過(guò)程中，我們偶然會(huì)碰到想要在增加index前看看該index是否能帶來(lái)預(yù)期的效果。為了達(dá)到這個(gè)目標(biāo)，我們可以使用oem的virtual index的功能。為了能讓大家比較簡(jiǎn)單地了解這個(gè)功能，我們?yōu)榇蠹已菔疽幌虏辉趏em下virtual index的使用。 另外，即然叫virtual index， 那么當(dāng)建立它時(shí)，應(yīng)當(dāng)不耗資源的。這對(duì)于很大的表，當(dāng)我們想看建某個(gè)index是否能改善當(dāng)前語(yǔ)句的執(zhí)行計(jì)劃時(shí)，顯然很有幫助。我們來(lái)看例子：
有一news表，400多M。有語(yǔ)句：
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
分析條件后，我們認(rèn)為在gmt_origin上建index可能會(huì)有幫助，然而我們并不清楚有于其他indexes的影響，當(dāng)我們建了基于gmt_origin的index時(shí)，是否的確會(huì)有幫助。
于是我們預(yù)備在gmt_origin上建個(gè)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看出，建立它的確是不耗多少資源。
為了能利用這個(gè)virtual index,我們還必須修改當(dāng)前session參數(shù)：
Code: [Copy to clipboard]
SQL> alter session set "_use_nosegment_indexes" = true;
Session altered.
現(xiàn)在我們來(lái)看看效果：
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.
執(zhí)行計(jì)劃顯示它的確用到了我們預(yù)備建的index。
設(shè)置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
