多表查询优化

[lbd8848]

有表 a,b,c ,表a表b记录数比较大几百万条,表c只有几百条
表a id为主键 adddate作普通索引
=====================================================
id adddate sort1 sort2 ......
1 2009-5-12 00:00:01 aaaa bbbb
2 2009-5-12 10:00:01 eeee aaaa
3 2009-5-11 20:01:01 cccc bbbb
4 2009-5-10 10:20:01 bbbb eeee
5 2009-5-9 10:20:01 aaaa eeee
6 2009-5-8 10:20:01 1111 cccc
...
======================================================
表b id作普通索引 表a与表b的关系是一对一关系,通过id号关联.
==========================================
id content adddate
1 564565 2009-5-12 00:00:02
2 435435 2009-5-12 00:00:03
3 543543 2009-5-12 10:00:01
4 apopep 2009-5-11 20:01:01
.....
==========================================
表c sort作普通索引 表a中的sort1字段或sort2字段皆有可能与sort字段关联
==========================================
sort stat unit

aaaa 1 a
bbbb 1 b
cccc 1 c
eeee 2 e
==========================================

以上三表实现关联查询:
select * from a inner join b on a.id=b.id inner join c on (a.sort1=c.sort or a.sort2=c.sort) where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi') and a.adddate<=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi') order by adddate desc
但效率不高,
后来改后
select * from a inner join b on a.id=b.id inner join c on a.sort1=c.sort where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi') and a.adddate<=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi') union all
select * from a inner join b on a.id=b.id inner join c on a.sort2=c.sort where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi') and a.adddate<=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
使用union all连接,快了很多.但在后面加上 order by adddate desc就慢得不得了
不知有什么好的优惠方法,请指点!

[fuyou001]

顶下，期待高手

[阿三]

adddate 这个字段建个索引,再试试

[lbd8848]

adddate有索引的,

[oraclelogan]

select *
  from a
 inner join b on a.id = b.id
 inner join c on (a.sort1 = c.sort or a.sort2 = c.sort)
 where a.adddate > to_date('2008-12-31 00:00', 'yyyy-mm-dd hh24:mi')
   and a.adddate < to_date('2009-05-16 23:59', 'yyyy-mm-dd hh24:mi')
 order by adddate desc

[fosjos]

a.id是主键，b.id不是主键，至少也应该是unique index

关联顺序应该是(select * from a order by a.adddate) , b , c

select ... from (select * from a order by a.adddate) aa , b , c where ...
或者用inner join
看一下解释计划，还是不对的话加上hint试试

[mzl_mzl]

1.a表几百万记录，但如果你查询的adddate范围涉及到的记录非常大时，则a表字段sort1, sort2建索引，同时加提示sort1, sort2索引
如下语句：加ordered是强制执行计划先访问c表，然后nl嵌套循环走a表sort1或sort2索引访问a,再访问b
select x.id, x.adddate, x.sort1, x.sort2
from (
select /*+ ordered*/a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort1
union all
select /*+ ordered*/a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort2
) x
order by x.adddate desc
2.尽管union all方式a,b,c表访问了两遍，但此种情况比使用or实现效率高
3.如果a表查询的adddate范围涉及到的记录很少，则可不需建sort1, sort2索引，且取消上一sql的提示信息
即：
select x.id, x.adddate, x.sort1, x.sort2
from (
select a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort1
union all
select a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort2
) x
order by x.adddate desc
4.避免查询用*，这样会使得sql访问数据库字典得到表相应字段，增加了额外开销
5.个人认为你的表设计有问题,a,b两个表完全等价于一张表，可一张表实现

[fosjos]

漏了条件了
(select * from a where a.adddate<... and a.adddate>... order by a.adddate)

[mzl_mzl]

1.a表几百万记录，但如果你查询的adddate范围涉及到的记录非常大时，则a表字段sort1, sort2建索引，同时加提示sort1, sort2索引
如下语句：加ordered是强制执行计划先访问c表，然后nl嵌套循环走a表sort1或sort2索引访问a,再访问b
select x.id, x.adddate, x.sort1, x.sort2
from (
select /*+ ordered*/a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort1
union all
select /*+ ordered*/a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort2
) x
order by x.adddate desc
2.尽管union all方式a,b,c表访问了两遍，但此种情况比使用or实现效率高
3.如果a表查询的adddate范围涉及到的记录很少，则可不需建sort1, sort2索引，且取消上一sql的提示信息
即：
select x.id, x.adddate, x.sort1, x.sort2
from (
select a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort1
union all
select a.id, a.adddate, a.sort1, a.sort2
from c, a, b
where a.adddate>=to_date('2009-01-01 00:00','yyyy-mm-dd hh24:mi')
and a.adddate <=to_date('2009-05-15 23:59','yyyy-mm-dd hh24:mi')
and a.id = b.id
and a.sort1 = c.sort2
) x
order by x.adddate desc
4.避免查询用*，这样会使得sql访问数据库字典得到表相应字段，增加了额外开销
5.个人认为你的表设计有问题,a,b两个表完全等价于一张表，可一张表实现

[oraclelogan]

select count(*) from(
      select *
        from a
       inner join b on a.id = b.id
       inner join c on a.sort1 = c.sort
       where a.adddate >= to_date('2009-01-01 00:00', 'yyyy-mm-dd hh24:mi')
         and a.adddate <= to_date('2009-05-15 23:59', 'yyyy-mm-dd hh24:mi')
      union all
      select *
        from a
       inner join b on a.id = b.id
       inner join c on a.sort2 = c.sort
       where a.adddate >= to_date('2009-01-01 00:00', 'yyyy-mm-dd hh24:mi')
         and a.adddate <= to_date('2009-05-15 23:59', 'yyyy-mm-dd hh24:mi')
)abc1

你是慢在排序上面。有2种解决方案。

1，先看看你的总数据量有多少，如果百万的话，你用order by当然会慢了，你想想啊，先查询出百万数据，然后再一个个排序，不慢才怪呢？就不要排序了。

2，oracle排序用的是大池，也就是larger pool，你数据量在数十万以上排序的话，非常消耗资源，你多给larger pool分配点内存吧！

[ldw2545398]

select a.*,b.*,nvl(c1.stat,c2.stat),nvl(c1.unit,c2.unit) from a , b , c c1, c c2 where a.id=b.id and a.sort1=c1.sort(+) and a.sort2=c.sort(+) and 条件。。。。你看看这样能快点么？

[lbd8848]

谢谢各位,澄清下,
1.a 表和b 表实际使用中是一对多关系,只是此查询以一对一为主所以只说一对一关系.
2.查询中只读取a b c表中的几个字段,没有使用"*".不好意思这里写了用"*".
3.不显示所有的百万条记录,只显示时间段内的部分记录.有分页的.即该查询外另套分页语句
4.b.id不是主键,原因是实际表中不是一对一关系,所以没有使用主键.
5.sort1和sort2已做了normal索引,不好意思,没有标出来.

[mzl_mzl]

[Quote=引用 10 楼 ldw2545398 的回复:]
select a.*,b.*,nvl(c1.stat,c2.stat),nvl(c1.unit,c2.unit) from a , b , c c1, c c2 where a.id=b.id and a.sort1=c1.sort(+) and a.sort2=c.sort(+) and 条件。。。。你看看这样能快点么？
[/Quote]
十楼的这样写完全与要求不符，你自己都没试过就写出来了，估计连你自己写的是对是错都不知道

[lbd8848]

关键点还是在排序上,不排序与排序速度上差近二三十倍,结贴!