> Message: 5
> Date: Sat, 23 Nov 2002 16:04:40 -0500
> From: "Ann W. Harrison" <aharrison@...>
> Subject: Re: Max number of indexes
>
> At 05:58 PM 11/21/2002 +0000, Chris Bland wrote:
>
> >The data is loaded in, and is read only after that. We can't predict with
> >any certainty which columns will be drilled into, although we
> are performing
> >some analysis on the columns which will allow us to determine which ones
> >will benefit most from indexing. The number of columns is
> arbitrary - some
> >users will have very wide files, e.g. 250+ attributes.
> >
> > >From what I have gleaned, sparse domains are questionable,
> with selectivity
> >being the main deciding factor.
> >
> >Nevertheless, it would be useful not to have to worry about this maximum
> >limit, just applying indexes as we deem appropriate.
> >
> >Is it there for a particular reason? Is it possible to make it go away...

Many thanks for the response. However, I am still getting the same error:

unsuccessful metadata update
-maximum indexes per table (64) exceeded
SQL> create index i99 on test_tab (myint99);
Statement failed, SQLCODE = -607

regardless of the page size chosen. In this particular case, the page size
was 16k, but the same error happened with 8k pages, (and indeed with my
original 4k page database). The OS is Win2k Pro (SP2).

I created a test table with 100 integer columns (I added no data to it), and
then created an index on each column, calling the indexes 'in' where n is
0-99, to keep the name as short as possible. The error message always
reports 64 as the maximum.

Any ideas? Has this limit been added at some stage, to ensure that the max
number of indices is consistent regardless of naming or page size?

Help and/or comments very much appreciated. This limit is something we
definitely want to remove, or manage within the practical boundaries
explained by Ann.

Regards
Chris

> In fact, the number of indexes that can be created is a function
> of the page size and the length of the index name. Larger page sizes
> with shorter names yield more indexes. Do create indexes after the
> data is stored. For a read-only application, indexes with lots of
> duplicates are not great problem - though they would be on read/write
> data.
>
> Regards,
>
> Ann
> www.ibphoenix.com
> We have answers.
>