多表查询优化

Question

有表 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就慢得不得了
不知有什么好的优惠方法,请指点!

Answer 1

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

Answer 2

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

Answer 3

a.id是主键，b.id不是主键，至少也应该是unique index关联顺序应该是(select * from a order by a.adddate) , b , cselect ... from (select * from a order by a.adddate) aa , b , c where ...
或者用inner join
看一下解释计划，还是不对的话加上hint试试

Answer 4

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两个表完全等价于一张表，可一张表实现

Answer 5

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

Answer 6

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两个表完全等价于一张表，可一张表实现

Answer 7

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分配点内存吧！

Answer 8

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 条件。你看看这样能快点么？

Answer 9

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