> On one of our live sites using SQL server, we do regular (hourly)
> bulk loads
> of data without any problems. There is certainly no need to do index
> rebuilds.
>
> Even if you use interbase, surely you do an index rebuild,
> without having to
> backup / restore, can't you?
Yup, of course you can.
from http://www.interbase.com/downloads/makeibscream.pdf
#6: Indexes
Defining indexes for some columns is part of designing a production
database. Indexes
dramatically improve performance of SELECT queries. The greater the number
of rows in the
table, the greater the benefit of using an index. Intelligently analyzing
your database and
defining indexes appropriately always improves performance.
An index in InterBase is a data structure inside the database file that
provides a quick lookup mechanism for the location of specific values in a
table. Queries make use of
appropriate indexes automatically by means of the cost-based optimizer,
which analyzes the
tables and columns used in the query and chooses indexes which speed up the
searching, sorting, or joining operations.
Indexes incur a small cost to maintain the index B-tree data structure
during INSERT and
UPDATE operations. Because of this cost, it is not recommended to be overly
liberal with index definitions. Dont create redundant indexes, and dont
make an index on every column as a
substitute for database usage analysis.
Indexes are actually detrimental to performance when created on a column
that has few unique
values. The classic example is a SEX column on a large table; the only
values are male, female,and perhaps unspecified. Maintaining such indexes is
expensive, and lookups are often more
expensive than if the lookup were done without using an index.
What uses an index:
7 Primary & Foreign keys
7 Sort keys, including DISTINCT and GROUP BY
7 Search criteria (WHERE)
What doesnt use an index:
7 Search criteria for CONTAINING, LIKE, <>
7 Columns used in aggregate functions, like COUNT()
7 Other expressions, like UPPER()
Directional indexes
Indexes are defined as either ASCENDING or DESCENDING. To sort in both
directions, you
need one index of each type. This is also very important if you are using a
scrolling list in a Delphi form.
Tuning indexes
The selectivity of an index is an indicator of its uniqueness. The optimizer
uses selectivity in its
cost-based analysis algorithm when deciding whether to use a given index in
a query execution
plan. If the selectivity is out of date and doesn't accurately represent the
state of the index, the
optimizer might use or discount the index inappropriately. This doesnt
usually have a great
performance penalty unless the selectivity is highly out of date. To
recompute an index
selectivity:
SET STATISTICS name;
Periodically, a B-tree data structure might become imbalanced, or it might
have some values in
the tree that have been deleted from the database (this should not happen in
InterBase 5.0 and
later, thanks to index garbage collection). You should periodically rebuild
an index:
ALTER INDEX name INACTIVE;
ALTER INDEX name ACTIVE;
Nic.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
