In very large production databases, indexes can grow to several terabytes and consume significant storage in index tablespaces. In telecom and billing systems where billions of records are processed daily, indexes can become extremely large and inefficient if they are not optimized properly.
In this example we demonstrate how we reduced the size of a massive Oracle index from 2.27 TB to 562 GB using Advanced Index Compression in an Oracle 19c production database running on Oracle Exadata Database Machine.
Environment Details
The database contains several large partitioned tables storing telecom EDR flow data. One of the largest tables in the schema is GUGEDRFLOW.
Exadata Environment
This optimization was performed on an Oracle Exadata Database Machine running Oracle Database 19c. Exadata provides high-performance storage servers, smart scan capabilities, and advanced compression technologies that significantly improve database performance for large telecom and billing workloads.
The index rebuild and compression operation leveraged the high parallel processing power of the Exadata environment, allowing the 2.27 TB index to be rebuilt with Advanced High Index Compression in approximately 33 minutes.
Step 1: Identify the Large Index
SQL> SELECT owner, segment_name, ROUND(bytes/1024/1024/1024,2) AS size_gb FROM dba_segments WHERE segment_name='IDX_GUGRATTYPE' AND segment_type='INDEX';
OWNER SEGMENT_NAME SIZE_GB
------------------ ------------------ --------
KHDBDBADMIN IDX_GUGRATTYPE 2279.45
The index size is approximately 2.27 TB, which is extremely large.
Step 2: Verify Index Information
SQL> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE OWNER='KHDBDBADMIN' AND TABLE_NAME='OTHEDRFLOW';
OWNER |INDEX_NAME |INDEX_TYPE |TABLE_NAME |TABLESPACE_NAME
--------------------|------------------|---------------|---------------|-------------------
KHDBDBADMIN |IDX_GUGRATTYPE |NORMAL |OTHEDRFLOW |KHDBIDXTBS
Step 3: Check Tablespace Utilization
SQL> @tablespace_size;
Status |Name |Size (GB) |Used (GB) |Free (GB) |(Used) %
---------|---------------|--------------|---------------|---------------|------------
ONLINE |KHDBIDXTBS | 32,548.00| 28647.62 | 3,900.38 | 88.02
The tablespace usage is already above 88%, which is risky for production environments.
Step 4: Rebuild Index Using Advanced Compression
SQL> ALTER INDEX KHDBDBADMIN.IDX_GUGRATTYPE REBUILD COMPRESS ADVANCED HIGH PARALLEL 128 ONLINE NOLOGGING;
Index altered.
Elapsed: 00:33:00
The rebuild operation completed in approximately 33 minutes.
SQL> ALTER INDEX KHDBDBADMIN.IDX_GUGRATTYPE LOGGING; Index altered. SQL> ALTER INDEX KHDBDBADMIN.IDX_GUGRATTYPE NOPARALLEL; Index altered.
Step 5: Verify Index Size After Compression
SQL> SELECT owner, segment_name,ROUND(bytes/1024/1024/1024,2) AS size_gb FROM dba_segments WHERE segment_name='IDX_GUGRATTYPE' AND segment_type='INDEX';
OWNER SEGMENT_NAME SIZE_GB
------------------ ------------------ --------
KHDBDBADMIN IDX_GUGRATTYPE 562.58
After compression the index size is reduced to 562 GB.
This optimization reduced the index size from 2.27 TB to 562 GB, saving approximately 1.7 TB of storage space.
Step 6: Verify Tablespace After Optimization
SQL> @tablespace_size
Status Name Size(GB) Used(GB) Free(GB) Used%
------- ---------- --------- --------- --------- ------
ONLINE KHDBIDXTBS 32548.00 26930.75 5617.25 82.74
Object Size After Optimization
SQL> @ldr_obj_size
OWNER TABLE_NAME TABLE_PARTITIONS TABLE_SIZE_GB INDEX_SIZE_GB TOTAL_SIZE_GB
------------- ----------- ---------------- ------------- ------------- -------------
KHDBDBADMIN GUGEDRFLOW 38 2920.71 11075.45 13996.16
Benefits of Advanced Index Compression
- Reduces storage requirements significantly
- Optimizes the use of tablespace
- Reduced I/O operations
- Enhances overall index efficiency
- Boosts performance in large-scale data warehouse environments
Conclusion
In large Oracle databases, indexes can consume several terabytes of storage.
Using Advanced Index Compression can drastically reduce index size
while maintaining performance.
In this real production example we reduced a 2.27 TB index to 562 GB
in just 33 minutes, saving nearly 1.7 TB of storage.
Regular index maintenance and compression strategies are essential best practices
for DBAs managing large-scale Oracle environments.
Toufique Khan
No comments:
Post a Comment