Re: Hash cluster, parameter SIZE
Date: Thu, 26 Nov 2009 09:48:47 -0800 (PST)
Message-ID: <30c7cbd3-58cc-4662-8809-ad35550e6399_at_r5g2000yqb.googlegroups.com>
On Nov 26, 4:32 am, abracadabuda <abracadab..._at_gmail.com> wrote:
snip
> 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/s...
>
> The only restriction I found when specifing SIZE is a note here:http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/clause...
> 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
32k is largest block size that oracle supports ... Received on Thu Nov 26 2009 - 11:48:47 CST