jian huang -- Thanks for the question regarding "How does insert and delete use space regarding to indexes?", version 9.2.0

Submitted on 6-Jan-2009 7:33 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 13-Mar-2009 7:28

You Asked

Hello Tom

I have such as test:
SQL> create table t as select * from dba_objects;

Table created.

SQL> desc t
Name

OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY

SQL> create index t_ind on t(object_id);

Index created.

SQL> desc segment_space_a
PROCEDURE segment_space_a
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SEGNAME VARCHAR2 IN
P_OWNER VARCHAR2 IN DEFAULT
P_TYPE VARCHAR2 IN DEFAULT
P_PARTITION VARCHAR2 IN DEFAULT

SQL> set serveroutput on size 1000000
SQL> exec segment_space_a(P_SEGNAME=>'T_IND',p_type=>'INDEX');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 65
Total Blocks............................ 80
Total Bytes............................. 655,360
Total MBytes............................ 0
Unused Blocks........................... 7
Unused Bytes............................ 57,344
Last Used Ext FileId.................... 14
Last Used Ext BlockId................... 120
Last Used Block......................... 1

PL/SQL procedure successfully completed.

SQL> delete from t;

29599 rows deleted.

SQL> exec segment_space_a(P_SEGNAME=>'T_IND',p_type=>'INDEX');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 66
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 0
Total Blocks............................ 80
Total Bytes............................. 655,360
Total MBytes............................ 0
Unused Blocks........................... 7
Unused Bytes............................ 57,344
Last Used Ext FileId.................... 14
Last Used Ext BlockId................... 120
Last Used Block......................... 1

PL/SQL procedure successfully completed.

SQL> insert into t select * from dba_objects;

29600 rows created.

SQL> exec segment_space_a(P_SEGNAME=>'T_IND',p_type=>'INDEX');
Unformatted Blocks ..................... 76
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 102
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 128
Total Blocks............................ 384
Total Bytes............................. 3,145,728
Total MBytes............................ 3
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 14
Last Used Ext BlockId................... 776
Last Used Block......................... 64

PL/SQL procedure successfully completed.

SQL>

First I create a table t and index it. then I check the index blocks to see the space consumed.
Then I delete all rows from T and check the index block and as expected , the blocks remain the same... But when I reinsert the *same* rows, I expect the deleted spaces to be reused, since index data has its own place to go and rows are same. but you can see that the blocks increase by almost 55 percent.

Why is that? Since the same rows are inserted ?

Thanks!

and we said...

space in an index cannot be reused in the SAME transaction. If you:

delete from t;
1,000,000 rows deleted
insert into t select * from t2;
1,000,000 rows inserted

you would expect to see the indexes effectively double in size. If on the other hand you:

delete from t;
1,000,000 rows deleted
COMMIT;
insert into t select * from t2;
1,000,000 rows inserted

then you would find the index to probably be about the same size - the space would be reused.

Now, if the job was to remove all rows and reload the table, I would recommend:
truncate t;
insert /*+ APPEND */ into t select * from t2;

That would result in the most efficient space utilization. If you cannot have the data "disappear" (eg, this reload must be transactional) you can use partitioning, for example assume you want to reload T from a staging table:

drop table t;
drop table stage;
create table stage as select * from all_objects;

create table t
(
part_id,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY
)
NOLOGGING
PARTITION BY list (part_id)
(
  PARTITION part1 VALUES (1),
  PARTITION part2 VALUES (2)
)
as 
select 1, stage.* 
  from stage;

create index t_idx on t(owner,object_type,object_name) local nologging;

create or replace procedure refresh_t
as
    l_empty   number;
    l_full    number;
begin
    /* figure out partition to insert into, that'll be the EMPTY one */
    select empty, full into l_empty, l_full
      from (
    select 2 empty, 1 full
      from t partition (part1) 
     where rownum=1 
     union all 
    select 1 empty, 2 full
      from t partition (part2)
     where rownum=1
           );
    insert /*+ APPEND */ into t
    select l_empty, stage.* 
      from stage;

    execute immediate 'alter table t truncate partition part' || to_char(l_full);
end;
/

that'll have space utilized as nicely as possible. Only one partition at a time will appear to have data as far as end users are concerned.

Reviews

January 7, 2009 - 4am Central time zone