Re: Capacity planning for VERY large table

Hi.
 The ranges vary ( 1 to 236 scenario ) and the same holds for every  column. The interval is fixed, but "FROM" <-> "TO" bounds are  variing from 1..20 to 236..256.

 Thanx. Michael.

In article <8qr3hv$es8$1_at_bob.news.rcn.net>,   "Steve Long" <steven.long_at_erols.com> wrote:
> please clarify one (crucial) item about ranges always covering 20
> values...are the intervals for the ranges well defined or fixed, ie
 always
> 1..20, 21..40, 41..60 for a given column, or can the end points on the
> ranges vary from 1 to 236, ie 1..20, 2..21, 3..22, ..., 236..255 are
 all
> possible.
>
> if they are fixed for a given column, what are they for each column?
>
> "Michael Bialik" <michael_bialik_at_my-deja.com> wrote in message
> news: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.
> >
> > Thanx again all "respondees". Michael.
> >
> >
> >
> > In article <969958077.2130.1.nnrp-12.9e984b29_at_news.demon.co.uk>,
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> > >
> > >
> > > Interesting challenge.
> > >
> > > Do all 25 columns always appear in all queries ?
> > > Do the typical ranges for each predicate cover about 20
 values ?
> > > Are the values 0..255 used fairly evenly in any given column ?
> > > Is the distribution of values in a column highly clustered, or
 very
> > > scattered ?
> > > Is this very static data so that it can be bitmap indexed ?
> > >
> > > When the smoke has died down, how many rows should
> > > a single query return in a 'typical' query.
> > >
> > > Arithmetic:
> > > 256 values per column to cover 25,000 000 000 rows
> > > Selecting on a range of 21 values
> > > => one column in predicate targets 10% of the data
> > >
> > > To reduce the target data to 25 rows, (for sub-second
> > > access - assume 25 rows = 25 physical disc reads)
> > > we need to apply the predicates for 9 columns.
> > >
> > > Estimate of size of bitmap,
> > > assuming poor compression: one byte per row
> > > assuming terrific compression: one bit per row
> > >
> > > In a good case, each bitmap index would be 3GB,
> > > in a poor case 25GB - and you need to keep about
> > > 10% of nine of them in memory to achieve sub-second
> > > responses.
> > >
> > > Even if Oracle uses a perfect range reduction
> > > algorithm, the very first step of the query has
> > > to process that 3GB.
> > >
> > > I think you need much tighter queries, with predicates
> > > on columns with far higher cardinality before you can
> > > hope to get high-speed queries.
> > >
> > > --
> > >
> > > Jonathan Lewis
> > > Yet another Oracle-related web site:
 http://www.jlcomp.demon.co.uk
> > >
> > > Michael Bialik wrote in message <8qoc4j$cfc$1_at_nnrp1.deja.com>...
> > > >Hi.
> > > >
> > > > I have a very large table ( 25 * 10**9 rows = 25,000,000,000
 rows ).
> > > > The row length is 60 bytes, so the table size is 1.5Tb - 2.0 Tb
> > > > ( including Oracle overhead ).
> > > >
> > > > The table contains 24 numeric fields and 1 description.
> > > > Each numeric field ( F01, F02, ... F24 ) may contain values
> > > > from 0 to 255 only.
> > > >
> > > > The SELECT is going to look like that:
> > > >
> > > > SELECT description FROM large_table
> > > > WHERE
> > > > F01 between 24 and 44 AND
> > > > F02 between 66 and 86 AND
> > > > F03 between 1 and 21 AND
> > > > ...
> > > > F24 between 235 and 255.
> > > >
> > > > Does anybody worked with such big tables?
> > > > What response time ( with what type of computer ) may I expect?
> > > > Is it possible to achieve 1 second response time with Oracle (
 8i )?
> > > > On what computer?
> > > >
> > > > TIA. Michael
> > > >
> > > >
> > > >
> > > >
> > > >Sent via Deja.com http://www.deja.com/
> > > >Before you buy.
> > >
> > >
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

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