RE: How to Reduce Index clustering Factor without downtime

The question is which indexes are you range scanning and how many index entries make up the range scan.

In worst case for a supposed clustering factor problem each index entry would point to a different table block for the range scanning indexes.

Since you are testing for performance instead of addressing a real problems, this is a simple optimization problem for you to create a data set with distinct columns that have differing variability in index entries to table blocks.

Then you can perform benchmarks for each index with differing ranges of index entry to table block gets and test for each table configuration to match a specific index.  

In some indexes you may determine during this test depending on the SQL statement being issued to add more columns to the other range scan indexes than the one you have aligned the table to eliminate those index jumps from having to access table blocks versus solving for clustering factor along one index vector. You may also decide to do the same for the primary range scanning index. A different trade off than reorganizing the table, but it can get unwieldy if you are returning a lot of columns form the table.  

…or some people would simply recommend that you need a faster I/O subsystem to not worry about this problem unless you are range the full index as the query/data model are simply poorly formed.      

Matthew Parker

Chief Technologist

Dimensional DBA

425-891-7934 (cell)

D&B 047931344

CAGE 7J5S7 Dimensional.dba_at_comcast.net

 <http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn

www.dimensionaldba.com <http://www.dimensionaldba.com/>  

From: sumit Tyagi [mailto:dba.tyagisumit_at_gmail.com] Sent: Saturday, June 4, 2016 12:53 AM
To: Dimensional DBA
Cc: Oracle L
Subject: Re: How to Reduce Index clustering Factor without downtime  

Hi ,  

No performance issue . It was just part of understanding performance tuning method better . I was testing different scenarios on my Dev box .  

Thanks you for the suggestion . I will try to reduce CF using dbms redef.  

Also what factors shall we consider which sorting data for one particular column as it might cause issue for other indexes . Is there a way we can compare    

On Sat, Jun 4, 2016 at 12:59 PM, Dimensional DBA <dimensional.dba_at_comcast.net> wrote:

What is the performance problem that you are trying to solve that you believe reducing clustering factor will help you?  

In generic answer to your question you could use dbms_redefnition to move your table to being an IOT that matches the index you wish to mirror or if you do not want an IOT then you could just perform dbms_redefinition on a frequent enough basis to eliminate the problem you believe you have.  

I would answer the first question and determine if this is really a necessity.  

Matthew Parker

Chief Technologist

Dimensional DBA

425-891-7934 (cell)

D&B 047931344

CAGE 7J5S7 Dimensional.dba_at_comcast.net

 <http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn

www.dimensionaldba.com <http://www.dimensionaldba.com/>  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of sumit Tyagi Sent: Saturday, June 4, 2016 12:02 AM
To: Oracle L
Subject: How to Reduce Index clustering Factor without downtime  

Hi Oracle-L team ,  

How can Clustering Factor be Reduced : My Question is how to reduce CF in a production OLTP environment where table data is constantly changing and we can't afford a downtime .  

Clustering Factor (Doc ID 39836.1)

The only method to affect the clustering factor is to sort and then store the rows in the table in the same order as in they appear in the index. Exporting rows and putting them back in the same order that they appeared originally will have no affect. Remember that ordering the rows to suit one index may have detrimental effects on the choice of other indexes.      

--
 

--

BR

Sumit Tyagi

+91-7829543355  

--
 

--

BR

Sumit Tyagi

+91-7829543355

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jun 04 2016 - 10:30:25 CEST