How to use CTAS to improve clustering factors on Oracle RDBMS to improve SQL performance (Doc ID 2065790.1)

Last updated on JANUARY 10, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.50 and later
PeopleSoft Enterprise HCM Global Payroll Core - Version 9.1 and later
Information in this document applies to any platform.

Goal

Over time, the order in which rows are inserted into a table can significantly differ from the order in which rows are commonly selected from that table.  While indexes can improve the ability to identify table rows, there may come a point where the order of the rows in the table diverges so significantly from the key order of ALL of the indexes that performance can degrade significantly because the indexes are no longer an efficient method of retrieving the data. When this condition occurs, the optimizer will tend to choose full table scans where an index would previously have been more effective. Additionally, changes in join order often occurs, further degrading performance. 

To remedy this condition, reordering the rows in the table so that the rows order more closely matches the most common use cases can restore application performance.  In PeopleSoft Application, the default, unique index is a good indicator of the row order that the applications will typically access the table data.  This document will explain the process of reorganizing the rows in a table.

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