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@xxxxxxxxxxx
<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@xxxxxxxxx] ;
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@xxxxxxxxxxx>
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@xxxxxxxxxxx
<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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
