Truncate Table is used when no matter drop storage or reuse storage will

Posted on 2009-06-26 filed under: Oracle (RSS) 26 views

Truncate Table is used when no matter drop storage or reuse storage will be re-instated to the first HWM

From: http://space.itpub.net/13165828/viewspace-607258

A, B for the two Table.
6g3n ~ Hrn ^ J9o0A, B, respectively, the data on the space under the table erp_data
k3 @ 8o9Z6 [4F4Y0A, B, respectively, on the index table space erp_indx personal ITPUB space under X! A u [7Z B

! nk @ n8s't WH K & T $) 0 then ITPUB personal space (rnV $ ArnW * D, m (I
We use the following two statements to delete data in two tables ITPUB personal space \! ^ Rnq R8x (U! X h / I
Truncate table A drop storage;
. z P1F)? / m n5W%] 0Truncate table B reuse storage; ITPUB Personal Space | & G3s) 1? 0 ~ b W k

rnP / w'v d N0 results would be:
6vrn [I C7A, r ^ 0
i8s a! l5 [0Truncate table A drop storage;
0S h # D'l% I, X y3Q $ \) y0 - data: data on the extent where some of the space will be released (the release of personal space minextentsITPUB recovery to O & N jz h'e x
Of extent), the space can be freed up for use of other segment.
: O) Y + | _ t2u knbx d0 - index: B form part of the index will delete data, extent has also been part of the release of the remaining first extentITPUB personal space P9q H Jrnt TH (QT
- hwm: will be re-instated HWM to the first position of Block's (hwm will change). ITPUB personal space 0z% C / _ C k
ITPUB personal space e + M8 ^ "E f RO
ITPUB personal space $ @ ornd% K! N; i s'? T)
Truncate table B reuse storage;
r "VG" ^ CWJ R0 - data: data on the extent where some of the space will not be recovered (only data will be deleted
3 \ d7 | V G0 in addition), after deletion of data freespace space only for the use of this table can not be
b K mN k P3e u0 for the use of other segment.
_, j + c ULY C0 - index: B form part of the index data will be deleted, but retain some extent personal ITPUB space [4h Y w $ D
- hwm: will be re-instated HWM to the first position of Block's (hwm will change).

LA z ( `x (P _7b + R0
`# s% a _ s.? rnp v N/q0 1, testing began to build two tables A, B and the corresponding index inx_A, idx_B
+ ~ R (YB F6t0ITPUB personal space; o O) V, t Q "UU g2n

f x6Q s0w y: ri @ s0 SQL> show user
k + w & T v (E2X I0
# bk "UX $ A l?! CrnN w l0USER is" QIUYB ";
nj T "z" wl X0
"z5Q xa` 0SQL> create table A (col number); ITPUB personal space 4h * K D # x K
ITPUB personal space, l G ^ / ny D ~ rnW
Table created.
6X M7F IOA: W | 0ITPUB personal space * s (drn ^ A1Q \ b

-uu l & d ^ 6s0SQL> insert into A values (1); ITPUB personal space 4n4 ~ 4R (N N9J v; K * ^
ITPUB personal space 9R K0f Q bi IE
1 row created.
\ rnn Z; ~ ( `0h qpi Q3u0SQL> insert into A select * from A;
X Y5y.Qrny (~ Y01 row created.
L "O c J j" M1e a0
'bo _ G r Z P0SQL> /
T a-X ' `rnw e m2B [# S02 rows created.
g @: B _0v | - ^-g, b5 (0
, P nb) s v.X '| g Q0SQL> /
rnm A'_ "); z8r & B ^ M Q0ITPUB personal space 6T% m QBS! t2P
4 rows created.
/ zm! L & s * A0ITPUB personal space: T Orn (& SrnE `e
SQL> / ITPUB personal space J6 @-XZ (9u

$ _5 `. T f * Y c08 rows created.
(y! n B2 ^ b \ o q0ITPUB personal space + ~ M ek B `D5G q
SQL> /
: \ 2I xZ-U o0
$ | E d G3U p B? C $ i016 rows created. ITPUB personal space, itz) A + i: y1 ^

Q7q v6 @ `(p0SQL> /
! e L) a! r F t Z0D0v b0ITPUB personal space * t p8R% @ "l% V w2`
32 rows created. ITPUB personal space [1 \ 8 `9z SrnX c) Z
ITPUB personal space \ KY \ [5Z sb
SQL> /
rno N7 `Q $ l / E + QrnE / VrnE! b064 rows created.
! r4O IG, R2R (f (S @ 0
(XPA) w & m & [M k0SQL> /
3a5 ^ 'X W1R @ b3A9P0ITPUB personal space)? ^' Mn | b O @
128 rows created. ITPUB personal space + o X wn prnQ (B

; k & `; bg (O $ L't WrnK Y q0SQL> /
rno% ~ $ LrnX X0
Z) S5 (i4 \ rnX5s \ (0256 rows created. ITPUB personal space "s (N p $ a.A 'Z & P L
ITPUB personal space tl # WEKB

rn ~ @. ( 'O7E l B) a0SQL> /
5I4L # (w H g0
$ x [1o0z hv V0512 rows created.
p LM # S /) s3H0ITPUB personal space \ vy V.Q4C # _ T
SQL> /
] Ugy D2Z Q f J. `0
. crnU BV * e #] T z3X i @ 8K? 01024 rows created. ITPUB personal space) EZJ _ M3S'_
ITPUB personal space J urng @ z ZX f $ A9b
SQL> / ITPUB personal space rn? N / E ,`&?* d
2048 rows created. ITPUB personal space RS _ (_8j.G
ITPUB personal space x S? Ru / `
SQL> /
@ $ y $) 7V] (e r04096 rows created.
gn N xE; D w0
. g M4U / `-] B0SQL> /
? C3Z o5) * `# r U j08192 rows created. ITPUB personal space e0F H y & L8 ^
ITPUB personal space e H # F "G y; h! Y
SQL> / ITPUB personal space rn (j D () hv% v9G b
16384 rows created.
"KBY mrnd C $ @ j yA \ 0
# A) C ~ yq ( `$ B0SQL> / ITPUB personal space #` N5c H r T # d T. (

# trnT0Y d ~ rnz) J032768 rows created.
4q ~ OW f) c/u0ITPUB personal space # m7T, [rnT (Y e & @ * N
SQL> /
b) e Y ernJ1F) * u0
ms U-_ t M Q065536 rows created.
A-N4L s $ C0ITPUB personal space X.A7f m / H c5 [L
SQL> commit;
$ XBRV) Urnp0
o! B g J j: w? mp M z8Y0Commit complete.
k3Krn_ G C6j) t0ITPUB personal space * J2 [y @ T7F m
ITPUB personal space (Q4q c3j3y; g7E $ [ 'rr X n
SQL> create table B as select * from A; ITPUB personal space q Qt t (t "x
ITPUB personal space t A r T3b Trnd T
Table created.
Z ~? Q db Z e ~ 1T (E0ITPUB personal space? "? @ 1? $ IrnCrn @

i5 ~ a YW (b0SQL> create index idx_A on A (col);
rnB; d J? \ 0
+ A * m T h + r0Index created. ITPUB personal space YJZ rrnr ^
ITPUB personal space O; t b1n.F
ITPUB personal space 0w; Z # a.lrnC Z, ^
SQL> create index idx_B on B (col);
6l2c T "(rnB0ITPUB personal space s W d HT (
Index created.
P t /? P2M c0
]: b A3a | 3L m, | 0ITPUB personal space 1A, QZ! ga Z $ H3F5H

# R (EZ * TN _ L8O0 2, look for the extent of these four paragraphs of the use. ITPUB personal space (s8b3 (D PP "[(f7N
ITPUB personal space m% urnj * t Prns ( `
ITPUB personal space GT f-u0H p V f / [5U
SQL> select segment_name, extent_id, bytes
9I S M5F% p W) M0
& l8M7S od K9j o0 2 from user_extents
b:? 2r: x2E7V! c8_0
& ( 'h5a z * pxee | E0 3 where segment_name like'% A% 'OR segment_name like'% B% '
Pv pu) p0
_ N _ W0r V0 4 order by 1, 2
8t Q-_1J n & b0ITPUB personal space g l1BrnS RP i! G
5 /
Q6E xNS B!] 0
Q] o V & @. N, d q0
; olz; GTT z. \ 0 SEGMENT_NA EXTENT_ID BYTESITPUB personal space! ib "R [@ 6W2m
---------- ---------- ----------
| Mrn | & I5X & c l0 A 0 65536
6V e, f T c @ t0A 1 65536
| $ k7 ^ 2Q & X) Brn_ Yrn [\ 0A 2 65536ITPUB personal space; W rf L1w $) (kn] 'c!]
A 3 65536
h; O s R wo W0A 4 65536
2g2 [ "KK K0A 5 65536
C2R E / n J (0A 6 65536
T & g V N6W0A 7 65536ITPUB personal space) * P u + (3G d]
A 8 65536
% r) L% E, D, NIL%? 0A 9 65536ITPUB personal space Z8]; p! @ rn (-S3N 'N% A8i
A 10 65536ITPUB personal space 1h G W4 `V1D to!? 6S
A 11 65536
\ rn ^ 6Z: nrnW D y7J $ n9d3J% J0A 12 65536
g0e [1i w Z0A 13 65536ITPUB personal space 9] G v `6n [1v
A 14 65536ITPUB personal space 8F, B); J! L u6O
A 15 65536ITPUB personal space, \% | E4N * t T
A 16 1048576ITPUB personal space P6crn (& z b N # | E
B 0 65536ITPUB personal space KFM p T0e
B 1 65536
(T! G1c $ o H0B 2 65536
4z qk UW # t2p; ~ 9H \ 3o0B 3 65536ITPUB personal space, B5r: _ f: u C3o) U \
B 4 65536ITPUB personal space 'y0O8 \ / F3_
B 5 65536
U () 'z # N & C b0B 6 65536
A5 | 4R e'L o z & K o0B 7 65536
+ x0n.o3 `7r.E0B 8 6553
H @ M0 [ "Q # n6S0B 9 65536ITPUB personal space D y4K'f R) N B1K.N $ r
B 10 65536ITPUB personal space? 7? - | 5E (\ TrnH (# n B ob
B 11 65536
) HX xb% oz% p'j8 | q K $ l p0B 12 65536ITPUB personal space 'HC o1S5g B3A
B 13 65536
# H "L m * N N0B 14 65536ITPUB personal space? R'a e8u Y a! L $ Rrn]
B 15 65536ITPUB personal space rny + W j W.R0 ~ R
B 16 1048576ITPUB personal space] rnY-W $ qA! Zrnt (Drn_ F
IDX_A 0 65536ITPUB personal space H5a1 `* i`
IDX_A 1 65536ITPUB personal space KI _; o # df
IDX_A 2 65536ITPUB personal space w J D9H R ^
IDX_A 3 65536ITPUB personal space KK (K6N TUL nV
IDX_A 4 65536
L/U5q u DrnX,?. Y0IDX_A 5 65536ITPUB personal space 6O Q) @] # s7_
IDX_A 6 65536ITPUB personal space, Z! Q5q) f O'M "O) * N
IDX_A 7 65536
i [Y @-p _0IDX_A 8 65536ITPUB personal space. j * w0t z J1P (
IDX_A 9 65536
A + o5E urg t0IDX_A 10 65536ITPUB personal space A2q% j (^ g Q k # X ( `
IDX_A 11 65536
J r V7v Z.A6L [^ vx K0IDX_A 12 65536ITPUB personal space] 9Z $ e D7h s S _ S ~
IDX_A 13 65536
\ R fe $ L Z0IDX_A 14 65536
f2h XN, c x4H * m9 `0IDX_A 15 65536ITPUB personal space u S (` I; ErnH (K5C
IDX_A 16 1048576
4M% _ si V) ^ z7j0IDX_A 17 1048576
(F "J ~ w:? 0IDX_B 0 65536
g I * NA o0p je F, A Y0IDX_B 1 65536ITPUB personal space U? R # J (j6v3N # h.O + N
IDX_B 2 65536ITPUB personal space f X6Y0G CrnH
IDX_B 3 65536ITPUB personal space M8? 4k9 ^ ~
IDX_B 4 65536
4) & c9 ^ 6o n ns Lrn ~ (0IDX_B 5 65536
! z c9P G # y: G0IDX_B 6 65536
W u OF u "A & s.v0IDX_B 7 65536ITPUB personal space an E` K v
IDX_B 8 65536ITPUB personal space I / `R! L% z O & Y1 @
IDX_B 9 65536ITPUB personal space 1 \ # z.) 9P f6i1l
IDX_B 10 65536ITPUB personal space g8z! U c0 \ grn `y
IDX_B 11 65536
! | V _rn @ F @; pp L-(0IDX_B 12 65536ITPUB personal space: O0o% p G1F "X5S
IDX_B 13 65536ITPUB personal space l2v an (~: m R
IDX_B 14 65536ITPUB personal space 6e8a l8 ~ ^ 9o) D
IDX_B 15 65536ITPUB personal space rnh) G (i4T9p ^ \ 5f
IDX_B 16 1048576
i Krno.h X/p0IDX_B 17 1048576ITPUB personal space \ / X7Y] I
ITPUB personal space mrnb. @ RnO _: V2J x
70 rows selected.
F t ED)) u0
rnV. [h vrnqrnT * E R'o0
h * V u FG! k: x0
3a e ~ 7p G ^ & [0
& R W & r-? T) d _ b6V0ITPUB personal space / g8y APO z m'n
3, View A, B List, HWM, formula HWM = total_blocks-empty_blocks, we can see A, B, respectively HWM of 232, 168
+ v2X l6O (v! h *): r | f0SQL> analyze table A compute statistics; ITPUB personal space 8c & Q \ IY t'M
Table analyzed. ITPUB personal space sa F3 @ l
ITPUB personal space (6s%? + M! W + LD
SQL> analyze table B compute statistics; ITPUB personal space, q9g q, l2x \) j3)
Table analyzed. ITPUB personal space; Z / d T [2m._
ITPUB personal space A + b B n (? (T
SQL> col table_name format a10ITPUB personal space V / t i4H a R i ^
ITPUB personal space% | t6e! E'o v ^ g
SQL> SELECT TABLE_NAME, blocks, empty_blocks, blocks-empty_blocks hwmITPUB personal space n * BW q6_ h \, Y x K
2 FROM user_tables
1Z ~ p, t K, b0 3 WHERE table_name IN ( 'A', 'B') ITPUB personal space 1nrnr d2 ^ # oJ * J
4 / ITPUB personal space L v J / (I _8J @ O

-VV i ((* C \ G l n0ITPUB personal space 2g4Z J0c byq
SQL> set pagesize 10 ITPUB personal space 0 | Z $ I (B [3s `
SQL> / ITPUB personal space c I `y, FT

-s! Uv h + L k Y, c0 TABLE_NAME BLOCKS EMPTY_BLOCKS HWMITPUB personal space 4? M9K8f3J! BH
---------- ---------- ------------ ---------- ITPUB `personal space (\:) ~ | # u (K) ZI
A 244 12 232
"B6O uk T0B 212 44 168ITPUB personal space" v6? ~ KrnP Y / M) 6q) CX $ G

G9 \ rnS l S7s6S (c N0
: ~ T2F ~-q! IF i B04, to DROP STORAGE, REUSE STORAGE on the two options A, B do see differences between different operating ITPUB personal space * `_ e + EHT K6 \ * j

C "[% P S3a9G" q0SQL> truncate table A DROP STORAGE; ITPUB personal space) Kc z3 [RrnL4w d * (
ITPUB personal space: w +? 9z + E w B
Table truncated. ITPUB personal space S hh) ^ 9r! B / \ 2F (S
ITPUB personal space). O # drn ^ 7Z Q x) t6C * rp
SQL> truncate table B REUSE STORAGE; ITPUB personal space z; Q / A gq nx \ l
Table truncated.
arn | 2G: F z% c: f% E0ITPUB personal space 7f d? + m% @ S HG
SQL> SET PAGESIZE 300ITPUB personal space $ J, hrne) R'E $ v

5k Y) n Hrn] / z prnY u0SQL> select segment_name, extent_id, bytes
(1G @ O qe (0ITPUB personal space Q6] b V% v Q ht l & u
2 from user_extents ITPUB personal space R g N (A) r

2H.A8g M "u A w E6j0 3 where segment_name like '% A%' OR segment_name like '% B%' ITPUB personal space # ^ 'Yf% k (rnw" p8L
ITPUB personal space / @ W y!] EE
4 order by 1, 2ITPUB personal space rnq L3N'q7K (T Urn_ (drnP d V

I?-L-R3q) D @ (S0 5 / ITPUB personal space 2g9k I u9L B9X C

b-^ ~ e9x y3b0
4 ~ 4k z? W0
z% dx ((/ [a $ c "j0 SEGMENT_NA EXTENT_ID BYTESITPUB Personal Space | S9q% Z # w3u
---------- ---------- ----------
+ d _ ~ j $ _ * RB / Y u0 A 0 65536ITPUB personal space _ on; K + ^ 0B (~ $ v ~
B 0 65536ITPUB personal space / W wi W) `` K (U, Z7b
B 1 65536ITPUB personal space% X (Bd Z f @ l S9h; t
B 2 65536
h'z y! e) GF ((- ~ 0B 3 65536
Y v irnN KR j i0B 4 65536ITPUB personal space _! H Srnt vt] j: m? B4 ^
B 5 65536ITPUB personal space rnV "y T * z QF Rrn (H x% K
B 6 65536ITPUB personal space O `; J0Q g3I n Q h N
B 7 65536ITPUB personal space [ "qC W9i
B 8 65536
c1 | (F], D) e BN z0X0B 9 65536ITPUB personal space 8t'r Z] e q2T z)
B 10 65536
7 (V KL m u4L `r0B 11 65536
[$ W0h3J1A (+ n ^ Q (q0B 12 65536
) y'g% l Z z Q + J0B 13 65536
rnc h # r4 `w ~: w * U E0B 14 65536ITPUB personal space si T | b1g
B 15 65536ITPUB personal space \ * k D] z A [
B 16 1048576
'i?; ghg T s0IDX_A 0 65536
& o L7X5o i (g): N z0IX_B 0 65536
: (, o; ^ `) s6j $ zrnP a0m2A0IDX_B 1 65536ITPUB personal space _2ernzrnh f5B I p X
IDX_B 2 65536
1f nx T3U P p-z0IDX_B 3 65536ITPUB personal space # A nb S9?: I P / I)
IDX_B 4 65536ITPUB personal space \ b $ M G2J D'h
IDX_B 5 65536ITPUB personal space U a) FB Y'e! Dk j6n r * et
IDX_B 6 65536ITPUB personal space 9U A [_ c, S5l9 ^ 1_
IDX_B 7 65536
t! OX w y0IDX_B 8 65536
) K I + J) G $ d, V N2) P0IDX_B 9 65536ITPUB personal space 9) 'q \) n5g E o
IDX_B 10 65536ITPUB personal space 4Q% g * m'u E ~
IDX_B 11 65536
) YX \ n * h1W0IDX_B 12 65536ITPUB personal space: DX; H: @ E-\ \ z (
IX_B 13 65536ITPUB personal space @ 1u pj H, ($]
IDX_B 14 65536
C (MF w F (] 9B C% T0IDX_B 15 65536
2z jG (U j L;? 0IDX_B 16 1048576
v, ~ \-b X0 `m0 [* A [v0IDX_B 17 1048576
bv? m l0
(B?% [S3 [0ITPUB personal space. T2T ^ Q & c ^ ob
37 rows selected. ITPUB personal space 1 | 7 ~ (S $ dh
ITPUB personal space GI / V $ I'a _
ITPUB personal space w M; @ 6l4h O # K, F6A
A table can see itself and its index are idx_A the extent of the release of all reservations to the first extent. ITPUB personal space% z R% A7G # k V + P
SQL> analyze table A compute statistics;
X5 ^ & @: Z / e O4H0ITPUB personal space 9k $ w'g A7 |? 2d * ko
Table analyzed.
v ^ + r8r) B n C1? i C Q0
DX F7l6] N: y0SQL> analyze table B compute statistics; ITPUB personal space% pi ^ x P7k + R

! h T \ g + ~; AD u N2N0Table analyzed.
4P "ji N c0
XL w M N4i N0SQL> SELECT TABLE_NAME, blocks, empty_blocks, blocks-empty_blocks hwm
r M, X p2d ~ L% U e0
7l c8] 4y1d q5 @ 0 2 FROM user_tables ITPUB personal space c \ | q! Q.p + L Y6a

XT y NF (R "^, Irna0 3 WHERE table_name IN ( 'A', 'B')
rnS Q/J3C w) [7V [* (0s0ITPUB personal space 9l A y Y # B
4 / ITPUB personal space "R r brnW6n Z" H / K / y

| 1y, r $ \ & Z) \ v0J0ITPUB personal space Q t G | (v! E; U

, G * _: T $ n? I% N a0 TABLE_NAME BLOCKS EMPTY_BLOCKS HWM
(% lp-z3p4? \ 0 ---------- ---------- ------------ ----------
J t P u% I KD f G / | 0A 0 8 - 8ITPUB personal space cm $ O1 (0d HO
B 0 256 - 256ITPUB personal space S r9 \: Z + m H0G.r

: b6K.N, u * I u0ITPUB personal space, FU) \-xf O (_
ITPUB personal space 4n n + Grnl & \, O h C u
Can see that the two forms are returned to the hwm the first block. ITPUB personal space 5G (; (8G ~

rn rn rn