Composite, Descending, Function-Based, Invisible, and Compressed Indexes

Learning outcomes

ServiceHub has three common requests: find open work orders for one region newest-first, perform case-insensitive lookup by external reference, and evaluate whether an older index is still necessary. Five single-column indexes are possible, but that does not mean five indexes are the best design. This lesson treats an index key as a workload-specific access contract and uses visibility/compression as controlled engineering tools rather than decoration.

1. Composite index order follows access patterns

For the common predicate region_code = :r AND status_code = :s followed by newest-first ordering, an index on (region_code, status_code, created_at DESC) can provide a contiguous key range and useful ordering. The “most selective column first” slogan is not a universal rule: equality predicates, range boundaries, ordering, skip-scan possibilities, compression, and other queries all matter.

CREATE INDEX sh08_region_status_dt_ixON servicehub_ix_case(    region_code,    status_code,    created_at DESC);EXPLAIN PLAN SET STATEMENT_ID = 'SH08L2_COMPOSITE'FORSELECT work_order_id, created_atFROM servicehub_ix_caseWHERE region_code = 'NORTH'  AND status_code = 'OPEN'ORDER BY created_at DESCFETCH FIRST 20 ROWS ONLY;SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L2_COMPOSITE', 'BASIC +PREDICATE'));

The optimizer may still choose another legal plan on tiny lab data. The design claim is that the key ordering supports the predicate/order pattern—not that an index scan is guaranteed.

2. Function-based indexes require expression discipline

A function-based index stores the result of an expression. If the application repeatedly performs case-insensitive lookup with UPPER(external_ref), an index on exactly that expression can make it indexable. The expression must be deterministic where user-defined functions are involved, and the index owner needs the required execution privileges. Statistics should be gathered so the optimizer can estimate the expression distribution.

CREATE INDEX sh08_upper_ref_ixON servicehub_ix_case(UPPER(external_ref));BEGIN    DBMS_STATS.GATHER_TABLE_STATS(        ownname => USER,        tabname => 'SERVICEHUB_IX_CASE',        cascade => TRUE    );END;/SELECT index_name, index_type, funcidx_status, statusFROM user_indexesWHERE index_name = 'SH08_UPPER_REF_IX';SELECT index_name, column_expressionFROM user_ind_expressionsWHERE index_name = 'SH08_UPPER_REF_IX';

SELECT work_order_id, external_refFROM servicehub_ix_caseWHERE UPPER(external_ref) = UPPER(:external_ref);

Do not assume every semantically equivalent expression will match the stored expression in the way you expect. Normalize application SQL deliberately, inspect the generated predicate, and verify plans on the target release.

3. Deliberately wrong approach: wrap the column differently and blame Oracle

A team creates UPPER(external_ref) but a framework emits TRIM(UPPER(external_ref)) = :b1. That is a different indexed expression. The correct repair is not to hint the old index blindly; align the expression with the real business normalization rule, possibly through a virtual column if that makes the contract clearer.

4. Invisible indexes support controlled testing, not risk-free deletion

An invisible index is maintained by DML but ignored by the optimizer unless OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE for the session/system. This makes invisibility useful for controlled before/after plan tests and for observing whether an index appears dispensable. It does not prove the index is globally unused: another service, batch job, foreign-key maintenance path, or rare incident query may depend on it.

ALTER INDEX sh08_region_status_dt_ix INVISIBLE;SELECT index_name, visibilityFROM user_indexesWHERE index_name = 'SH08_REGION_STATUS_DT_IX';ALTER SESSION SET optimizer_use_invisible_indexes = FALSE;EXPLAIN PLAN SET STATEMENT_ID = 'SH08L2_OFF'FORSELECT work_order_idFROM servicehub_ix_caseWHERE region_code = 'NORTH'  AND status_code = 'OPEN';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L2_OFF', 'BASIC'));ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;EXPLAIN PLAN SET STATEMENT_ID = 'SH08L2_ON'FORSELECT work_order_idFROM servicehub_ix_caseWHERE region_code = 'NORTH'  AND status_code = 'OPEN';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L2_ON', 'BASIC'));ALTER SESSION SET optimizer_use_invisible_indexes = FALSE;ALTER INDEX sh08_region_status_dt_ix VISIBLE;

5. Prefix compression and Advanced Index Compression are different mechanisms

Prefix compression (key compression) stores repeated leading-key prefixes once per leaf-block structure and is particularly useful when leading composite columns repeat. It is available in Oracle AI Database Free and does not require the Advanced Compression option in the current licensing matrix for the offerings where it is listed.

Advanced Index Compression is a different block-level mechanism with LOW and HIGH levels. Current 26ai syntax requires COMPATIBLE >= 12.1 for LOW and >= 12.2 for HIGH. It is included in Free, but on EE/EE-ES the current licensing matrix says it requires the Oracle Advanced Compression option. That offering distinction is why this mandatory lab uses prefix compression.

CREATE INDEX sh08_region_status_comp_ixON servicehub_ix_case(region_code, status_code, work_order_id)COMPRESS 2;SELECT index_name, compression, prefix_lengthFROM user_indexesWHERE index_name = 'SH08_REGION_STATUS_COMP_IX';

6. Hands-on lab: design, test invisibly, and compare metadata

DROP TABLE servicehub_ix_case IF EXISTS PURGE;CREATE TABLE servicehub_ix_case (    work_order_id NUMBER PRIMARY KEY,    region_code   VARCHAR2(12) NOT NULL,    status_code   VARCHAR2(12) NOT NULL,    external_ref  VARCHAR2(40) NOT NULL,    created_at    TIMESTAMP NOT NULL,    amount        NUMBER(10,2) NOT NULL);INSERT INTO servicehub_ix_caseSELECT    LEVEL,    CASE MOD(LEVEL,4)      WHEN 0 THEN 'NORTH'      WHEN 1 THEN 'SOUTH'      WHEN 2 THEN 'EAST'      ELSE 'WEST'    END,    CASE MOD(LEVEL,3)      WHEN 0 THEN 'OPEN'      WHEN 1 THEN 'CLOSED'      ELSE 'HOLD'    END,    'ref-' || TO_CHAR(LEVEL),    TIMESTAMP '2026-01-01 00:00:00' + NUMTODSINTERVAL(LEVEL, 'MINUTE'),    MOD(LEVEL * 17, 10000) / 10FROM dualCONNECT BY LEVEL <= 5000;CREATE INDEX sh08_region_status_dt_ixON servicehub_ix_case(region_code, status_code, created_at DESC);CREATE INDEX sh08_upper_ref_ixON servicehub_ix_case(UPPER(external_ref));CREATE INDEX sh08_region_status_comp_ixON servicehub_ix_case(region_code, status_code, work_order_id)COMPRESS 2;BEGIN    DBMS_STATS.GATHER_TABLE_STATS(USER, 'SERVICEHUB_IX_CASE', cascade => TRUE);END;/SELECT    index_name, index_type, visibility, compression, prefix_length,    blevel, leaf_blocks, distinct_keysFROM user_indexesWHERE table_name = 'SERVICEHUB_IX_CASE'ORDER BY index_name;

ALTER SESSION SET optimizer_use_invisible_indexes = FALSE;DROP TABLE servicehub_ix_case PURGE;

7. Production judgment

Composite index design is workload design. Start from predicates, join keys, ordering, projection and write rate. Function-based indexes can make standardized expressions efficient, but they increase DML work and can be invalidated by dependencies. Invisible indexes are a controlled experiment, not proof of safe deletion. Compression trades CPU and maintenance characteristics for space/cache efficiency, so measure both storage and execution effects.

No restart is required for the lesson’s session-level invisibility test. Prefix compression is the mandatory path. Advanced Index Compression is described with its COMPATIBLE and licensing boundaries but is not required. Lesson 3 now changes the physical representation entirely: bitmap indexes are optimized for analytical boolean combinations but can create surprising DML lock scope.

Authoritative references

• CREATE INDEX — function-based, invisible, descending and compression syntax/restrictions
• Managing Indexes — invisible-index testing and prefix/advanced compression
• ALL_INDEXES — visibility, compression and index statistics
• Licensing Information — Advanced Index Compression, prefix compression and offering-specific entitlements
• DBMS_STATS — statistics collection