|
-
HWM question after delete
Hi all,
Please see the folllowing test:
Code:
SQL> set linesize 10000
SQL> spool c:\temp\hwm.txt
SQL> set timing on
SQL> select count(*) from TEST;
COUNT(*)
----------
2798098
Elapsed: 00:08:24.01
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 145.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:01.04
SQL> delete from TEST;
2798098 rows deleted.
SQL> ALTER TABLE TEST.TEST MOVE
2 /
Table altered.
Elapsed: 00:00:13.03
SQL> ALTER INDEX TEST.TEST REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
2 /
Index altered.
Elapsed: 00:00:00.02
SQL> ALTER INDEX TEST.TEST_PK REBUILD LOGGING NOREVERSE TABLESPACE TEST_DATA
2 /
Index altered.
Elapsed: 00:00:00.04
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 100.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:00.06
SQL> SPOOL OFF
SQL>
Why HWM is not shrink close to zero after I move tables and rebuild index???? I intentionally move it on the same tablespace, I also tried to move to different tablespace but it doens't help.
could someone please tell me why???
-
Since we're taking about storage, you moved your table without changing any storage parameters.
ALTER TABLE xxx MOVE STORAGE(minextents 1);
Last edited by marist89; 01-10-2005 at 11:23 AM.
Jeff Hunter
-
Thanks Jeff,
So if I don't have (minextents 1), will HWM shrink??? All I care about to shrink HWM to improve performance.
also, please see the below test:
Code:
SQL> SPOOL OFF
SQL> ALTER TABLE TEST.test MOVE (minextents 1);
ALTER TABLE TEST.test MOVE (minextents 1)
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
Elapsed: 00:00:00.01
SQL>
thanks again
Last edited by learning_bee; 01-10-2005 at 11:12 AM.
-
?
ALTER TABLE xxx MOVE STORAGE(minextents 1);
-
Originally posted by learning_bee
So if I don't have (minextents 1), will HWM shrink??? All I care about to shrink HWM to improve performance.
Does HWM have anything to do with the storage allocated to that segment?
Jeff Hunter
-
Code:
SQL> ALTER TABLE TEST.TEST move storage (minextents 1);
Table altered.
Elapsed: 00:00:00.04
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 100.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:00.01
SQL> analyze table TEST compute statistics;
Table analyzed.
Elapsed: 00:00:00.00
SQL> SELECT tablespace_name "Tablespace Name",
2 bytes/1048576 "Size(M)"
3 FROM dba_segments
4 where owner='TEST';
Tablespace Name Size(M
------------------------------------------------------------------------------------------ ---------
TEST_DATA 100.5
TEST_DATA 48.5
TEST_DATA 56.5
Elapsed: 00:00:00.01
SQL>
nothing is changing.
-
See below:
PHP Code:
+1 09:54:27 SQL> drop table t1;
+2
+3 Table dropped.
+4
+5 Elapsed: 00:00:01.92
+6 09:54:33 SQL> create table t1 as select * from dba_objects ;
+7
+8 Table created.
+9
+10 Elapsed: 00:00:01.39
+11 09:54:49 SQL> insert into t1 select * from t1 ;
+12
+13 23360 rows created.
+14
+15 Elapsed: 00:00:00.90
+16 09:55:08 SQL> /
+17
+18 46720 rows created.
+19
+20 Elapsed: 00:00:00.99
+21 09:55:10 SQL> /
+22
+23 93440 rows created.
+24
+25 Elapsed: 00:00:00.97
+26 09:55:11 SQL> /
+27
+28 186880 rows created.
+29
+30 Elapsed: 00:00:05.14
+31 09:55:17 SQL> commit;
+32
+33 Commit complete.
+34
+35 Elapsed: 00:00:00.13
+36 09:55:20 SQL> col segment_name format a5
+37 09:55:37 SQL> select segment_name, blocks, bytes
+38 from user_segments where segment_name='T1' ;
+39
+40 SEGME BLOCKS BYTES
+41 ----- ---------- ----------
+42 T1 7205 59023360
+43
+44 Elapsed: 00:00:00.48
+45
+46 09:56:38 SQL> select tablespace_name ,
+47 09:56:47 2 extents, segment_name, blocks, bytes
+48 from user_segments where segment_name='T1' ;
+49
+50 TABLESPACE_NAME EXTENTS SEGME BLOCKS BYTES
+51 ------------------------------ ---------- ----- ---------- ----------
+52 USERS 17 T1 7205 59023360
+53
+54 Elapsed: 00:00:00.09
+55 09:56:54 SQL> commit;
+56
+57 Commit complete.
+58
+59 Elapsed: 00:00:00.00
+60 09:57:01 SQL> delete from t1 ;
+61
+62 373760 rows deleted.
+63
+64 Elapsed: 00:00:20.10
+65 09:57:29 SQL> commit;
+66
+67 Commit complete.
+68
+69 Elapsed: 00:00:00.01
+70 09:57:32 SQL> select tablespace_name ,
+71 09:57:38 2 extents, segment_name, blocks, bytes
+72 from user_segments where segment_name='T1' ;
+73
+74 TABLESPACE_NAME EXTENTS SEGME BLOCKS BYTES
+75 ------------------------------ ---------- ----- ---------- ----------
+76 USERS 17 T1 7205 59023360
+77
+78 Elapsed: 00:00:00.10
+79 09:57:40 SQL> alter table t1 move ;
+80
+81 Table altered.
+82
+83 Elapsed: 00:00:01.53
+84 09:57:49 SQL> select tablespace_name ,
+85 09:57:51 2 extents, segment_name, blocks, bytes
+86 from user_segments where segment_name='T1' ;
+87
+88 TABLESPACE_NAME EXTENTS SEGME BLOCKS BYTES
+89 ------------------------------ ---------- ----- ---------- ----------
+90 USERS 1 T1 5 40960
+91
+92 Elapsed: 00:00:00.44
+93 09:57:54 SQL> spool off
Tamil
-
How many extents?
DMT or LMT?
Jeff Hunter
-
Originally posted by learning_bee
Why HWM is not shrink close to zero after I move tables and rebuild index???? I intentionally move it on the same tablespace, I also tried to move to different tablespace but it doens't help.
You are observing the space alocated by the table segment, your query does not tell you anything about HWM. HWM and space alocated to the table are two totaly different things.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
