请问如何提高如下sql语句的性能select C1,C2,R1, R2, R3 from table1 where C1
in
(
select DISTINCT R1 from table1
where C2 in(...)
)
or C1 in
(
select DISTINCT R2 from table1
where C2 in(...)
)
or C1 in
(
select DISTINCT R3 from table1
where C2 in(...)
)
select DISTINCT R3(或R1, R2) from table1
where C2 in(...同样条件)
同样的结果集重复查询了三次,而且作为同一个where的条件 有什么办法改进一下,比如重用结果集合,只查一次使用三次,
in
(
select DISTINCT R1 from table1
where C2 in(...)
)
or C1 in
(
select DISTINCT R2 from table1
where C2 in(...)
)
or C1 in
(
select DISTINCT R3 from table1
where C2 in(...)
)
select DISTINCT R3(或R1, R2) from table1
where C2 in(...同样条件)
同样的结果集重复查询了三次,而且作为同一个where的条件 有什么办法改进一下,比如重用结果集合,只查一次使用三次,
解决方案 »
- oracle数据库服务器突然断电造成(ORA-12528监听程序:所有适用都不允许建立新的连接。)
- 在线等。分组后取每一组条的第一条数据
- 【请教】eclipse中Java jdbc连接Oracle数据库,为什么语句需要在表名前加上数据库的表空间名称?
- 这样的sql查询合并怎么写
- sqlldr怎么导入数据到表的个别列?疑惑一天了。
- 这个简单的触发器为什么报错?
- 高手请进!!!!!
- 分数不多,在于知识 about create sequence !!!
- 如何把一个表的所有数据导入另外一个表?
- 关于自治事务的几句话,不太明白,请英语好的指点指点?
- 急,如何实现两台机器两个数据库的自动刷新?
- 存储过程中的 警告:过程被创建 有编译错误?????
in
(select DISTINCT R1 from table1
)
or C1 in
(
select DISTINCT R2 from table1
)
or C1 in
(
select DISTINCT R3 from table1
) and c1 in(.........)既然是同样的条件,当然可以拿出来
既然都是同一张表,还需要用嵌套的select吗?
select C1,C2,R1, R2, R3 from table1
where C1 in r1
or c1 in r2
or c1 in r3
and c2 in (...........)在更正刚才最后一个 c1应该是c2
where C2 in(...同样条件)加一个别名,作为一个结果集在where语句中使用多次,以提高效率
(
select 1 from table1 B where ( A.C1 = B.R1 OR A.C1 = B.R2 OR A.C1 = B.R3 ) AND C2 in(...)
)
在R1 , R2 , R3上有索引
FROM table1 a
WHERE EXISTS
(SELECT 1
FROM table1 b
WHERE a.c1 IN (b.r1,b.r2,b.r3)
AND b.c2 in (....) )
同意楼上的
是不是也可以如下做:
SELECT a.c1,a.c2,a.r1,a.r2,a.r3
FROM table1 a, table1 b
WHERE b.c2 in (....) and (a.c1=b.r1 or a.c1=b.r2 or a.c1=b.r3)
请多多指正!
FROM table1 a, table1 b
WHERE (a.c1=b.r1 or a.c1=b.r2 or a.c1=b.r3) and
b.c2 in (....) 最好将b.c2 in (....) 改成exists的形式.
用inner join代替in,exist
用union all代替or
看explain plan,建索引,避免full table access
FROM table1
WHERE (C1 = R1 OR C1 =R2 OR C1 =R3)
AND C2 IN (...)
select C1,C2,R1, R2, R3 from table1 C where
exists select 1 from
(
(
select DISTINCT R1 from table1
where C2 in(...)
)
union
(
select DISTINCT R2 from table1
where C2 in(...)
)
union
(
select DISTINCT R3 from table1
where C2 in(...)
)
) K
where C.c1=K.R1
select C1,C2,R1, R2, R3 from table1
(
select DISTINCT r1 from a
(
select DISTINCT R1,c2 from table1
union
select DISTINCT R2,c2 from table1
union
select DISTINCT R3,c2 from table1
) a
where a.c2 in(...)
) table2
where table1.C1 = table2.r1(+)
select C1,C2,R1, R2, R3 from table1,(select DISTINCT R1,R2,R3 from table1
where C2 in(...)) t where C1=t.R1 or C2=t.R2 or C1=t.R3
所谓表就是记录集
当然拉,你可以把记录集当做表使用
比方说:
select * from (select * from tab_1 where condition) x ,(select * from tab_2) y, tab_3 z
where condition;