表A:a1 a2 a3 a4 a5 a6,
表B:b1 b2 b3 b4 b5 b6,
表C:c1 c2 c3 num
在a1 a2 a3上建了个索引,同样b1 b2 b3也有索引,数据量在1000万左右1、查询时where条件的应该顺序怎么写? 好像oracle中解析where子句的顺序是从后往前,那如何利用这个索引?是a1=?and a2 =? and a3=? 还是 a3=? and a2=? and a1=?
2、A、B两表关联字段为a1 a2 a4,where顺序该怎么写?a1=b1 and a2=b2 and a4=b4 and a1=? and a2=?好像关联的写在前,条件筛选写在后?关联部分的具体顺序是什么,能用索引吗?
3、update C set c.num = (select count(1) from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? group by a4,a5) where exists (select 1 from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? )
这条语句写法有问题吗?有没有简便的写法?大家帮帮忙!。。
表B:b1 b2 b3 b4 b5 b6,
表C:c1 c2 c3 num
在a1 a2 a3上建了个索引,同样b1 b2 b3也有索引,数据量在1000万左右1、查询时where条件的应该顺序怎么写? 好像oracle中解析where子句的顺序是从后往前,那如何利用这个索引?是a1=?and a2 =? and a3=? 还是 a3=? and a2=? and a1=?
2、A、B两表关联字段为a1 a2 a4,where顺序该怎么写?a1=b1 and a2=b2 and a4=b4 and a1=? and a2=?好像关联的写在前,条件筛选写在后?关联部分的具体顺序是什么,能用索引吗?
3、update C set c.num = (select count(1) from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? group by a4,a5) where exists (select 1 from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? )
这条语句写法有问题吗?有没有简便的写法?大家帮帮忙!。。
2.建索引在a1,a2,a4和b1,b2,b4上,关联写在前面
where a1=b1 and a2=b2 and a4=b4 and a1=?
3.没有问题,不过数据量很大的情况下这样更新会很慢 可以试试临时表和merge
1.
Oracle里有cbo和rbo,选择cbo的方式,oracle会在统计数据的依据,基于cost来选择最优化的方式执行。2
默认的情况下,最大过滤的放在右边,从右至左3
update C set c.num = (select count(1) from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? group by a4,a5) where exists (select 1 from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? )
这样的写法,从语法上是没有问题的。
不过 c.num = (select count(1) from A where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=? group by a4,a5)
这样的写法,必须保证子查询里是单结果集返回,你这样是的吗,如果不是,会有错误报出
1、where的顺序:是a1=?and a2 =? and a3=? 还是 a3=? and a2=? and a1=? 这两种顺序是不是没区别,都能利用索引?3、能保证子查询里是单结果集返回;确实更新相当慢,merge的方法怎么用?谢谢。。
merge好像在这种情况下也不会太好
写个过程用cursor批量更新吧cursor upd_cur is
select c.rowid id, sum(1) over(partition by a4,a5) val
from a,c
where a1=c1 and a4=c2 and a5=c3 and a1=? and a2=? and a3=?;open upd_cur;
loop
fetch upd_cur
bulk collect into row_id_rec
limit 10000; forall i in row_id_rec.first..row_id_rec.last
update c set num = row_id_rec.val where c.rowid = row_id_rec.id; commit; exit when upd_cur%notfound;
end loop;
加上hints, /*+RULE+*/将采用RBO,此时限制条件顺序有关系