WILDCARD_INDEX_FRAGMENTATION

Oracle Database supports wildcard index diagnostics and optimization. Use this function to determine whether wildcard index ($KG) would benefit from optimization.

This function returns an estimate of fragmentation for the wildcard index associated with an Oracle Text index. It returns a numeric value representing the estimated percentage fragmentation of the wildcard index. A higher value indicates greater fragmentation and may indicate that optimization would improve storage efficiency and wildcard query performance.

A recommended workflow is to first use CTX_REPORT.WILDCARD_INDEX_FRAGMENTATION to evaluate the level of fragmentation in the wildcard index. If the reported fragmentation is high, optimize the wildcard index by running CTX_DDL.OPTIMIZE_INDEX. After the optimization completes, rerun CTX_REPORT.WILDCARD_INDEX_FRAGMENTATION to verify that fragmentation has been reduced and to assess the effectiveness of the optimization. This workflow helps maintain efficient wildcard index.

Syntax

CTX_REPORT.WILDCARD_INDEX_FRAGMENTATION(
index_name IN VARCHAR2,
part_name IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

index_name

Specify the name of the wildcard index.

part_name

Specify the name of the index partition. Required for local partitioned indexes.

Notes

• This function applies only to indexes that include wildcard indexing.

• For partitioned indexes, you need to specify part_name.

• If wildcard indexing is not enabled for the index, the reported value may not be meaningful.

• The reported value is an estimate intended for maintenance and diagnostic purposes.

Related Topics

• Optimizing Wildcard Index ($KG)