judlian23 wrote:

> Alexandre, thanks for the response.
>
> I am thinking we may need to quickly access random data, so a hash
> indexing scheme would be nice. The B*Tree also gives us the range
> functionality we need. Also we may index a few attributes together as
> a unit, so the compound indices sounds good.
>
> Is there a way to do hash indexing? Is there a way to specify the
> compound indexing?
>
> Julian
>
>

There is no hash index in FB, but I think you should first try FB to see
if it performs good enough for you.

FB uses a two-phase index scan, first it reads the index pages to mount
a bitmap of data pages that has potential records, and then read the
data pages on storage order (a kind of cluster index, but works for
*every* index and not for just one for table)

Compound indices are usual in FB you just need to specify all the
columns on the index creation, you should be aware that the maximum key
size is around 1/4 of the page size (in FB 2.0) the maximum page size is
16KB, so the maximum key size is around 4KB.

To create a compound index, you need something like this:
Create index MyCompoundIndex on MyTable(ColumnA, ColumnB, ColumnC)

but, you could do this too:
Create index MyIndexA on MyTable(ColumnA)
Create index MyIndexB on MyTable(ColumnB)
Create index MyIndexC on MyTable(ColumnC)

the 3 distinct indices could be used for queries like:

select * from MyTable where ColumnA = 1; (MyIndexA could be used)
select * from MyTable where ColumnB = 'foo'; (MyIndexB could be used)
select * from MyTable where ColumnC = 1234; (MyIndexC could be used)

and act like a single compound index on queries like
select * from MyTable where ColumnA = 1 and ColumnB = 'foo'; (MyIndexA
and MyIndexB could be used *togheter*)
select * from MyTable where ColumnA = 1 and ColumnC = 1234; (MyIndexA
and MyIndexC could be used *togheter*)
select * from MyTable where ColumnC = 1234 and ColumnB = 'foo';
(MyIndexC and MyIndexB could be used *togheter*)
select * from MyTable where ColumnA = 1 and ColumnB = 'foo' and ColumnC
= 1234; (MyIndexA and MyIndexB and MyIndexC could be used *togheter*)

if you have declared the Compound Index on ColumnA, ColumnB and ColumnC
(in this order) it could be used for the following queries
select * from MyTable where ColumnA = 1;
select * from MyTable where ColumnA = 1 and ColumnB = 'foo';
select * from MyTable where ColumnA = 1 and ColumnB = 'foo' and ColumnC
= 1234;

but could not be used for queries that does not filter on ColumnA like
select * from MyTable where ColumnB = 'foo';
select * from MyTable where ColumnC = 1234;
select * from MyTable where ColumnB = 'foo' and ColumnC = 1234;

I have very few compound indices on my databases, it's better than the
combination that FB does if you query a lot for the columns together,
but the distinct indices gives a very good performance too.

I suggest you to do some testing and post any doubts you have here,
there is some people around that knows pretty well how to improve query
performance.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br