业务场景：
表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，如果要做表连接，则可以对其中的大表做这个分段，分段的结果再来跟其他小表做连接，同样可以达到化整为零的目的。

有5人发表了评论  ↓发表评论↓

• 

  前两天我负责的一个利用BPM规则引擎拆核心业务表的项目，就是用的最后一种方式，每次开始拆分前先根据id生成段号表数据，然后多台机器并发领取不同的batch对业务表进行拆分

  kevin zheng @ January 13, 2010 |

• 

  奶妈，顶你

  orain @ January 16, 2010 |

• 

  上面有(id)是number型自增字段，且是pk。

  杭州搬家 @ January 23, 2010 |

• 

  (⊙ˍ⊙)＞﹏＜

  上海水泵厂 @ January 23, 2010 |

• 

  以上三种方案,如何可以的话, 我倾向于使用第三种..

  另外介绍另外一种处理方式.
  来自 Jonathan Lewis的Pseudo-parallel.
  http://jonathanlewis.wordpress.com/2010/01/03/pseudo-parallel/

  
select
data_object_id
from user_objects
where object_name = 'T1'
;

  select
  extent_id, file_id, block_id, blocks
  from
  dba_extents
  where
  owner = 'TEST_USER'
  and segment_name = 'T1'
  order by
  extent_id
  ;

  set serveroutput off
  set linesize 180

  with rowid_range as (
select
/*+ materialize */
dbms_rowid.rowid_create(
1,
&m_object,
file_id,
block_id,0
) low_rowid,
dbms_rowid.rowid_create(
1,
&m_object,
file_id,
block_id+blocks-1,
4095
) high_rowid
from
dba_extents
where
owner = 'TEST_USER'
and segment_name = 'T1'
and extent_id = 8
)
select
/*+
gather_plan_statistics
ordered
use_nl(t1)
rowid(t1)
*/
t1.rowid,
t1.small_vc
from
rowid_range rr,
t1
where
t1.rowid between rr.low_rowid and rr.high_rowid
;


  对于具体内容感兴趣的兄弟可以自己研究该文.

  jametong @ February 1, 2010 |

Name (必填)

Mail (不会公开) (必填)

网站

表情：<(￣︶￣)> | (⊙ˍ⊙) | ＞﹏＜ | ｂ（￣▽￣）ｄ | （─.─||） | (^_-)

[ Ctrl+Enter提交 ]