Boost Oracle Insert Speed by Moving Hourly Indexes to Memory and Fixing Offline Datafile Issues

Background

The author wanted to test whether keeping the index for the most recent hour in memory would improve the speed of bulk inserts into an Oracle database. Because the table is partitioned by hour, moving the current hour's index to memory could reduce I/O overhead.

Prerequisites

This approach requires Oracle 12c, which supports online datafile movement. After the hour ends, the index must be moved out of memory so the next hour's index can occupy the memory space, keeping memory usage predictable.

Preparation

Create the necessary tablespaces and a user with appropriate privileges.

create tablespace dasong datafile '/oradata/oracle/dasong.dbf' size 100m;
create tablespace dasong_idx2 datafile '/oradata/oracle/dasong_idx2.dbf' size 100m;
create tablespace dasong_idx3 datafile '/oradata/oracle/dasong_idx3.dbf' size 100m;

create user dasong identified by dasong
  default tablespace dasong
  temporary tablespace temp;

grant dba to dasong;
grant create session to dasong;
grant resource to dasong;
grant debug connect session to dasong;
grant debug any procedure to dasong;
grant select_catalog_role to dasong;

Create Table and Indexes

Define a partitioned table and two local indexes, then insert test data.

create table t_idx_offline_test (
  c1 number,
  c2 number,
  c3 number
)
partition by range(c1) interval(1000) (
  partition part_0 values less than (0)
) tablespace dasong;

create index idx_test_c2 on t_idx_offline_test(c2) tablespace dasong_idx2 local;
create index idx_test_c3 on t_idx_offline_test(c3) tablespace dasong_idx3 local;

insert into t_idx_offline_test
select rownum, rownum+1, rownum+2 from dual connect by rownum < 10000;
commit;

Offline the Index Datafile

Take the datafile that stores one of the index partitions offline to simulate the scenario.

alter database datafile '/oradata/oracle/dasong_idx2.dbf' offline for drop;

Even though the datafile is offline, the index partition remains visible; the dictionary still reports the partition as present.

Querying user_ind_partitions and user_segments shows the partition and its segment, even though the underlying file is offline.

select * from user_ind_partitions;

select * from user_segments;

Rebuild the Index Partition

Attempt to rebuild the partition while its datafile is offline. The rebuild fails because Oracle tries to read the original index data.

alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;

The error returned is:

ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/oradata/oracle/dasong_idx2.dbf'
*Cause: attempting to read from a file that is not readable. Most likely the file is offline.
*Action: Check the state of the file. Bring it online

Work‑around: Mark Partition Unusable Then Rebuild

First mark the problematic partition as UNUSABLE, then rebuild it into a different tablespace.

alter index idx_test_c2 modify partition sys_p872 unusable;</ncode><code>alter index idx_test_c2 rebuild partition sys_p872 tablespace dasong_idx3;

The rebuild succeeds, and the partition status becomes USABLE with its segment now residing in dasong_idx3.

However, the original temporary segment (10.130) remains in the dictionary even after the datafile is brought online and the database is restarted.

Temporary Segment Behaviour

Any operation that makes an index partition UNUSABLE (or drops the index, exchanges partitions, etc.) creates a temporary segment. Deleting the tablespace does not automatically remove this temporary segment because the dictionary still retains metadata about it.

Cleaning Up

Attempts to drop the tablespace dasong_idx2 fail with ORA‑14405 because the partitioned index still has a partition in a different tablespace.

SQL Error: ORA-14405: partitioned index contains partitions in a different tablespace
*Cause: An attempt was made to drop a tablespace which contains indexes whose partitions are not completely contained in this tablespace.
*Action: Find indexes with partitions spanning multiple tablespaces, drop or move them.

Manually deleting rows from seg$ does not fully clear the dictionary; the temporary segment remains invisible in user_segments but still blocks tablespace removal.

delete from seg$ where file#=10 and type#=3;
commit;

The reliable cleanup sequence is to drop the index first, then drop the tablespace.

drop index idx_test_c2;
drop tablespace dasong_idx2 including contents and datafiles;

Conclusion

The experiment shows that moving the current hour's index into memory can multiply insert throughput, but offline index datafiles introduce a subtle bug: rebuild operations fail unless the partition is first marked UNUSABLE and rebuilt into a different tablespace. Temporary segments left behind do not affect subsequent rebuilds, so the workaround is acceptable when the performance gain outweighs the leftover metadata.