化整为零访问大表的三种方式
业务场景:
表xngul 大小大于 100G。
上面有(id)是number型自增字段,且是pk。
现在有需求要对这个表进行全表扫描,如果直接 select * from xngul, 则至少要半个小时,
而且一次性返回数据过多,应用程序无法处理。
所以想了办法化整为零,将这个表分段,分段读取。
有以下三种方式。
*******I.两个步骤,一个取分段的头尾,一个按头尾取分段内数据。*********
-取分段的头尾
select min(id) as min_id,
max(id) as max_id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id)
where rownum <= :page_size;
-按头尾取中间的数据
select *
from xngul
where id between :min_id and :max_id;
**********************************************************************
――――――――――――――――――――――――――――-
**************II.一个步骤,完成分段和取数据。*************************
-在一个sql中完成分段和取数据
select /*+ordered use_nl(b)*/
b.*
from
(select id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id) a
where rownum <= :page_size) a, xngul b
where a.id=b.id;
**********************************************************************
――――――――――――――――――――――――――――-
********III.借助一个表,实现多个进程并发处理。************************
-将分段数据记录到表中,并给每个段赋予一个 batch_id 和 batch_status
insert into batch
(batch_id, batch_status, ceiling_id, floor_id)
select
seq_batch.nextval as batch_id,
’not dealed’ as batch_status,
max(id) as ceiling_id,
min(id) as floor_id
from
(select /*+index (xngul xngul_pk)*/id
from xngul
where id > :last_max_id
order by id)
where rownum <= :page_size;
-多进程并发取未处理的batch_id
select batch_id, ceiling_id, floor_id
from batch for update nowait
where batch_status=’not dealed’
and rownum<=1;
-取该batch_id的明细数据
select *
from xngul
where id between :min_id and :max_id;
-处理完毕后,更新该batch_status
update batch
set batch_status=’has dealed’
where batch_id=:batch_id;
**********************************************************************
该方式还可以再扩展:
1,如果对数据实时性要求不高,可以在standby上按rowid来分段读取,效率会更高。
2,如果要做表连接,则可以对其中的大表做这个分段,分段的结果再来跟其他小表做连接,同样可以达到化整为零的目的。
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:奶妈来了 来源: Alibaba DBA Team
- 标签: 化整为零
- 发布时间:2010-01-14 09:29:11
- [11] 产品设计之QQ邮箱登录页与淘宝登录页
- [10] Python连接 MySQL 数据库的超时问
- [8] 四年前的今天,我开始找工作
- [8] 页面线框图教程(之一):从本质到表象
- [8] 一个 VLA (可变长度数组)的实现
- [8] 读书:谣言
- [8] 分布式系统的事务处理
- [7] 招聘的绑架
- [7] 格式塔:转动视觉的魔方
- [7] 谈谈Facebook的聊天系统架构