17,377
社区成员
发帖
与我相关
我的任务
分享SQL效率问题,OR 还是UNION
以下两句SQL哪个更好些,效率高些??
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE((blck_cd = 1
AND svc_cl_cd = 1) OR(blck_cd = 1
AND svc_cl_cd = 2) OR(blck_cd = 2
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 3
AND svc_cl_cd = 1) OR(blck_cd = 3
AND svc_cl_cd = 2) OR(blck_cd = 4
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 5
AND svc_cl_cd = 1) OR(blck_cd = 5
AND svc_cl_cd = 2) OR(blck_cd = 6
AND svc_cl_cd = 1) OR(blck_cd = 6
AND svc_cl_cd = 2) OR(blck_cd = 7
AND svc_cl_cd = 1) OR(blck_cd = 7
AND svc_cl_cd = 2) OR(blck_cd = 8
AND svc_cl_cd = 1) OR(blck_cd = 8
AND svc_cl_cd = 2) OR(blck_cd = 9
AND svc_cl_cd = 1) OR(blck_cd = 9
AND svc_cl_cd = 2) OR(blck_cd = 10
AND svc_cl_cd = 1) OR(blck_cd = 10
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE((blck_cd = 1
AND svc_cl_cd = 1) OR(blck_cd = 1
AND svc_cl_cd = 2) OR(blck_cd = 2
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 3
AND svc_cl_cd = 1) OR(blck_cd = 3
AND svc_cl_cd = 2) OR(blck_cd = 4
AND svc_cl_cd = 1) OR(blck_cd = 2
AND svc_cl_cd = 2) OR(blck_cd = 5
AND svc_cl_cd = 1) OR(blck_cd = 5
AND svc_cl_cd = 2) OR(blck_cd = 6
AND svc_cl_cd = 1) OR(blck_cd = 6
AND svc_cl_cd = 2) OR(blck_cd = 7
AND svc_cl_cd = 1) OR(blck_cd = 7
AND svc_cl_cd = 2) OR(blck_cd = 8
AND svc_cl_cd = 1) OR(blck_cd = 8
AND svc_cl_cd = 2) OR(blck_cd = 9
AND svc_cl_cd = 1) OR(blck_cd = 9
AND svc_cl_cd = 2) OR(blck_cd = 10
AND svc_cl_cd = 1) OR(blck_cd = 10
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 2
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 3
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 4
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 5
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 6
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 7
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 8
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 9
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 1)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
UNION
SELECT blck_cd,
svc_cl_cd,
dev_typ_cd,
dev_mdl_cd,conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 10
AND svc_cl_cd = 2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
...全文
请发表友善的回复…
发表回复
oracledbalgtu 2008-01-30
- 打赏
- 举报
使用临时表最好,把所有的or的数据放到临时表.
_liangzi_ 2008-01-29
- 打赏
- 举报
把sql语句改成这样：
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1 or blck_cd = 2)
and (svc_cl_cd = 1 or svc_cl_cd = 2
or svc_cl_cd = 3 or svc_cl_cd = 4
or svc_cl_cd = 5 or svc_cl_cd = 6
or svc_cl_cd = 7 or svc_cl_cd = 8
or svc_cl_cd = 9 or svc_cl_cd = 10)
and dev_typ_cd = '905' and dev_mdl_cd = '1208';
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE(blck_cd = 1 or blck_cd = 2)
and (svc_cl_cd = 1 or svc_cl_cd = 2
or svc_cl_cd = 3 or svc_cl_cd = 4
or svc_cl_cd = 5 or svc_cl_cd = 6
or svc_cl_cd = 7 or svc_cl_cd = 8
or svc_cl_cd = 9 or svc_cl_cd = 10)
and dev_typ_cd = '905' and dev_mdl_cd = '1208';
sulins 2008-01-29
- 打赏
- 举报
楼上提供的SQL不够严谨，若blck_cd字段的值为浮点型，比如：3.5。
建议SQL：
若非要比较二者的性能，建议把“UNION”改为“UNION ALL”。
建议SQL：
SELECT conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE blck_cd IN (1,2,3,4,5,6,7,8,9,10) AND svc_cl_cd IN (1,2)
AND dev_typ_cd = '905'
AND dev_mdl_cd = '1208'
若非要比较二者的性能，建议把“UNION”改为“UNION ALL”。
DragonBill 2008-01-29
- 打赏
- 举报
这两个SQL和
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE blck_cd BETWEEN 1 AND 10 AND svc_cl_cd IN (1, 2)
AND dev_typ_cd = '905' AND dev_mdl_cd = '1208'
是等价的
SELECT
conf_param_def_file_path
FROM t_re_conf_param_def_mtrx
WHERE blck_cd BETWEEN 1 AND 10 AND svc_cl_cd IN (1, 2)
AND dev_typ_cd = '905' AND dev_mdl_cd = '1208'
是等价的
ydlchina 2008-01-28
- 打赏
- 举报
差不多
Andy__Huang 2008-01-27
- 打赏
- 举报
是第一个快些,第一个只有一个select .... from tb ....
说明它只有编译一句;
而第二个用多个union 连接select ....from tb...
要把多个select语句连接起来一起编译,要的时间多
oracle编译语句是最花费时间的,
编译好一次后,以后再执行这样的语句就不需要再编译
说明它只有编译一句;
而第二个用多个union 连接select ....from tb...
要把多个select语句连接起来一起编译,要的时间多
oracle编译语句是最花费时间的,
编译好一次后,以后再执行这样的语句就不需要再编译
hailang_maxu 2008-01-27
- 打赏
- 举报
如果第一个使用提示索引合并,应该是第一个高些,
Croatia 2008-01-25
- 打赏
- 举报
应该是第一个语句快。
假如在相关字段上建立索引，我相信应该更加快。
UNION会默认执行SORT操作。
UNION执行的是ROWID的全检索，假如这次相关字段上有索引，肯定快过UNION的动作。
UNION还需要重新安排一些内存来保存临时的纪录。而OR不需要。所以，花掉的COST远低于UNION。
假如在相关字段上建立索引，我相信应该更加快。
UNION会默认执行SORT操作。
UNION执行的是ROWID的全检索，假如这次相关字段上有索引，肯定快过UNION的动作。
UNION还需要重新安排一些内存来保存临时的纪录。而OR不需要。所以，花掉的COST远低于UNION。
sinusoid 2008-01-25
- 打赏
- 举报
假设OR 与UNION出来的结果集一致，那么OR会比UNION快
1.UNION来求出结果集，并做重复的记录过滤
2.OR 某条件满足，即可以忽略之后的条件
1.UNION来求出结果集，并做重复的记录过滤
2.OR 某条件满足，即可以忽略之后的条件
bobfang 2008-01-25
- 打赏
- 举报
to fenixshadow:
我所说的or与union all等价是指相同列等于不同值的or连接。对于不同列的or条件通常情况下是无法转换为union all的，也无法转换为union的。
你所举的例子是假设结果集中没有重复数据，所以是可以转换为union，但如果没有执行查询前并不能确定记录集中是否有重复数据，那么转换为union后就过滤掉了那些重复数据。
我所说的or与union all等价是指相同列等于不同值的or连接。对于不同列的or条件通常情况下是无法转换为union all的，也无法转换为union的。
你所举的例子是假设结果集中没有重复数据，所以是可以转换为union，但如果没有执行查询前并不能确定记录集中是否有重复数据，那么转换为union后就过滤掉了那些重复数据。
bobfang 2008-01-24
- 打赏
- 举报
逻辑上or与union是不等价的。or可以改写为union all，但不能改写为union，所以效率高低就不可比。
CathySun118 2008-01-24
- 打赏
- 举报
or 和unnion的效率是一个数量级的
凤影 2008-01-24
- 打赏
- 举报
楼上两颗星哦，说话更要负责任了。
举例如下：
create table tablea
(
code int,
name int
)
insert into tablea values (1,2);
select a.code,a.name from tablea a where a.code =1 or a.name =2;返回一条记录
逻辑上等价于
select a.code,a.name from tablea a where a.code =1
union
select a.code,a.name from tablea a where a.name =2;
返回一条记录。
而不等价于
select a.code,a.name from tablea a where a.code =1
union all
select a.code,a.name from tablea a where a.name =2;
返回两条记录。
举例如下：
create table tablea
(
code int,
name int
)
insert into tablea values (1,2);
select a.code,a.name from tablea a where a.code =1 or a.name =2;返回一条记录
逻辑上等价于
select a.code,a.name from tablea a where a.code =1
union
select a.code,a.name from tablea a where a.name =2;
返回一条记录。
而不等价于
select a.code,a.name from tablea a where a.code =1
union all
select a.code,a.name from tablea a where a.name =2;
返回两条记录。
凤影 2008-01-23
- 打赏
- 举报
实践是检验真理的唯一标准。
性能调整的第一法则：没有度量不要调整。
性能调整的第二法则：没有度量不要调整。
性能调整的第三法则：一次只调整一个地方。
性能调整的第一法则：没有度量不要调整。
性能调整的第二法则：没有度量不要调整。
性能调整的第三法则：一次只调整一个地方。
popeye627 2008-01-23
- 打赏
- 举报
or 或者 in 比 union 效率高很多
如果table中數據量大，而且沒有索引或者索引不得儅的話，union 那是相當的慢的
（我在MSSQL中測過(這兩天剛好修改了個這方面的程序)，90W筆數據，如果沒有添加索引的話，我的SQL有9個union，執行下來差不多8分鐘，用in大概40秒左右；當然如果索引的黨的話，兩者基本上不相上下，1秒左右就完了）
如果table中數據量大，而且沒有索引或者索引不得儅的話，union 那是相當的慢的
（我在MSSQL中測過(這兩天剛好修改了個這方面的程序)，90W筆數據，如果沒有添加索引的話，我的SQL有9個union，執行下來差不多8分鐘，用in大概40秒左右；當然如果索引的黨的話，兩者基本上不相上下，1秒左右就完了）
dawugui 2008-01-22
- 打赏
- 举报
or , union
都差不多.
都差不多.
中国风 2008-01-22
- 打赏
- 举报
第二个少了重新编译，第二个要高一点。。。
or--编译时MS_SQL编译了union
--oracle不太清楚
or--编译时MS_SQL编译了union
--oracle不太清楚
mantisXF 2008-01-22
- 打赏
- 举报
好像第一个的效率高一些吧 ..
EANFS 2008-01-22
- 打赏
- 举报
第一条的优化结果
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "8" "5" "165" "" "" "" "" ""
"TABLE ACCESS(FULL) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "8" "5" "165" "" "" "" "" ""DEV_MDL_CD"=1208 AND "DEV_TYP_CD"=905 AND ("BLCK_CD"=1 AND "SVC_CL_CD"=1 OR "BLCK_CD"=1 AND "SVC_CL_CD"=2 OR "BLCK_CD"=2 AND "SVC_CL_CD"=1 OR "BLCK_CD"=2 AND "SVC_CL_CD"=2 OR "BLCK_CD"=3 AND "SVC_CL_CD"=1 OR "BLCK_CD"=3 AND "SVC_CL_CD"=2 OR "BLCK_CD"=4 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=2 OR "BLCK_CD"=6 AND "SVC_CL_CD"=1 OR "BLCK_CD"=6 AND "SVC_CL_CD"=2 OR "BLCK_CD"=7 AND "SVC_CL_CD"=1 OR "BLCK_CD"=7 AND "SVC_CL_CD"=2 OR "BLCK_CD"=8 AND "SVC_CL_CD"=1 OR "BLCK_CD"=8 AND "SVC_CL_CD"=2 OR "BLCK_CD"=9 AND "SVC_CL_CD"=1 OR "BLCK_CD"=9 AND "SVC_CL_CD"=2 OR "BLCK_CD"=10 AND "SVC_CL_CD"=1 OR "BLCK_CD"=10 AND "SVC_CL_CD"=2 OR "BLCK_CD"=11 AND "SVC_CL_CD"=1 OR "BLCK_CD"=11 AND "SVC_CL_CD"=2 OR "BLCK_CD"=12 AND "SVC_CL_CD"=1 OR "BLCK_CD"=12 AND "SVC_CL_CD"=2 OR "BLCK_CD"=13 AND "SVC_CL_CD"=1 OR "BLCK_CD"=13 AND "SVC_CL_CD"=2 OR "BLCK_CD"=14 AND "SVC_CL_CD"=1 OR "BLCK_CD"=14 AND "SVC_CL_CD"=2 OR "BLCK_CD"=15 AND "SVC_CL_CD"=1 OR "BLCK_CD"=15 AND "SVC_CL_CD"=2 OR "BLCK_CD"=16 AND "SVC_CL_CD"=1 OR "BLCK_CD"=16 AND "SVC_CL_CD"=2 OR "BLCK_CD"=17 AND "SVC_CL_CD"=1 OR "BLCK_CD"=17 AND "SVC_CL_CD"=2 OR "BLCK_CD"=18 AND "SVC_CL_CD"=1 OR "BLCK_CD"=18 AND "SVC_CL_CD"=2 OR "BLCK_CD"=19 AND "SVC_CL_CD"=1 OR "BLCK_CD"=19 AND "SVC_CL_CD"=2 OR "BLCK_CD"=20 AND "SVC_CL_CD"=1 OR "BLCK_CD"=20 AND "SVC_CL_CD"=2)"
第二条的优化结果
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "132" "44" "1452" "" "" "" "" ""
"SORT(UNIQUE)" "" "132" "44" "1452" "" "" "" "" ""
"UNION-ALL" "" "" "" "" "" "" "" "" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "8" "5" "165" "" "" "" "" ""
"TABLE ACCESS(FULL) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "8" "5" "165" "" "" "" "" ""DEV_MDL_CD"=1208 AND "DEV_TYP_CD"=905 AND ("BLCK_CD"=1 AND "SVC_CL_CD"=1 OR "BLCK_CD"=1 AND "SVC_CL_CD"=2 OR "BLCK_CD"=2 AND "SVC_CL_CD"=1 OR "BLCK_CD"=2 AND "SVC_CL_CD"=2 OR "BLCK_CD"=3 AND "SVC_CL_CD"=1 OR "BLCK_CD"=3 AND "SVC_CL_CD"=2 OR "BLCK_CD"=4 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=1 OR "BLCK_CD"=5 AND "SVC_CL_CD"=2 OR "BLCK_CD"=6 AND "SVC_CL_CD"=1 OR "BLCK_CD"=6 AND "SVC_CL_CD"=2 OR "BLCK_CD"=7 AND "SVC_CL_CD"=1 OR "BLCK_CD"=7 AND "SVC_CL_CD"=2 OR "BLCK_CD"=8 AND "SVC_CL_CD"=1 OR "BLCK_CD"=8 AND "SVC_CL_CD"=2 OR "BLCK_CD"=9 AND "SVC_CL_CD"=1 OR "BLCK_CD"=9 AND "SVC_CL_CD"=2 OR "BLCK_CD"=10 AND "SVC_CL_CD"=1 OR "BLCK_CD"=10 AND "SVC_CL_CD"=2 OR "BLCK_CD"=11 AND "SVC_CL_CD"=1 OR "BLCK_CD"=11 AND "SVC_CL_CD"=2 OR "BLCK_CD"=12 AND "SVC_CL_CD"=1 OR "BLCK_CD"=12 AND "SVC_CL_CD"=2 OR "BLCK_CD"=13 AND "SVC_CL_CD"=1 OR "BLCK_CD"=13 AND "SVC_CL_CD"=2 OR "BLCK_CD"=14 AND "SVC_CL_CD"=1 OR "BLCK_CD"=14 AND "SVC_CL_CD"=2 OR "BLCK_CD"=15 AND "SVC_CL_CD"=1 OR "BLCK_CD"=15 AND "SVC_CL_CD"=2 OR "BLCK_CD"=16 AND "SVC_CL_CD"=1 OR "BLCK_CD"=16 AND "SVC_CL_CD"=2 OR "BLCK_CD"=17 AND "SVC_CL_CD"=1 OR "BLCK_CD"=17 AND "SVC_CL_CD"=2 OR "BLCK_CD"=18 AND "SVC_CL_CD"=1 OR "BLCK_CD"=18 AND "SVC_CL_CD"=2 OR "BLCK_CD"=19 AND "SVC_CL_CD"=1 OR "BLCK_CD"=19 AND "SVC_CL_CD"=2 OR "BLCK_CD"=20 AND "SVC_CL_CD"=1 OR "BLCK_CD"=20 AND "SVC_CL_CD"=2)"
第二条的优化结果
"Optimizer" "Cost" "Cardinality" "Bytes" "Partition Start" "Partition Stop" "Partition Id" "ACCESS PREDICATES" "FILTER PREDICATES"
"SELECT STATEMENT" "ALL_ROWS" "132" "44" "1452" "" "" "" "" ""
"SORT(UNIQUE)" "" "132" "44" "1452" "" "" "" "" ""
"UNION-ALL" "" "" "" "" "" "" "" "" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=1 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=2 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=3 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=4 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=5 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=1 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
"TABLE ACCESS(BY INDEX ROWID) NGN.T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "2" "1" "33" "" "" "" "" ""
"INDEX(UNIQUE SCAN) NGN.PK_T_RE_CONF_PARAM_DEF_MTRX" "ANALYZED" "1" "1" "" "" "" "" ""BLCK_CD"=6 AND "SVC_CL_CD"=2 AND "DEV_TYP_CD"=905 AND "DEV_MDL_CD"=1208" ""
vc555 2008-01-22
- 打赏
- 举报
你要先执行下，看看执行计划，还看看用的是什么优化器。
