在应用过程中，我们偶尔会碰到想要在增加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
