OraFAQ Forum: Performance Tuning » How to change value of CLUSTERING

Home » RDBMS Server » Performance Tuning » How to change value of CLUSTERING_FACTOR of index (oracle 10g hpux11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to change value of CLUSTERING_FACTOR of index [message #349111]

Thu, 18 September 2008 23:22

reyazan
Messages: 53
Registered: February 2005

Member

How to change value of CLUSTERING_FACTOR of index?

Report message to a moderator

Re: How to change value of CLUSTERING_FACTOR of index [message #349118 is a reply to message #349111]

Thu, 18 September 2008 23:47

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: How to change value of CLUSTERING_FACTOR of index [message #349170 is a reply to message #349111]

Fri, 19 September 2008 03:10

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;

Report message to a moderator

Re: How to change value of CLUSTERING_FACTOR of index [message #349403 is a reply to message #349111]

Sun, 21 September 2008 09:10

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

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

Report message to a moderator

Previous Topic:	Relationsip between Parallel Degree & Clustering Factor
Next Topic:	Why This Execution Plan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 19:52:07 CDT 2024