Home » RDBMS Server » Performance Tuning » How to change value of CLUSTERING_FACTOR of index (oracle 10g hpux11)
How to change value of CLUSTERING_FACTOR of index [message #349111] Thu, 18 September 2008 23:22 Go to next message
reyazan
Messages: 53
Registered: February 2005
Member
How to change value of CLUSTERING_FACTOR of index?
Re: How to change value of CLUSTERING_FACTOR of index [message #349118 is a reply to message #349111] Thu, 18 September 2008 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Recreate the table to move the rows in the order of the index columns.

Regards
Michel
Re: How to change value of CLUSTERING_FACTOR of index [message #349170 is a reply to message #349111] Fri, 19 September 2008 03:10 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
it is two ways
change clustering_factor of index for true,
so you need reorganise table ordering it by indexed column,

or you can put fake statistics and change clustering_factor value to another like that

begin

DBMS_STATS.SET_INDEX_STATS (

ownname  =>'SCOTT',

indname  => 'EMP_ENAME_I',

clstfct  => 100); -- NEW clustering factor value

end;

Re: How to change value of CLUSTERING_FACTOR of index [message #349403 is a reply to message #349111] Sun, 21 September 2008 09:10 Go to previous messageGo to next message
VivekSharma
Messages: 2
Registered: September 2008
Location: Mumbai, India
Junior Member

As suggested by others, there are multiple ways :

1. Re-organize the table to sort by way of Indexed Columns, and/or
2. Use dbms_stats to manually change the clustering factor of an Index.

With option 1, you can improve the CF of an Index, but then the impact on other indexes can cause performance issues as well.

For a Composite Index, if the CF is impacted due to the non-leading column, then there is a way to compute CF for the index on single column and then dbms_stats can be used to update the statistics.

Read http://viveklsharma.blogspot.com/2008/09/predicting-performance-impact-due-to.html for more info on this.

Regards
Vivek

[Updated on: Sun, 21 September 2008 11:38] by Moderator

Report message to a moderator

Re: How to change value of CLUSTERING_FACTOR of index [message #349428 is a reply to message #349111] Sun, 21 September 2008 14:38 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
And just to point out
Quote:
1. Re-organize the table to sort by way of Indexed Columns, and/or
2. Use dbms_stats to manually change the clustering factor of an Index.


#1 Will improve the actual clustering while #2 is just
changing a statistic, and will not change actual clustering.

Regards
Harry
Previous Topic: Relationsip between Parallel Degree & Clustering Factor
Next Topic: Why This Execution Plan
Goto Forum:
  


Current Time: Fri Dec 09 19:38:18 CST 2016

Total time taken to generate the page: 0.11853 seconds