当我们查询、更新、删除几百GB的非分区大表或已分区但单分区过大时，为了解决01555等问题，经常需要通过ROWID切片的方式，并行执行，减少单次执行时间。当我们切分分区表的一个分区后，查询时除了rowid between ‘xxxx’ and 'xxxx’外还需要显示指定分区名，否则将会扫描ALL PARTITIONS。
演示如下
SQL> @seg TEST.TEST_PART_TAB
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- ------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
3143 TEST TEST_PART_TAB P142407 TABLE PARTITION HS_DAT 402304 95 2012305
2240 TEST TEST_PART_TAB P143119 TABLE PARTITION HS_DAT 286720 95 2013329
3119 TEST TEST_PART_TAB P143989 TABLE PARTITION HS_DAT 399232 95 2014353
3133 TEST TEST_PART_TAB P144724 TABLE PARTITION HS_DAT 401024 95 2015377
3221 TEST TEST_PART_TAB P145449 TABLE PARTITION HS_DAT 412288 95 2016401
3498 TEST TEST_PART_TAB P147312 TABLE PARTITION HS_DAT 447744 95 2018449
6719 TEST TEST_PART_TAB P147736 TABLE PARTITION HS_DAT 860032 95 2019473
7552 TEST TEST_PART_TAB P148165 TABLE PARTITION HS_DAT 966656 95 2020497
10944 TEST TEST_PART_TAB P148889 TABLE PARTITION HS_DAT 1400832 95 2021521
3063 TEST TEST_PART_TAB P150498 TABLE PARTITION HS_DAT 392064 95 2023569
2752 TEST TEST_PART_TAB P151284 TABLE PARTITION HS_DAT 352256 95 2024593
2752 TEST TEST_PART_TAB P152083 TABLE PARTITION HS_DAT 352256 95 2025617
3008 TEST TEST_PART_TAB P152840 TABLE PARTITION HS_DAT 385024 95 2026641
3004 TEST TEST_PART_TAB P153613 TABLE PARTITION HS_DAT 384512 95 2027665
2944 TEST TEST_PART_TAB P155869 TABLE PARTITION HS_DAT 376832 95 2029713
3008 TEST TEST_PART_TAB P156594 TABLE PARTITION HS_DAT 385024 95 2030737
3008 TEST TEST_PART_TAB P157318 TABLE PARTITION HS_DAT 385024 95 2031761
3135 TEST TEST_PART_TAB P159139 TABLE PARTITION HS_DAT 401280 95 2032785
2254 TEST TEST_PART_TAB P162234 TABLE PARTITION HS_DAT 288512 95 2034833
2021 TEST TEST_PART_TAB P165489 TABLE PARTITION HS_DAT 258688 95 2035857
2240 TEST TEST_PART_TAB P168295 TABLE PARTITION HS_DAT 286720 95 2036881
3300 TEST TEST_PART_TAB P146340 TABLE PARTITION HS_DAT 422400 95 2017425
11562 TEST TEST_PART_TAB P149657 TABLE PARTITION HS_DAT 1479936 95 2022545
2944 TEST TEST_PART_TAB P155029 TABLE PARTITION HS_DAT 376832 95 2028689
2503 TEST TEST_PART_TAB P160489 TABLE PARTITION HS_DAT 320384 95 2033809
2304 TEST TEST_PART_TAB P168991 TABLE PARTITION HS_DAT 294912 95 2037905
8768 TEST TEST_PART_TAB P173380 TABLE PARTITION HS_DAT 1122304 95 2038929
27 rows selected.
SQL> @partition_table_trunk_by_rowid
Enter value for trunks: 6
Enter value for owner: TEST
Enter value for table_name: TEST_PART_TAB
Enter value for part_name: P149657
ROWID2
--------------------------------------------------------------------
where rowid between 'AADxxCAARAAC3aAAAA' and 'AADxxCABlAADML/CcP';
where rowid between 'AADxxCABlAADOMAAAA' and 'AADxxCAB/AAC75/CcP';
where rowid between 'AADxxCAB/AADCsAAAA' and 'AADxxCACMAAC+L/CcP';
where rowid between 'AADxxCACMAADEMAAAA' and 'AADxxCACaAADB7/CcP';
where rowid between 'AADxxCACaAADKMAAAA' and 'AADxxCACnAADML/CcP';
where rowid between 'AADxxCACnAADSMAAAA' and 'AADxxCAC+AADJr/CcP';
6 rows selected.
explain plan for
select count(*) from TEST.TEST_PART_TAB where rowid between ‘AADxxCAARAAC3aAAAA’ and ‘AADxxCABlAADML/CcP’;
explain plan for
select count(*) from TEST.TEST_PART_TAB partition(P149657) where rowid between ‘AADxxCAARAAC3aAAAA’ and ‘AADxxCABlAADML/CcP’;
角本partition_table_trunk_by_rowid.sql
select /*+ rule */ ' where rowid between '''||dbms_rowid.rowid_create(1, oid1, fid1, bid1, 0) ||''' and '''||
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 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')
and a.PARTITION_NAME=upper('&part_name')
)))
group by chunk_no
order by chunk_no) a);
最后修改时间：2020-09-18 10:12:16
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
