Kris Kringle the Database! – Truncate and Indexes – Learning is not a spectator sport

Kris Kringle the Database! – Truncate and Indexes

December 4, 2024

1–2 minutes

Whether you call it “Kris Kringle” or “Secret Santa“, there’s much pleasure in giving small gifts to people over the Christmas period. So rather than fully fledged blog posts, I’ll wrap up this year with a series of daily quick tips on the Oracle Database. Ho Ho Ho!

If you are loading a table, often we want to make the indexes unusable before loading the data to speed up the process. My target table has three indexes, so I’ll make them unusable before truncating and loading my data, and the rebuild them after.


SQL> select index_name, status
  2  from   user_indexes
  3  where  table_name = 'TARGET_TABLE';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_TABLE_IX1               VALID
TARGET_TABLE_IX2               VALID
TARGET_TABLE_IX3               VALID

SQL> alter index target_table_ix1 unusable;

Index altered.

SQL> alter index target_table_ix2 unusable;

Index altered.

SQL> alter index target_table_ix3 unusable;

Index altered.

SQL>
SQL> truncate table target_table;

Table truncated.

SQL>
SQL> insert into target_table
  2  select * from source_data;

88343 rows created.

SQL> alter index target_table_ix1 rebuild;

Index altered.

SQL> alter index target_table_ix2 rebuild;

Index altered.

SQL> alter index target_table_ix3 rebuild;

Index altered.

Looks fine right? Unfortunately not. If you are using this method, remember that setting indexes to unusable must come after the truncate, because will that automatically make your indexes valid again.


SQL> alter index target_table_ix1 unusable;

Index altered.

SQL> alter index target_table_ix2 unusable;

Index altered.

SQL> alter index target_table_ix3 unusable;

Index altered.

SQL>
SQL> truncate table target_table;

Table truncated.

SQL>
SQL> select index_name, status
  2  from   user_indexes
  3  where  table_name = 'TARGET_TABLE';

INDEX_NAME                     STATUS
------------------------------ --------
TARGET_TABLE_IX1               VALID
TARGET_TABLE_IX2               VALID
TARGET_TABLE_IX3               VALID

Ho Ho Ho… Merry Christmas!

Yes, this is me, sacrificing my dignity to bring you Christmas tips 🙂

6 responses to “Kris Kringle the Database! – Truncate and Indexes”

aminaa49ed2e27e

December 4, 2024 at 7:16 pm

Is dropping the indexes and rebuilding do anything different? Except for FKs

Reply
1. Connor McDonald
  
  December 4, 2024 at 9:45 pm
  
  I hate dropping because it means you need to make sure you recreate them exactly as you had them before, eg what type of compression, what tablespace, what pctfree, reverse, parallelism etc etc …. Easy to end up with mistakes
  
  Reply
Chris Grabowy

December 4, 2024 at 10:20 pm

Hey Connor, is your second example output correct? You mention:

If you are using this method, remember that setting indexes to unusable must come after the truncate, because will that automatically make your indexes valid again.

But I see alter index unusable commands and then truncate. Shouldn’t that be the other way around based on your statement?

Am I missing something here?? I feel like I am misreading this…

Reply
1. Connor McDonald
  
  December 4, 2024 at 10:54 pm
  
  The second code segment is just proving my statement, ie, truncate after unusable makes an index valid.
  
  Reply
Rajeshwaran Jeyabal

December 5, 2024 at 3:38 pm
Plus adding docs link to this
- Oracle Database also automatically truncates and resets any existing UNUSABLE indicators for the following indexes on table: range and hash partitions of local indexes and subpartitions of local indexes.
- If table is not empty, then the database marks UNUSABLE all nonpartitioned indexes and all partitions of global partitioned indexes on the table. However, when the table is truncated, the index is also truncated, and a new high water mark is calculated for the index segment. This operation is equivalent to creating a new segment for the index. Therefore, at the end of the truncate operation, the indexes are once again USABLE.
Reply
Hemant K Chitale

December 13, 2024 at 2:43 pm

Yep. Some years ago, I got caught by this behaviour. I had thought “UNUSABLE – TRUNCATE – REBUILD” would work. Jonathan Lewis pointed out to me that the TRUNCATE would make the Indexes “usable” again !

https://hemantoracledba.blogspot.com/2016/05/truncateing-table-makes-unusable-index.html

Reply