Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Performance Tuning Unique index on repeated values columns and TABLE ACCESS (FULL)

Waqas AhmedMar 23 2025 — edited Mar 23 2025

Dear experts,

Execution plan of query showing huge cost and TABLE ACCESS (FULL). I checked the table, it has unique composite index ( on four columns). All four columns has repeated values. I need your help to understand plan, why unique index on repeated valuse column and suggestions to fix this performance issue.

Plan Hash: 2387984269 (child number 0)

Rows marked with '*' have predicates, which are listed below.

-----------------------------------------------------------------------------------------------------
| Id | Operation | Bytes | Cost | Rows | Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT Optimizer=ALL_ROWS | | 80087 | | |
| 1 | SORT (AGGREGATE) | 20 | | 1 | |
|* 2 | TABLE ACCESS (FULL) OF schema.table_name (TABLE) | 220 | 80087 | 11 | 4 |
-----------------------------------------------------------------------------------------------------

Predicate Information by operation id:
-----------------------------------------------------------------------------------------------------

2 - filter: ("UPLOAD_TRX_NO"=:B4 AND "PRICR_KEY"=:B2 AND "EXCESS"=:B1 AND "ENDT_NO"=:B3)

All four columns in predicate are indexed

Index script:

CREATE UNIQUE INDEX schema_name.index_name ON schema_name.Table_name
(UPLOAD_TRX_NO, ENDT_NO, SERIAL, PRICR_KEY, EXCESS)

DB Version: 19c RAC

OS version: Oracle linux.

Regards.

Comments
Post Details
Added on Mar 23 2025
7 comments
384 views