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.