关于 Oracle Sql 优化

Question

create or replace view vXXX
(a,b)
as
select a1,b1 from t1
union
select a2,b2 from t2然后，比如：
select * from vXXX where b < 3请问：where后面的条件是否会加载到union的每一个select语句中。也就是说，通过上面的view来查询和通过下面的sql来查询，速度会有多大差别？（t1和t2都是大表）select a1,b1 from t1 where b1 < 3
union
select a2,b2 from t2 where b2 < 3

Answer 1

两个查询得到得结果不同的。create view tmp2
as
select * from tmp
union
select * from tmp1
15:33:35 SQL> select * from tmp2 where rownum < 4
15:33:46   2  /NAMES                课程                      分数
-------------------- -------------------- ---------
a                    数学                        90
a                    英语                        85
a                    语文                        62 real: 47
15:33:48 SQL> select * from tmp where rownum < 4
15:34:10   2  union
15:34:14   3  select * from tmp1 where rownum < 4
15:34:25   4  /NAMES                课程                      分数
-------------------- -------------------- ---------
a                    语文                        62
b                    语文                        70
c                    语文                        80 real: 63
15:34:25 SQL> 第一种用view的效率高点。

Answer 2

如果业务允许将union改成 union all速度将有明显提高!

Answer 3

……，
to Eric_1999(╙@^@╜)：
为什么结果不同？？？
第一种用view效率高？我试的结果是第二种高一些……

Answer 4

to Eric_1999(╙@^@╜)：
你用where rownum < 4过滤，结果当然不同了。

Answer 5

create view tmp2
as
select *  --以列名代替＊，效果更佳，
from(select * from tmp
     union
     select * from tmp1)这是我碰见的情况，oracle 9.2

Answer 6

加分，希望同志们继续讨论。to jdsnhan(柳荫凉)：
确实，我想最好能够定量测试下，效率能高多少。
我这里似乎能提高25％左右（多大的视图我不知道，查了半天没反应，估计太大……）

Answer 7

select * from vXXX where b < 3;
与
select a1,b1 from t1 where b1 < 3
union
select a2,b2 from t2 where b2 < 3;
这两个语句是完全不同的，无论是执行顺序或者执行结果.所以上面的两个语句是完全不能同等使用的，而性能也完全不同，究竟谁性能与表参数有关,我举例一下:
create or replace view test_1 as select * from labelconfigure union select * from test;
 select * from test_1 where rownum < 10;
的执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS
 COUNT STOPKEY
  VIEW Object owner=UT_BOSS
   SORT UNIQUE STOPKEY
    UNION-ALL
     TABLE ACCESS FULL
     TABLE ACCESS FULL而select * from labelconfigure where rownum < 10 union select * from test where rownum < 10;的执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS
 SORT UNIQUE
  UNION-ALL
   COUNT STOPKEY
    TABLE ACCESS FULL
   COUNT STOPKEY
    TABLE ACCESS FULL相对来说，如果索引建立正确的话视图的效率高一些.select * from vXXX where b < 3;
实际上与这个语句一样
select * from (select select a1,b1 from union select a2,b2 from t2) where rownum < 3;

Answer 8

select * from vXXX where b < 3;
实际上与这个语句一样
select * from (select select a1,b1 from union select a2,b2 from t2) where rownum < 3;
——————————————————————————————————————————
如果是视图的话，“b < 3”就变成“rownum < 3”了？？？——非常疑惑的问-_-

Answer 9

Sorry,我大意了完整的应该为:select a, b from (select select a1 a,b1 b from union select a2 a,b2 b from t2) where b < 3;

Answer 10

晕，自从上面老兄提到 rownum ，我的帖子……55555～～～
再请ColinGan(浪子)分析一下，谢谢。

Answer 11

我把上面的条件弄错了，不好意思,
实际上你想分析性能问题的话,最好的就是explain

Answer 12

1. view 的本质就是sql语句
2. 不管是否使用了rownum, rowid等，
   select * from v_view where col1 = ...
等价于：
select * from (
--创建视图的select 语句
select a1 as a, b1 as b from t1
union
select a2 as a, b2 as b from t2
) where b = ...
而不一定等价于：
select a1 as a, b1 as b from t1 where b1 = ...
union
select a2 as a, b2 as b from t2 where b2 = ...
仅当创建视图的select语句中没有使用rownum等字段，且在视图中进行查询的条件中没有使用rownum作为查询条件的时候，才有可能是等价的

Answer 13

1、建义楼主分析sql性能前，参考执行计划，对你本人极大提高.
2、对于楼主采用第二条语句比视图更为好(数据量级，建立了索引更为体现)
3、不考虑重复记录，union -> union all性能大大提高.

Answer 14

非常感谢各位。声明，rownum不作为我的查询条件！！！
为什么我要做选择呢？是因为：
1）view，这样的话，我就可以把view写入数据库中，而我的程序就简单了。我的程序有大量的这种情况。同时，如果性能确实太慢，那我肯定得想其他办法。
2）自己构建sql，这样的话，程序就很复杂了，我的程序中写大量的sql，容易出错，且修改起来不方便。我的解决方法：
1）建立多个view，将每个union的一部分建立一个view，这样，既能拥有较好的查询速度，又能简化程序中的sql构建代码。
2）采用函数（oracle存储过程）返回记录集的方法，将参数传入函数中，然后函数自动将where添加加载到每个union的分量上，然后返回记录集。请大家指教。

Answer 15

还是用union吧，你的解决方法很好，另外如果要加快速度的话，从索引入手，你会有很大的收获地