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