Home » RDBMS Server » Performance Tuning » how to reduce the clustering_factor's value? (10g,linux)
how to reduce the clustering_factor's value? [message #556984] Thu, 07 June 2012 22:02 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
hi all,how to reduce the clustering_factor's value which appears in the user_indexes view?

in my table ,its clustering factor's value is so high:

SQL> SELECT UI.clustering_factor,UI.num_rows,UI.index_type,UI.distinct_keys FROM USER_INDEXES UI WHERE UI.table_name = 'TAWB_AWB';

CLUSTERING_FACTOR   NUM_ROWS INDEX_TYPE                  DISTINCT_KEYS
----------------- ---------- --------------------------- -------------
            83609     187603 NORMAL                             187603



and its block numbers is 5063

SQL> SELECT COUNT(DISTINCT DBMS_ROWID.rowid_block_number(ROWID)) BLOCK_NUM FROM TAWB_AWB A;

 BLOCK_NUM
----------
      5063

Re: how to reduce the clustering_factor's value? [message #556985 is a reply to message #556984] Thu, 07 June 2012 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT COUNT(*) FROM USER_INDEXES UI WHERE UI.table_name = 'TAWB_AWB';

post results from SQL above
Re: how to reduce the clustering_factor's value? [message #556987 is a reply to message #556985] Thu, 07 June 2012 22:33 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
SQL> SELECT COUNT(*) FROM USER_INDEXES UI WHERE UI.table_name = 'TAWB_AWB';

  COUNT(*)
----------
         1

Re: how to reduce the clustering_factor's value? [message #556989 is a reply to message #556984] Thu, 07 June 2012 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create table tmp as select * from mytab;
truncate table mytab;
insert into mytab select * from tmp order by <my index columns>;


Regards
Michel
Re: how to reduce the clustering_factor's value? [message #556994 is a reply to message #556989] Fri, 08 June 2012 00:36 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Hi,Michel Cadot and BlackSwan, why the clustering_factor's value is so high ? what happened with the table?
Re: how to reduce the clustering_factor's value? [message #556995 is a reply to message #556994] Fri, 08 June 2012 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
why the clustering_factor's value is so high ?

Because it is by its definition.

Quote:
what happened with the table?


The rows are not ordered in the table as the kays are in the index.
This is the definition of the clustering factor.

Regards
Michel
Re: how to reduce the clustering_factor's value? [message #557006 is a reply to message #556995] Fri, 08 June 2012 02:25 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
how to judge whether the value high or not ?
Re: how to reduce the clustering_factor's value? [message #557008 is a reply to message #557006] Fri, 08 June 2012 02:39 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
lonion wrote on Fri, 08 June 2012 08:25
how to judge whether the value high or not ?



http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1032431852141
Re: how to reduce the clustering_factor's value? [message #557009 is a reply to message #557006] Fri, 08 June 2012 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The lowest value is the number of blocks (of the table).
The highest value is the number of rows.
Lower is the value, better it is.

Regards
Michel

Re: how to reduce the clustering_factor's value? [message #557013 is a reply to message #556989] Fri, 08 June 2012 02:55 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Michel Cadot wrote on Fri, 08 June 2012 12:26
create table tmp as select * from mytab;
truncate table mytab;
insert into mytab select * from tmp order by <my index columns>;


Regards
Michel


maybe this is the only method to reduce the value .

thank u ,all.
Re: how to reduce the clustering_factor's value? [message #557016 is a reply to message #557013] Fri, 08 June 2012 03:24 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure.

Regards
Michel
Previous Topic: Optimizing sql
Next Topic: about V$SQL ......
Goto Forum:
  


Current Time: Thu Mar 28 06:34:20 CDT 2024