Sindu,
At 17:14 21.02.2001 -0800, you wrote:

>Hi Jason, but if the index is not used, wouldn't it
>slow down the query? I only take less than 10% of the
>total rows in a table so I think index is needed in my
>case.

if you have a query like
SELECT APRIMARYKEY, AFOREIGNKEY
FROM TABLEA
WHERE APRIMARYKEY BETWEEN 'AVALUE' AND 'SOMEOTHERVALUE'
AND AFOREIGNKEY BETWEEN 'AVALUE' AND 'SOMEOTHERVALUE'
you risk ending up with a plan like (TABLE1(RDB$PRIMARY1, RDB$FOREIGN1))
even though (TABLE1(RDB$PRIMARY1) would clearly be enough (well, I don't
know if the optimiser is stupid enough for such a simple case, but in more
complex cases it does things like this). In such circumstances, using
RDB$FOREIGN1 would slow down the query. Jason prefers to add '' in the
WHERE clause, I normally write
SELECT APRIMARYKEY, AFOREIGNKEY
FROM TABLEA
WHERE APRIMARYKEY BETWEEN 'AVALUE' AND 'SOMEOTHERVALUE'
AND (AFOREIGNKEY BETWEEN 'AVALUE' AND 'SOMEOTHERVALUE' or 0=1)

Use whichever you prefer, all we do is try making the optimiser choose the
same plan as you've already established to be faster without explicitly
defining the plan.

HTH,
Set