At 06:41 PM 18/02/2007, you wrote:

>Hi,
>I was just curious about one thing.
>First i made three tables and indexed at most two fields of each tables.
>Then i inserted almost 100000 records.
>the database size was about 1.86 GB.
>Then i deleted all the indexes but still the database size remained
>the same i.e. 1.86 GB.
>
>I was expecting decrease in database size.
>
>Is this behaviour obvious or i missed something..

Wrong expectations. A Firebird database isn't a folder full of
physical tables and physical files containing indexes. It's a chunk
of disk which the engine lays out as pages. It writes index
structures to index pages, data structures to data pages, etc.
etc. The stuff it writes to index pages isn't copies of table data,
it's structures that the engine uses at runtime to build binary index
trees in memory.

When you delete things, the space used by dead record versions and
dead index entries on those pages gets freed up (eventually) once
those old versions are no longer interesting. A garbage collection
process (GC) cleans up the artifacts when it is able to and the space
becomes available for new record versions and new index entries,
respectively. A database file never gets smaller (except after a
restore, of course).

>Also while querying the indexed table, the query performance is not
>that fast.
>
>I did it like this:-
>
>CREATE TABLE WHF(
>ID BIGINT NOT NULL,
>W1 VARCHAR(200),
>F1 BIGINT NOT NULL);
>CREATE INDEX WHFIDINDEX ON WORD_HASH_FILE (ID);
>CREATE INDEX WHFWFINDEX ON WORD_HASH_FILE (W1,F1);
>
>Then i inserted values...
>
>And i executed following query to fetch record from it..
>
>SELECT ID FROM WHF WHERE WORD IN('a','b',.....) ORDER BY F1 DESC;
>
>It took much time then expected.

Well, creating indexes on one table won't make performance faster on
a different table. Other confusion here: there is no column named
"WORD" in table WHF, anyway. Presumably you meant "W1".

And, even if you had created those indexes on table WHF instead of
table WORD_HASH_FILE, there would be nothing there that could be used
to do a descending (or ascending) sort on column F1. The engine
would be able to use the first segment of the second index, though I
don't see why F1 is in that index.

This query will be helped by the following changes to table WHF:

1) putting a PRIMARY KEY constraint on table WHF, presumably on column ID
(and NOT putting an additional ascending index on ID)
2) Putting a DESCending index on column F1
3) Putting an index on column W1

>I have done same thing in MS SQL SErver previously.

I don't believe you. I never knew a case in SQLServer where indexing
one table caused selects to go faster on a different, non-indexed table.

What you did in SQLServer to speed up searches is irrelevant in a
different engine, in any case. Forget it. Every DB engine has its
own strategy for optimising searches, including the use of indexes
and how certain index structures can be useful.

./heLen