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);

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

?
ALTER TABLE xxx MOVE STORAGE(minextents 1);

Quote:

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?

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?

Quote:

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.