| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Capacity planning for VERY large table
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),
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>...Received on Sun Oct 01 2000 - 05:09:20 CDT
>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.
 |
 |