At 04:03 PM 23-02-01 +0100, you wrote:

>Helen Borrie wrote:
>
> > No. The unique constraint isn't an index, so it won't cause problems for the optimizer.
> >
> > >I still don't know if IB creates an index on unique, I think so, but I'm not
> > >sure.
> >
> > No, it doesn't...well, it kinda does...but it's an internal structure and it won't be used by the optimizer for joins.
> >
>
>Are You sure?
>I created the table with a unique constraint and if I look with IB_WISQL
>it shows me
>an unique index on ORDER_ID, ORDER_POS
>and an index on ORDER_ID

This could be an anomaly in IB_WISQL or a bug introduced by IB 6 or...Ann?

Try checking the output of this query:

select rdb$index_name, rdb$relation_name from rdb$indices
where rdb$relation_name = 'PARTS'

(I think this is similar to the query Jason uses to the get the data for the Indexes tab).

It shouldn't show the unique constraint (and didn't used to) so if IB 6 is now storing unique constraints in rdb$indices, IMO that's a bug that has potential to inhibit optimization.

Ann, can you comment?

>I'm using IB 6.0.0.627.
>When the next Firebird version comes out, I think I will use Firebird
>instead.

FB already has fixed a significant number of important bugs in the 0.9.4 betas. Not this one, though, since it hasn't been reported before...it will be interesting to see what your query throws up.

Regards,
Helen



All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________