Subject | Max number of indexes |
---|---|
Author | Chris Bland |
Post date | 2002-11-21T17:58:02Z |
Hi,
I'm sure this has been answered elsewhere, but I can't find it, so here
goes..
According to P27 of the (Interbase 6.0) operations guide, the maximum number
of indexes per table is 65,536 (design limit). According to P120 of the Data
Definition guide, it is 64. This seems to be the number that Firebird
enforces - try to create any more via ISQL, and it complains.
The situation is a bit unusual - we are developing some analysis software.
One function it will have is allow drill down on values within single
columns, to retrieve a row or rows. We are indexing to provide good
performance.
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...
:-)
Regards
Chris
I'm sure this has been answered elsewhere, but I can't find it, so here
goes..
According to P27 of the (Interbase 6.0) operations guide, the maximum number
of indexes per table is 65,536 (design limit). According to P120 of the Data
Definition guide, it is 64. This seems to be the number that Firebird
enforces - try to create any more via ISQL, and it complains.
The situation is a bit unusual - we are developing some analysis software.
One function it will have is allow drill down on values within single
columns, to retrieve a row or rows. We are indexing to provide good
performance.
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...
:-)
Regards
Chris