溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》
您好，登錄后才能下訂單哦！
簡(jiǎn)單的數(shù)據(jù)集
execute dbms_random.seed(0)
create table t1 (
id not null,
date_open, date_closed,
deal_type, client_ref,
small_vc, padding
)
nologging
as
with generator as (
select /*+ materialize cardinality(1e4) */
rownum id
from dual
connect by
rownum <= 1e4
)
select
1e4 * (g1.id - 1) + g2.id
id,
trunc(
add_months(sysdate, - 120) +
(1e4 * (g1.id - 1) + g2.id)* 3652 / 1e7
)
date_open,
trunc(
add_months(
add_months(sysdate, - 120) +
(1e4 * (g1.id - 1) + g2.id) * 3652 / 1e7,
12 * trunc(dbms_random.value(1,6))
)
)
date_closed,
cast(dbms_random.string('U',1) as varchar2(1)) deal_type,
cast(dbms_random.string('U',4) as varchar2(4)) client_ref,
lpad(1e4 * (g1.id - 1) + g2.id,10) small_vc,
rpad('x',100,'x') padding
from
generator g1,
generator g2
where
g1.id <= 1e3
and g2.id <= 1e4
;
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')
alter table t1 add constraint t1_pk primary key(id) using index nologging;規(guī)模
Quality
場(chǎng)景
select rows_in_block, count(*) blocks, rows_in_block * count(*) row_count, sum(count(*)) over (order by rows_in_block) running_blocks, sum(rows_in_block * count(*)) over (order by rows_in_block) running_rows from ( select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid), count(*) rows_in_block from t1 -- -- where date_open >=add_months(sysdate, -60) -- where date_open < add_months(sysdate, -60) -- -- where date_closed >=add_months(sysdate, -60) -- where date_closed < add_months(sysdate, -60) -- -- where substr(client_ref,2,1) >= 'F' -- where substr(client_ref,2,1) < 'F' -- group by dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) ) group by rows_in_block order by rows_in_block ;
Blocks Rows Rows per block Blocks Rows Running total Running total -------------- -------- ------------ ------------- ------------- 27 1 27 1 27 49 203,877 9,989,973 203,878 9,990,000 50 200 10,000 204,078 10,000,000 -------- sum 204,078
Blocks Rows Rows per block Blocks Rows Running total Running total -------------- -------- ------------- ------------- ------------- 27 1 27 1 27 42 1 42 2 69 49 102,014 4,998,686 102,016 4,998,755 -------- sum 102,016
Blocks Rows Rows per block Blocks Rows Running total Running total ------------- -------- -------------- ------------- -------------- 1 5 5 5 5 2 22 44 27 49 3 113 339 140 388 4 281 1,124 421 1,512 5 680 3,400 1,101 4,912 6 1,256 7,536 2,357 12,448 7 1,856 12,992 4,213 25,440 8 2,508 20,064 6,721 45,504 9 2,875 25,875 9,596 71,379 10 2,961 29,610 12,557 100,989 11 2,621 28,831 15,178 129,820 12 2,222 26,664 17,400 156,484 13 1,812 23,556 19,212 180,040 14 1,550 21,700 20,762 201,740 15 1,543 23,145 22,305 224,885 16 1,611 25,776 23,916 250,661 17 1,976 33,592 25,892 284,253 18 2,168 39,024 28,060 323,277 19 2,416 45,904 30,476 369,181 20 2,317 46,340 32,793 415,521 21 2,310 48,510 35,103 464,031 22 2,080 45,760 37,183 509,791 23 1,833 42,159 39,016 551,950 24 1,696 40,704 40,712 592,654 25 1,769 44,225 42,481 636,879 26 1,799 46,774 44,280 683,653 27 2,138 57,726 46,418 741,379 28 2,251 63,028 48,669 804,407 29 2,448 70,992 51,117 875,399 30 2,339 70,170 53,456 945,569 31 2,286 70,866 55,742 1,016,435 32 1,864 59,648 57,606 1,076,083 33 1,704 56,232 59,310 1,132,315 34 1,566 53,244 60,876 1,185,559 35 1,556 54,460 62,432 1,240,019 36 1,850 66,600 64,282 1,306,619 37 2,131 78,847 66,413 1,385,466 38 2,583 98,154 68,996 1,483,620 39 2,966 115,674 71,962 1,599,294 40 2,891 115,640 74,853 1,714,934 41 2,441 100,081 77,294 1,815,015 42 1,932 81,144 79,226 1,896,159 43 1,300 55,900 80,526 1,952,059 44 683 30,052 81,209 1,982,111 45 291 13,095 81,500 1,995,206 46 107 4,922 81,607 2,000,128 47 32 1,504 81,639 2,001,632 48 3 144 81,642 2,001,776 49 122,412 5,998,188 204,054 7,999,964 -------- sum 204,054
索引空間
select rows_per_leaf, count(*) leaf_blocks from ( select /*+ index_ffs(t1(client_ref)) */ sys_op_lbid(94255, 'L', t1.rowid) leaf_block, count(*) rows_per_leaf from t1 where client_ref is not null groupby sys_op_lbid(94255, 'L', t1.rowid) ) group by rows_per_leaf order by rows_per_leaf ;
select rows_per_leaf, count(*) blocks, rows_per_leaf * count(*) row_count, sum(count(*)) over (order by rows_per_leaf) running_blocks, sum(rows_per_leaf * count(*)) over (order by rows_per_leaf) running_rows from ( select /*+ leading(v1 t1) use_hash(t1)*/ leaf_block, count(*) rows_per_leaf from ( select /*+ no_mergeindex_ffs(t1(client_ref)) */ sys_op_lbid(94255, 'L',t1.rowid) leaf_block, t1.rowid rid from t1 where client_ref is not null ) v1, t1 where t1.rowid = v1.rid and date_open < add_months(sysdate, -60) group by leaf_block ) group by rows_per_leaf order by rows_per_leaf ;
Blocks Rows Rows_per_leaf Blocks Rows Running total Running total ------------- -------- ------------- ------------- -------------- 181 2 362 3 458 186 2 372 5 830 187 2 374 7 1,204 188 1 188 8 1,392 ... 210 346 72,660 2,312 474,882 211 401 84,611 2,713 559,493 ... 221 808 178,568 8,989 1,921,410 222 851 188,922 9,840 2,110,332 223 832 185,536 10,672 2,295,868 ... 242 216 52,272 21,320 4,756,575 243 173 42,039 21,493 4,798,614 244 156 38,064 21,649 4,836,678 ... 265 1 265 22,321 5,003,718 266 1 266 22,322 5,003,984
原作者 : Jonathan Lewis
原文地址： https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/
| 譯者簡(jiǎn)介
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼