Home » RDBMS Server » Performance Tuning » Performance issue with Oracle Cluster
Performance issue with Oracle Cluster [message #324798] Wed, 04 June 2008 01:47 Go to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Dear All,

I am trying to use clusters to upgrade the performance of my queries.
I have few questions regarding clusters:

#How the performance varies with the increase in the number of associated rows?
#Can the clustered tables be partitioned further?
#what are the main disadvantages of using clustered tables?

Could you please help me find the answers to these questions.

Thanks in advance.

Sarwagya.
Re: Performance issue with Oracle Cluster [message #324803 is a reply to message #324798] Wed, 04 June 2008 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide
Chapter 15 Using Indexes and Clusters

Regards
Michel
Re: Performance issue with Oracle Cluster [message #324846 is a reply to message #324803] Wed, 04 June 2008 05:47 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Thanks Michel.

I went through the doc.
But got confused on the line:

Store a detail table alone in a cluster if you often select many detail records of the same master. This measure improves the performance of queries that select detail records of the same master, but does not decrease the performance of a full table scan on the master table. An alternative is to use an index organized table.

My question is:
How are the rows organized in a cluster if there is only one table in it?
Re: Performance issue with Oracle Cluster [message #324849 is a reply to message #324846] Wed, 04 June 2008 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How are the rows organized in a cluster if there is only one table in it?

In the order of the cluster column.

Regards
Michel
Re: Performance issue with Oracle Cluster [message #324857 is a reply to message #324849] Wed, 04 June 2008 06:26 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Then it seems good.

Is there any drawback of creating a single table cluster?


Also, I didn't find anything regarding my previous question:

Can a clustered table be partitioned later on if its size increases significantly?
Re: Performance issue with Oracle Cluster [message #324876 is a reply to message #324857] Wed, 04 June 2008 07:09 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any drawback of creating a single table cluster?


Yes you have to precisely determine size value otherwise you will either loose space (and increase FTS time) or get overflow block (and increase index access).

Quote:
Can a clustered table be partitioned later

No.

Regards
Michel
Previous Topic: View Performance tunning
Next Topic: query is taking index sacn,even though it is taking long time and it is not completing
Goto Forum:
  


Current Time: Sun Dec 11 07:54:47 CST 2016

Total time taken to generate the page: 0.09576 seconds