Home » SQL & PL/SQL » SQL & PL/SQL » Help Creating Single Table Cluster (Win2003, 11g (11.1.0.7.0))
Help Creating Single Table Cluster [message #446240] Sat, 06 March 2010 18:22 Go to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Hi,

Can someone help me determine, or confirm that I am building a cluster table correctly?

I have one table called posh, which has about 50,000 ids (millions of rows) so far. It is currently at about 50 gb. So each series of rows for a particular id is about 1 mb.

CREATE CLUSTER posh_cluster (id int)
SIZE 2M
STORAGE (initial 2000K next 2000K)
PARALLEL 4;

CREATE INDEX posh_cluster_index ON CLUSTER posh_cluster;

CREATE TABLE posh (
  id int references identification(id),
  stamp timestamp,
  fields  (about 40 fields),
constraint posh_id_time_pki UNIQUE(id,stamp), 
) CLUSTER posh_cluster(id);


According to the Oracle documentation size is "the average cluster key size", I am not really sure what that means. Is this the 1mb in posh table space I am using for each series of rows for an id, or the space used for the index? Secondly, the storage parameter for 'CREATE CLUSTER' should be what?

Thanks.
Re: Help Creating Single Table Cluster [message #446241 is a reply to message #446240] Sat, 06 March 2010 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>I have one table called posh, which has about 50,000 ids (millions of rows) so far. It is currently at about 50 gb. So each series of rows for a particular id is about 1 mb.

Why is Single Table Cluster an appropriate or best solution?

What business problem needs to be solved?
Re: Help Creating Single Table Cluster [message #446243 is a reply to message #446241] Sat, 06 March 2010 21:49 Go to previous messageGo to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Most of my queries pull by id. So I pull all (or most) data for a certain id, multiple times.

Currently, the queries a read i/o bounded. I am trying to reduce reading the blocks. If all the rows for an id is pulled at once, the queries would be much quicker.

Partitioning doesn't really work due to the large number of ids.

Thanks.
Re: Help Creating Single Table Cluster [message #446245 is a reply to message #446243] Sat, 06 March 2010 22:01 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>Currently, the queries a read i/o bounded

It would be really helpful if we could see the SQL and the explain plan report generated using DBMS_XPLAN.
Re: Help Creating Single Table Cluster [message #446246 is a reply to message #446245] Sat, 06 March 2010 22:31 Go to previous messageGo to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Query:
select * from posh where id = 4543 and stamp between to_date('01-JAN-06') and to_date('01-JAN-10')


Plan:
SELECT STATEMENT	ALL_ROWS	1	1 5389					
FILTER							TO_DATE('01-JAN-06')<=TO_DATE('01-JAN-10')
TABLE ACCESS(BY INDEX ROWID) POSH		1	1	5389					
INDEX(RANGE SCAN) POSH_ID_TIME_PKI		1	1 "PVID"=4543 AND "STAMP">=TO_DATE('01-JAN-06') AND "STAMP"<=TO_DATE('01-JAN-10')	



The query is accurately using the indexes, it is just that I need to read so many blocks that it is slowing down. Sure, I can recreate the table using CTAS and performance is much better, but as data is being added, performance decreases... which is why I want to cluster & keep the like data (id) together.

Thanks.

[Updated on: Sun, 07 March 2010 01:31] by Moderator

Report message to a moderator

Re: Help Creating Single Table Cluster [message #446247 is a reply to message #446246] Sat, 06 March 2010 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
>TO_DATE('01-JAN-06')

TO_DATE needs format mask.

TO_DATE('01-JAN-2006','DD-MON-YYYY')
Re: Help Creating Single Table Cluster [message #446249 is a reply to message #446247] Sun, 07 March 2010 01:42 Go to previous messageGo to next message
John Watson
Messages: 6396
Registered: January 2010
Location: Global Village
Senior Member
You are creating an index cluster, is that intended? I can see no purpose to a single table index cluster, whereas a hash cluster (given that your select uses equality) might help a lot - hash clusters also have the SINGLE TABLE clause.
You could even use the sorted hash cluster structure: I've used that effectively for telco billing, where I needed to retrieve all rows with equality predicate on the phone number and range predicate on date. Is that what you are doing?
The SIZE clause is the space assigned for all the rows with the same key (or hash) value, it will still work if you get it wrong, but not as efficiently.

[Updated on: Sun, 07 March 2010 01:46]

Report message to a moderator

Re: Help Creating Single Table Cluster [message #446275 is a reply to message #446249] Sun, 07 March 2010 07:45 Go to previous messageGo to next message
db33
Messages: 21
Registered: July 2009
Junior Member
I've looked at the hash clusters, but by specifying where to store, it puts a limit on the number of keys. I expect mt ids to grow over time.

Other than converting the key to a hash, is there any other advantage to hash cluster?

Re: Help Creating Single Table Cluster [message #446294 is a reply to message #446275] Sun, 07 March 2010 11:44 Go to previous messageGo to next message
John Watson
Messages: 6396
Registered: January 2010
Location: Global Village
Senior Member
I don't see why there should be any limit on the number of keys - if you have 50000 now, there should be no problem in specifying, say, 1000000 to allow for growth. The advantage would be that you don't need an index at all: the rows will be located by calculation on the hash key, which is CPU intensive rather than a disc intensive index search.
Look at it from the other direction: an index cluster is intended for de-normalizing tables joined on a common key. If you have only one table, what is the point? You might be better off if you prefix the primary key with the ID column, and store it as an IOT.

Re: Help Creating Single Table Cluster [message #446299 is a reply to message #446294] Sun, 07 March 2010 12:34 Go to previous messageGo to next message
db33
Messages: 21
Registered: July 2009
Junior Member
Interesting. Thanks.

Follow up, what if some of the ids go over the 2M cluster size. Does one provide a benefit over the other? The cluster index would have another section allocated to it.

The hash couldn't do that, would it span to a spot on the disk not used by the cluster, or would try to overwrite other spots?

Thanks.
Re: Help Creating Single Table Cluster [message #446301 is a reply to message #446299] Sun, 07 March 2010 12:42 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
By running your own benchmark tests, you could actually answer your questions YOURSELF.
Re: Help Creating Single Table Cluster [message #446310 is a reply to message #446294] Sun, 07 March 2010 16:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
John Watson wrote on Mon, 08 March 2010 04:44
Look at it from the other direction: an index cluster is intended for de-normalizing tables joined on a common key.


I don't entirely agree with this. I think clusters - both types- are designed to co-locate rows that are commonly accessed together. They may all reside in one table, or in two or more tables.

The downside of hash clusters is that they need to be reorganised periodically as the table grows. Yes, you can pre-allocate extra space, but then that impacts full-table-scan performance.

Eventually you will have to reorganise, and the bigger the table, the harder that will be.

All in all, index clusters have a lower maintenance overhead. But as John Watson rightly points out, Hash Clusters have better cluster key access performance.

Personally, I agree with the OP - try an index cluster. But if you are prepared to cop the maintenance, try out a hash cluster - even if its just for benchmarking.

Ross Leishman
Previous Topic: Query Help
Next Topic: Joins
Goto Forum:
  


Current Time: Sat Sep 24 19:48:44 CDT 2016

Total time taken to generate the page: 0.05961 seconds