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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing

Post Details

Added on Mar 23 2025
5 comments
80 views