Re: Cluster Gobbling Space. Help!

There are a couple of oddities in your notes. Your 'select nvl(avg(vsize)))' covers only one column - can we assume (a) there is only one extra column per table (apart from cluster key columns) or (b) you are using the sample to show us the method, but did actually add up the vsize for all the columns (excluding cluster key) in each table.

Also - do we assume that you expect to get ca. 2 B rows for each A row ?

My first thought (given that number of blocks is very close to number of A rows) is that you created the cluster without defining the SIZE paramter, so Oracle is using one block per cluster key.

If your row sizing is reasonably accurare, and my assumptions are correct, then you need to redefine the cluster with a SIZE somewhere around 220 to 250

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Chakravarthy KM Nalamotu wrote in message <7n858p$ql8_at_news.Hawaii.Edu>...
>Dear Oracle Users,
>I am having a serious space problem with a table cluster
>on a 8.0.4.3 database on Solaris.
>
>I have two tables A and B in a CLUSTER.
>table A has 3 million rows
>table B has 6 million rows
>
>I have obtained the average rowsize for each table
>using
>
>select avg(nvl(vsize(col_a),0))
>from table_A;
>
>Avg Row length = 55 and Rows per 4K block = ~50
>#of blocks required = 3 million/50 = 60,000 blocks
>spacre required= approx 300MB
>
>select avg(nvl(vsize(col_b),0))
>from table_B;
>
>Avg Row length = 41 and Rows per 4K block = ~75
>#of blocks required = 6 million/75 = 30,000 blocks
>space required= approx 400MB
>
>My cluster is taking up a whopping 2895360 blocks = 12GB space.
>It shouldn't even be using 1GB since I should have saved space
>using a cluster.
Received on Fri Jul 23 1999 - 03:38:39 CDT