Home » RDBMS Server » Performance Tuning » How to identify table need resequencing. (10.2.0.3.0 on xp)
How to identify table need resequencing. [message #433621] Thu, 03 December 2009 05:32 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

vHi,
can you please suggest me how to identify what database table need
resequencing? is there any criteria or any statement thru which we
can gather table.
please suggest.
Re: How to identify table need resequencing. [message #433622 is a reply to message #433621] Thu, 03 December 2009 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define what you mean be resequencing.
Re: How to identify table need resequencing. [message #433623 is a reply to message #433621] Thu, 03 December 2009 05:36 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

in my context, resequencing means again arranging table data on the basic of index hint. CTAS method (by create table as select).

[Updated on: Thu, 03 December 2009 05:38]

Report message to a moderator

Re: How to identify table need resequencing. [message #433629 is a reply to message #433623] Thu, 03 December 2009 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does mean "arranging table data on the basic of index hint"?

Regards
Michel
Re: How to identify table need resequencing. [message #433632 is a reply to message #433623] Thu, 03 December 2009 06:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I would say that a table never NEEDS 'resequencing'

If you had a table that was mainly accessed by queries using the same access method (eg index range scans on the same index) and you didn't mind the fact that what you were going to do would adversely affect all other access paths then you could do what you're saying.

To see if it needs doing, have a look at the CLUSTERING_FACTOR on the index that you want to order the table by.

As Tom Kyte explains here if the clustering_factor is near to the number of blocks in the table, then your table reflects the order of that index. If the clustering factor is near the number of rows in the table, then the table is randomly ordered with regards to this index.
Previous Topic: DBMS_OUTLINE to override HINTS [Merged]
Next Topic: SQL via Crystal Reports and ODBC is very slow, same SQL with toad is fast
Goto Forum:
  


Current Time: Sat Dec 03 12:29:12 CST 2016

Total time taken to generate the page: 0.10153 seconds