My Oracle Support Banner

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

Last updated on DECEMBER 17, 2019

Applies to:

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


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.


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

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