Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Capacity planning for VERY large table

Re: Capacity planning for VERY large table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 1 Oct 2000 11:09:20 +0100
Message-ID: <970395240.8792.0.nnrp-10.9e984b29@news.demon.co.uk>

Why do you think that it is only possible to create a single bitmap index. I've just run the following script on 8.1.5 which , in outline, would probably be the optimum strategy for you requirement.

drop table hash_test;
create table hash_test(

 date_part  date,
 f01   number(3),
 f02   number(3),
 f03   number(3),
 f04   number(3),
 f05   number(3),

 description varchar2(80)
)
partition by range (date_part)
subpartition by hash (f01) subpartitions 8 (
 partition p01 values less than (to_date('01-jan-2001','dd-mon-yyyy')),  partition p02 values less than (to_date('01-jan-2002','dd-mon-yyyy')) )
;
create bitmap index ht_2 on hash_test(f02) local;
create bitmap index ht_3 on hash_test(f03) local;
create bitmap index ht_4 on hash_test(f04) local;




Note -
you have tested 20 table with 8M rows for a total of 160M rows, with 7 indexes as a test for 25,000,000,000 - do you really think you can scale by a factor of 150 in a linear fashion ?
For example, how much space did your indexes total ? My guess would be around 140 MB - if so you would need an SGA of 21GB to get then all in memory.

BTW there is a logical inconsistency in your requirement. The data is distributed evenly with 256 values for each of 24 columns, so there are

    256 to the power 24 possible combinations of data     this is approx: 4 times 10 to the power 57 combinations.

    You are storing only 2.5 times 10 to the power 10 rows.     which is virtually none of them.

    Your query is restricting rows to roughly 10% on each column     so after 24 columns, will restrict the selection to one row     out of 10 to the power 24.

For the model to be useful there has to be a MASSIVE correlation between the data in the 24 columns - find out what it is.

--

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

Michael Bialik wrote in message <8r42o5$baa$1_at_nnrp1.deja.com>...

>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 Sun Oct 01 2000 - 05:09:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US