CODEWITHSUNDEEP
sqldatabaseoracle-databasetablespace
Kirill Kobyshev
Kirill Kobyshev

Reputation: 17

How to clean unused space after rows deletion on table with functional indexes?

How to clean table space correctly?

For example, we have a table with several million rows and functional indexes. We want to remove the most part of the table.

For this we called: delete from some_table where ....

What the next steps?

Is this sequence correct? 1. Drop functional indexes. 2. Alter some_table shrink space. 3. Create functional indexes again.

Upvotes: 0

Views: 5428

Answers (5)

EdStevens
EdStevens

Reputation: 3872

There is another option I haven't seen anyone propose. Issue the DELETE, then do nothing. WHY do we think we need to rebuild the index? WHY do we think we need to resize the table? If we do nothing after the DELETE, all of the extents for the table remain allocated to the table and WILL be used on future INSERTS. If you have a retail store and conduct a clearance sale, resulting in a lot of empty shelves, do you then rebuild the store to eliminate the 'wasted' space? Or do you simply re-use the empty shelves as new stock comes in? What is to be gained by resizing the table? At best, it will release the space back to the TS, not the OS file system. While there are use cases that argue for resizing the table, it is NOT an automatic given that resizing after a large (even massive) DELETE is necessarily justified.

So again, my answer is to do nothing after the DELETE (well, do a COMMIT, of course!)

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59553

  • Create a copy of your table
  • Drop old table
  • Rename copy

has a disadvantage, during this operation (which may take some time) your application is not available.

So, your approach

  1. Drop functional indexes.
  2. Alter some_table shrink space.
  3. Create functional indexes again.

is correct.

In case you have a partitioned table, see this one: https://dba.stackexchange.com/questions/162415/how-to-shrink-space-on-table-with-a-function-based-index

Upvotes: 0

Levente Takács
Levente Takács

Reputation: 445

Best way

Create a new table with only the valid data, and recreate the indexes there, then drop the old table.

  • Create the new table with the filtered data, where table_name is the name of the table, filter_text is a condition started with 'WHERE ...', and the partitionText is a partitioning clause if you have one for the table, example RANGE (ENDEDAT) INTERVAL ( NUMTODSINTERVAL(1,''day'') ) ( PARTITION p_first VALUES LESS THAN ( TO_DATE(''01-01-2010'',''dd-MM-yyyy'') ) ) ENABLE ROW MOVEMENT
sqlCommand := 'create table ' || table_name ||'_TMP
        tablespace &TBS_NORMAL_TABLES initrans 32 ' || partitionText ||'
        nologging 
        AS (SELECT * FROM '||table_name|| ' ' ||filter_text||')';

EXECUTE IMMEDIATE sqlCommand;
  • Add attributes to the new table.

Such as, constraints, indexes... Those could be collected from built-in tables such as all_constraints, all_indexes. The moving of the attributes could be also automatized, just need to apply some rename hacks.

  • Change the old and the new table
execute immediate 'ALTER TABLE &Schemaowner..'||v_table_name||' RENAME TO '||v_table_name||'_OT';
execute immediate 'ALTER TABLE &Schemaowner..'||v_table_name||'_TP'||' RENAME TO '||v_table_name;
  • Drop the old table

execute immediate 'DROP TABLE '||v_table_name||'_OT';

TL;DR informations about the shrinking, and reorg tables

Here comes some informations and useful links about my investigation when considered archiving huge amount of data on live production DBs.

Automated way to shrink some tables, and handle an error on them

for i in (SELECT obj.owner,obj.table_name,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable, row_movement
        FROM all_tables obj,
        (SELECT table_name, COUNT(rownum) cnt
            FROM user_indexes 
            WHERE index_type LIKE 'FUN%'
        GROUP BY table_name) idx
        WHERE obj.table_name = idx.table_name(+)
        AND obj.owner = &Schemaowner 
        and obj.table_name like 'T_%' and obj.table_name not like 'TMP_%'
        and NVL(idx.cnt,0) < 1) 
    loop
        BEGIN
            if i.row_movement='ENABLED' then
                execute immediate 'alter table '||i.table_name||' shrink space';
            else
                execute immediate 'alter table '||i.table_name||' enable row movement';
                execute immediate 'alter table '||i.table_name||' shrink space';
                execute immediate 'alter table '||i.table_name||' disable row movement';
            end if;
        DBMS_OUTPUT.PUT_LINE('shrinked table: ' || i.table_name);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('error while shrinking table: ' || i.table_name);
                DBMS_OUTPUT.PUT_LINE (SQLERRM);
                DBMS_OUTPUT.PUT_LINE (SQLCODE);

                if SQLCODE=-10635 then
                    for p in (SELECT partition_name ,tablespace_name FROM user_tab_partitions WHERE  table_name = 'SOME_PARTITIONED_TABLE') 
                    loop
                    BEGIN
                        execute immediate 'alter table '||i.table_name||' MOVE PARTITION ' || p.partition_name || ' ONLINE TABLESPACE ' || p.tablespace_name || ' COMPRESS UPDATE INDEXES';
                        DBMS_OUTPUT.PUT_LINE('moved partition: ' || p.partition_name);
                        EXCEPTION
                            WHEN OTHERS THEN
                            DBMS_OUTPUT.PUT_LINE('error while moving partition: ' || p.partition_name);
                            DBMS_OUTPUT.PUT_LINE (SQLERRM);
                            DBMS_OUTPUT.PUT_LINE (SQLCODE);
                            CONTINUE;
                    END;
                    end loop;
                end if;
               CONTINUE;
        END;
    end loop;

USEFUL selects

Shrinkable tables

SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM all_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM user_indexes 
WHERE index_type LIKE 'FUN%'
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND NVL(idx.cnt,0) < 1
and obj.owner='YOUR_SCHEMA_OWNER'

Indexes that makes shrink command inexecutable

SELECT *
FROM all_indexes 
WHERE index_type LIKE 'FUN%'
and owner='YOUR_SCHEMA_OWNER'

The shrinkable tables without any compromise

SELECT obj.owner
,obj.table_name
,(CASE WHEN NVL(idx.cnt, 0) < 1 THEN 'Y' ELSE 'N' END) as shrinkable
FROM all_tables obj,
(SELECT table_name, COUNT(rownum) cnt
FROM user_indexes 
WHERE index_type LIKE 'FUN%'
GROUP BY table_name) idx
WHERE obj.table_name = idx.table_name(+)
AND NVL(idx.cnt,0) < 1
--and obj.table_name like 'T_%' and obj.table_name not like 'TMP_%'
and obj.compression != 'ENABLED'
and obj.table_name not in (SELECT table_name FROM user_tab_partitions WHERE compression = 'ENABLED')
and obj.owner='YOUR_SCHEMA_OWNER'

Shrink problems, compression of tables and/or partitions

SELECT table_name,compression, compress_for FROM   user_tables WHERE  compression = 'ENABLED'


SELECT table_name,partition_name, compression, compress_for FROM   user_tab_partitions WHERE  compression = 'ENABLED' ORDER BY 1

Examine these before/after shrink, test the hell out of it

select segment_name,bytes/1024/1024 as mb,blocks from user_segments where segment_name='TABLE_NAME'

In my case i create a table (not partitioned) with couple million rows, then delete 1/3 of it, here is the results:

                ||     BYTES     ||  BLOCKS     ||
Before deletion ||   105250816   ||  12848      ||
After deletion  ||    78774272   ||   9616      ||

Investigation and side-effects

Possible side effects https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536157800346457013

...and when should use reorg: http://www.dba-oracle.com/t_table_fragmentation.htm

...enable row movement while shrink space can reorder the rows (this means if use use ROWID based jobs or selects or something like that, then there could be some surprises)

http://www.dba-oracle.com/t_enable_row_movement.htm

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17944

As you probably realized (or you wouldn't be asking about function-based indexes in particular), you are not able to simply:

alter table mytable enable row movement;
alter table mytable shrink space;
alter table mytable disable row movement;

Attempting to do so will result in:

ORA-10631: SHRINK clause should not be specified for this object

(Note: this limitation also applies to bitmap join indexes.)

Clearly, you can drop the FBI first...

drop index my_fbi_index;
alter table mytable enable row movement;
alter table mytable shrink space;
alter table mytable disable row movement;
create index my_fbi_index... online;

That's not an online operation though. Your application(s) will be affected by the missing function-based-index for a short time.

If you need an online operation and you are on Oracle 12.2 or later, you can try this instead:

alter table mytable move online;

(alter table...move (no "online") is available pre-12.2, but it's not an online operation and it will drop your index segments, leaving the indexes marked "unusable" and requiring you to rebuild them. So, not really a good option pre-12.2.)

Upvotes: 2

Needle file
Needle file

Reputation: 518

  1. create table newtable select * from oldtable where...
  2. drop oldtable
  3. rename newtable to oldtable
  4. recreate indexes.

Upvotes: 0

Related Questions