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

[abstruse]

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秒
结论： 视图放在查询内层的效率比较高，如果视图放在查询的外层效率比较低。原因暂时不明.请各位高手分析一下该结论,谢谢

[gentlePenguin]

顶

[dinya2003]

广告时间..马上回来.

[dinya2003]

在上面的几个查询中,子查询都将不使用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

[dinya2003]

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