My Oracle Support Banner

Create Composite Index (Doc ID 2161330.1)

Last updated on JANUARY 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 to 12.1.0.2 [Release 10.2 to 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
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 XXX_PK,XXX_PRID,XXX_PID,XXX_DAT,XXX_CTY,XXX_IME,XXX_STA,XXX_DPRID,XXX_DID
FROM XXX
WHERE (XXX_PRID = :1 AND XXX_PID = :2 AND XXX_STA = :3 ) ;
 

Solution

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.