Re: How to calculate parameters for indexes?

Here's how I do it.

Table T
f1 varchar2(20)
f2 number(10)
f3 varchar2(10)
f4 number

Currently has 50K rows
adds 10K per month

assuming no updates
assuming semi-annual rebuild of index
total space = 20+6+10+6 bytes * (50K + 10K + 10K + 10K+10K+10K) = 4,200,000 mbytes in 6 months.
I size all storage for between 4 and 6 extents prior to rebuild, thus

This will have about 5 extents at the end of 6 months totalling 5Mbytes create index I_T_f1 on T (f1)
tablsapace MY_INDEX_TABLESPACE
storage (initial 1M next 1M pctincrease 0) pctfree 0 /

If you are experiencing periodic UPDATEs (changes to existing data) then you should size thus:

storage (initial 1.7M next 1.7M pctincrease 0) pctfree 70

Note: this is only one strategy which works well for the kind of usage at my site (primarily data warehousing). Pick a reasonable strategy, monitor it,
then improve it.

<skadhane_at_my-deja.com> wrote in message news:8ooj17$ptm$1_at_nnrp1.deja.com...
> What is the way to calculate parameters( initial and next) while
> creating indexes?
>
> Say i want to create a index on 4 columns of a table which has 50,000
> rows which has
> monthly growth of 10,000 rows.The columns are
> varchar2(20),number(10),varchar2(10),number.
>
> What factors will you consider so that it index wont use more no of
> extents.
> And what are the other things that should consider while creating
> indexes.
>
> Thanks
>
> SK
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Sep 02 2000 - 22:16:23 CDT