Re: Capacity planning for VERY large table

Hi.

 First things first:

1. How can you keep the last 10% of the data when you don't appear to have an 'age value' in the description, and when the data is 'static' ? I didn't mention it but there is an "TIMESTAMP" as part of description, so it is possible to keep only recent data.

 I intended to partition it on the first ( F01 ) column and to define  256 partitions.
 The problem is that it's possible to define only ONE local bitmap  index for such a table.

 I just finished following benchmark:

1. Define 20 tables TAB_01 to TAB_20 with following columns: ( F02 NUMBER(3), F03 NUMBER(3), ... F24 NUMBER(3), DESCRIPTION VARCHAR2(20));
2. Populate the tables with 8M rows each (even distribution of data).
3. Create 7 BITMAP indexes for each table on F02 - F08 columns.
4. Runs following statement: SELECT description /* Access DATA */ FROM tab_01 WHERE f02 between 1 and 20 and f03 between 77 and 97 and f03 between 33 and 53 and ... F08 between 211 and 231 UNION ALL SELECT description /* Access DATA */ FROM tab_02 WHERE f02 between 1 and 20 and f03 between 77 and 97 and f03 between 33 and 53 and ... F08 between 211 and 231 UNION ALL SELECT description /* Access DATA */ FROM tab_03 WHERE f02 between 1 and 20 and f03 between 77 and 97 and f03 between 33 and 53 and ... F08 between 211 and 231 UNION ALL ...

     SELECT description /* Access DATA */ FROM tab_20
        WHERE f02 between 1 and 20 and f03 between 77 and 97 and
              f03 between 33 and 53 and ... F08 between 211 and 231;

   Following hardware/software configuration:    Win/NT 4.0 SP 5
   Intel P III / 933 Mhz ( Single CPU )
   Memory : 512 Mb
   Oracle 8.1.6.0 EE
   2 disks ( 1 SCSI 1 IDE ) - 24Gb each.

   The response times ( after indexes are loaded into memory ) are quite    linear for UNIONing 2 to 20 such tables and are varying from

   0.11 sec ( 2 tables )
   0.51 sec ( 10 tables )
   1.1  sec ( 20 tables )

   So I think that with larger memory, faster ( and more ) disks and    with multiple CPUs it will be possible to get the response time    within 1 sec.

  Thanx. Michael.

In article <970005007.10304.0.nnrp-10.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> It is possible to have local bitmap indexes
> on partitioned tables. But I'm not sure this
> helps you, what would you partition on ?
>
> Perhaps something like 16 range partitions
> on F01 with 16 composite partitions on F02,
> or 32/8.
>
> How can you keep the last 10% of the data
> when you don't appear to have an 'age value'
> in the description, and when the data is 'static' ?
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Michael Bialik wrote in message <8qr0q3$d39$1_at_nnrp1.deja.com>...
> >Thanx for responding.
> >
> > Here are the answers for your questions:
> >
> > 1. Do all 25 columns always appear in all queries ?
> > -- Yes.
> > 2. Do the typical ranges for each predicate cover about 20 values ?
> > -- It ALWAYS cover 20 values.
> > 3. Are the values 0..255 used fairly evenly in any given column ?
> > -- Yes again. It is safe to assume that the values have an
> > even dispersion for each column.
> > 4. Is the distribution of values in a column highly clustered, or
 very
> > scattered ?
> > -- My "educated guess" - the values ARE clustered.
> > 5. Is this very static data so that it can be bitmap indexed ?
> > -- The data is static, so I thought about using bitmap indexes
> > already. There is ( of course ) a problem that it is NOT
> > possible to use bitmap indexes for partitioned table, so I'm
> > checking the possibility to define 256 table and generate
> > dynamic SQL to perform an UNION of 20 such tables ( for the
> > first column ).
> > All other indexes will be bitmap.
> >
> > I'm trying to convince the application designers to keep only last
> > 10% of data ( may be even less ). Still the data volumes are going
 to
> > be impressive.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Sep 30 2000 - 01:55:01 CDT