视图与表嵌套的查询的效率问题,原理何在?

Question

1:有表INSTITUTION , 同时为该表建立一个视图v_st_inst_inuse(create view v_st_inst_inuse as select * from institution).INSTITUTION中有8万条记录,institution_id 与,Chinese_Name都建立有索引.现在实验如下:
a: sql语句一
 select institution_id,FIRSTTRANS_DATE 
           from INSTITUTION 
           where institution_id in (
                        select  v_st_inst_inuse.institution_id
                           from  v_st_inst_inuse 
                        where lower( v_st_inst_inuse.Chinese_Name)like '%77%')
//运行次数四次,运行时间分别为 0.12秒 0.97秒 0.13秒 0.97秒 0.13秒
b: sql语句二
select institution_id,FIRSTTRANS_DATE 
           from INSTITUTION 
           where institution_id in (
                                     select  b.institution_id
                                            from  INSTITUTION b
                                            where lower( b.Chinese_Name)like '%77%')
//运行次数五次,运行时间分别为 1.53秒 1.68秒 1.00秒 0.97秒 1.69秒c: sql语句三
select institution_id,FIRSTTRANS_DATE 
           from INSTITUTION a
           where a.institution_id in (
                                     select  b.institution_id
                                            from  INSTITUTION b
                                            where lower( b.Chinese_Name)like '%77%')
//运行次数五次,运行时间分别为 1.16秒 1.85秒 1.00秒 1.16秒 1.69秒d: sql语句四
select CHINESE_NAME,SPELLING_NAME
           from v_st_inst_inuse 
           where institution_id in (
                                     select  b.institution_id
                                            from  INSTITUTION b
                                            where lower( b.Chinese_Name)like '%77%')
//运行次数三次,运行时间分别为 50.00秒 70.34秒 47.71秒
e: sql语句五
select CHINESE_NAME,SPELLING_NAME
           from v_st_inst_inuse 
           where institution_id in (
                                     select  b.institution_id
                                            from  v_st_inst_inuse b
                                            where lower( b.Chinese_Name)like '%77%')//运行次数三次,运行时间分别为54.03秒 54.09秒,54.80秒
结论： 视图放在查询内层的效率比较高，如果视图放在查询的外层效率比较低。原因暂时不明.请各位高手分析一下该结论,谢谢

Answer 1

在上面的几个查询中,子查询都将不使用chinese_name字段上的索引.表id ,name上都有索引:--子查询主查询都是表
select * from 表 where 表.id in (select id from 表 where 表.name like 'D%')
--执行计划如下:
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=20 Bytes=450
          0)   1    0   HASH JOIN (Cost=11 Card=20 Bytes=4500)
   2    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=
          220)   3    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=470 Bytes
          =100580)Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
         52  consistent gets
         25  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         17  rows processed--子查询是视图 主查询是表
select * from 表 where 表.id in (select id from 视图 where 视图.name like 'D%')
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=20 Bytes=450
          0)   1    0   HASH JOIN (Cost=11 Card=20 Bytes=4500)
   2    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=
          220)   3    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=470 Bytes
          =100580)Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
         54  consistent gets
         34  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         17  rows processed
--子查询是表 主查询是视图
select * from 视图 where 视图.id in (select id from 表 where 表.name like 'D%')
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=20 Bytes=450
          0)   1    0   HASH JOIN (Cost=11 Card=20 Bytes=4500)
   2    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=
          220)   3    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=470 Bytes
          =100580)Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
         54  consistent gets
         34  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         17  rows processed--子查询主查询都是视图
select * from 视图 where 视图.id in (select id from 视图 where 视图.name like 'D%')Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=20 Bytes=450
          0)   1    0   HASH JOIN (Cost=11 Card=20 Bytes=4500)
   2    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=
          220)   3    1     TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=470 Bytes
          =100580)Statistics
----------------------------------------------------------
         14  recursive calls
          8  db block gets
         56  consistent gets
         34  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         17  rows processed
select id from 表 where 表.name like 'D%'Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=20 Bytes=4280
          )   1    0   TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=42
          80)Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
         27  consistent gets
         29  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         17  rows processed
select id from 视图 where 视图.name like 'D%'Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=20 Bytes=4280
          )   1    0   TABLE ACCESS (FULL) OF 'FND_USER' (Cost=5 Card=20 Bytes=42
          80)Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
         29  consistent gets
         30  physical reads
          0  redo size
       5560  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         17  rows processed

Answer 2

结论:
    1、从执行计划来看,子查询中用了 like 'D%',系统将不会使用user_name上的索引.而是采用了全表扫描。
    2、表和视图作为子查询，执行计划是一样的.统计的数据也显示是一样的。楼主的问题是怎么回事，等待高手们的解答。
    3、以上测试oracle 8.1.7版本