--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 05:15 AM 10/11/2003 +0000, you wrote:
>
> >There is no primary key in this table. Indexes are:
> >1) history_idx = IPAddress, RowId, DateTime
> >2) history_idx2 = datetime
> >3) history_idx3 = rawsec, rawusec (plan to remove it in future).
> >
> >how do I check the segments for index ?
>
> The segments are the different parts (columns, keys) of a composite

key

> (like history_ix and history_idx3)
>
>
> >Here is the query plan:
> >for example this query that took about 10 sec to execute the below
> >statement:
> >select * from history where ipaddress = -1056765951 and rowid =
> >28844063 and datetime >= '2003-10-1 00:00:00' and datetime < '2003-

10-

> >2 00:00:00'
> >
> >PLAN (HISTORY INDEX (HISTORY_IDX,HISTORY_IDX2))
> >
> >Any way to improve it ?
>
> Start by setting these two indexes inactive:
>
> alter index history_idx inactive;
> alter index history_idx2 inactive;
>
> Then, just rebuild history_idx:
>
> alter index history_idx active;
>
> Leave history_idx2 inactive, because the optimizer shouldn't need

it. It

> is already using the index that has that column. It might make the

crucial

> difference in such a large table.
>
> Also go into the database and run SET STATISTICS on history_idx

before the

> next time you shut down the database. Next time anyone connects

after

> shutdown, the optimizer will refresh its view of the statistics.
>
> heLen

heLen, thanks for your advise. The select is faster now from 7 sec to
2 sec. Should I remove history_idx2 or leave it as inactive. If set
statistics on history_idx, will it cause the performance of insert
drop ?

rgds, chooi-ting