HWM question after delete

[learning_bee]

Hi all,

Please see the folllowing test:

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???

[marist89]

Since we're taking about storage, you moved your table without changing any storage parameters.

ALTER TABLE xxx MOVE STORAGE(minextents 1);

[learning_bee]

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:

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

[DaPi]

?
ALTER TABLE xxx MOVE STORAGE(minextents 1);

[marist89]

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?

[learning_bee]

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.

[tamilselvan]

See below:

    +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

[marist89]

How many extents?
DMT or LMT?

[jmodic]

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.