Hash cluster, parameter SIZE

From: abracadabuda <abracadabuda_at_gmail.com>
Date: Thu, 26 Nov 2009 01:32:15 -0800 (PST)
Message-ID: <045799c6-9fc9-4008-8d23-3a6158fb9348_at_m3g2000yqf.googlegroups.com>

Hi everyone.

I have a table with transactions of customers. There are about 20 transactions per month for a customer, I need to store history of 24 months. There is about 1 500 000 customers. Average length of row is 300 bytes.

The only select statements on this table would be count of transactions, balance of transactions, etc., etc of one customer. There are no updates, inserting and deleting is running in batch. So I decided to use hash cluster, with hash on customer_number.

Size of one db block is 8KB. One block therefore cannot store all records of one customer, so I tried to set parameter SIZE of the cluster to a higher value. I calculated the space for one custmer = 20 transations * 24 months * 300 bytes = 144 000 bytes, that's 140,625 KB, so I decided to use the SIZE of 144KB.

I tried to create the cluster with mentioned SIZE (144KB), but I got this error message:
Error at line 1
ORA-02229: invalid SIZE option value

Action, I googled for this error is:
Specify an appropriate value.

I tried to decrease the SIZE value, the highest accepted SIZE value was 32766 bytes (it didn't even accept 32K)

Only restriction I found in Oracle documentation is, that it has to be an integer value. There is no restriction when specifing SIZE parameter when creating cluster:
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5001.htm

The only restriction I found when specifing SIZE is a note here: http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/clauses008.htm Note:
Not all multiples of bytes are appropriate in all cases, and context- sensitive limitations may apply. In the latter case, Oracle issues an error message.

Create statement for my cluster:
CREATE CLUSTER CLS$TEST_HASH
(
  cust_no VARCHAR2(10)
)
TABLESPACE DATL_TEST
PCTUSED 0
PCTFREE 0
INITRANS 2
MAXTRANS 255
SIZE 144K
STORAGE (

            INITIAL          256K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )

HASHKEYS 2000003
HASH IS mod(to_number(cust_no), 2000003) NOROWDEPENDENCIES
NOCACHE
NOPARALLEL; Can you please help me? I cannot find anything useful anywhere :( What are the context-sensitive limitations for creating cluster with specified SIZE?

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Thanks a lot.
abracadabuda Received on Thu Nov 26 2009 - 03:32:15 CST