?

注意:

（1）使用FULL HINT,以避免查询进行索引快速全扫描，而不是对表进行全表扫描。

（2）这里不能为了提高性能而使用PARALLEL（并行），测试表明，在表上进行并行查询，以DIRECT READ方式读取表并不会清除掉表上的事务。

?

如果表过大，SELECT COUNT(*)的时间过长，那么我们可以用下面的代码将表分成多个段，进行分段查询。

/* Formatted on 2011/6/29 19:18:40 (QP5 v5.163.1008.3004) */

SELECT DBMS_ROWID.rowid_create (1,

oid1,

fid1,

bid1,

0)

rowid1,

DBMS_ROWID.rowid_create (1,

oid2,

fid2,

bid2,

9999)

rowid2

FROM (SELECT a.*, ROWNUM rn

FROM ( SELECT chunk_no,

MIN (oid1) oid1,

MAX (oid2) oid2,

MIN (fid1) fid1,

MAX (fid2) fid2,

MIN (bid1) bid1,

MAX (bid2) bid2

FROM (SELECT chunk_no,

FIRST_VALUE (

data_object_id)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

oid1,

LAST_VALUE (

data_object_id)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

oid2,

FIRST_VALUE (

relative_fno)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

fid1,

LAST_VALUE (

relative_fno)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

fid2,

FIRST_VALUE (

block_id)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

bid1,

LAST_VALUE (

block_id + blocks - 1)

OVER (

PARTITION BY chunk_no

ORDER BY

data_object_id, relative_fno, block_id

ROWS BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING)

bid2

FROM (SELECT data_object_id,

relative_fno,

block_id,

blocks,

CEIL (sum2 / chunk_size) chunk_no

FROM (SELECT /*+ rule */

b.data_object_id,

a.relative_fno,

a.block_id,

a.blocks,

SUM (

a.blocks)

OVER (

ORDER BY

b.data_object_id,

a.relative_fno,

a.block_id)

sum2,

CEIL (

SUM (a.blocks) OVER ()

/ &trunks)

chunk_size

FROM dba_extents a, dba_objects b

WHERE a.owner = b.owner

AND a.segment_name =

b.object_name

AND NVL (a.partition_name,

'-1') =

NVL (b.subobject_name,

'-1')

AND b.data_object_id

IS NOT NULL

AND a.owner = UPPER ('&owner')

AND a.segment_name =

UPPER ('&table_name'))))

GROUP BY chunk_no

ORDER BY chunk_no) a);

?

该SQL 在执行时需要输入几个参数：

trunks: 表示把表分成的段数

owner： 表的所有者

table_name: 表名

?