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

2/9/2008来源:Oracle教程人气:8282


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