Skip to main content

Search

Welcome to Tibero GTS!

AUTO_COALESCE Function and Alternatives When Disabled

Document Type | Technical Information

Category | Administration

Applicable Product Versions | 6FS06, 7FS02

Document Number | TADTI034

 

Overview

This document explains the AUTO_COALESCE function and the alternative measures when this function is disabled.
AUTO_COALESCE is a feature that periodically cleans up empty space within indexes to reduce the overall index size. In other words, it efficiently manages index space through an internal mechanism that merges multiple empty blocks into a single block.
 
 

Method

Check the AUTO_COALESCE and AUTO_COALESCE_INTERVAL parameter values using the example below.
SQL> select name, value, dflt_value from v$parameters where name = 'AUTO_COALESCE';

NAME                 VALUE                DFLT_VALUE
-------------------- -------------------- --------------------
AUTO_COALESCE        2                    2

1 row selected.
Options
  • 0 : Do not use the index coalescing feature
  • 1 : Perform coalescing only on LOB indexes
  • 2 : Perform coalescing on all indexes (default)
     
SQL> select name, value, dflt_value from v$parameters where name = 'AUTO_COALESCE_INTERVAL';

NAME                           VALUE                DFLT_VALUE
------------------------------ -------------------- --------------------
AUTO_COALESCE_INTERVAL         100                  100

1 row selected.
Options
AUTO_COALESCE_INTERVAL indicates the frequency at which the index coalescing operation is performed.
  • Default: 100 (10 seconds)
  • Range: 0 ~ 3600000 (0.1 second ~ 100 hours)
Note
If AUTO_COALESCE is disabled during operation, performance degradation may occur during index scans due to fragmented index blocks. Additionally, in environments with heavy DML operations, the storage space used by indexes can continuously increase.
 
As an alternative, it is necessary to periodically perform manual COALESCE or INDEX REBUILD operations.
 
 
 

Related to