Create Composite Index (Doc ID 2161330.1)

Last updated on JULY 24, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 12.1.0.2 [Release 10.2 to 12.1]
Information in this document applies to any platform.

Goal

We have a table that makes a Full table scan.

At start of the application the query had 0.1 second execution time, now after 5 months it's 0.3 seconds.
The query is simple and has only a Primary key, no other indexes defined.

What is the best practice to create the index?

The problematic query looks like this:


SELECT TRS_PK,TRS_PRID,TRS_PID,TRS_DAT,TRS_CTY,TRS_IME,TRS_STA,TRS_DPRID,TRS_DID
FROM TRS
WHERE (TRS_PRID = :1 AND TRS_PID = :2 AND TRS_STA = :3 ) ;
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms