--- In firebird-support@yahoogroups.com, "skander_sp" <skander_sp@...>
wrote:

>
> > > In the case I exposed, what is the recomanded Indexes or

Constraints?

> >
> > Well, given that there is a logical difference between the two,
> > choose what fits your requirements.
>
> sorry, may be i explain myself, I ask for the definition of indexes or
> constraints to resolve the TWO optimized acces I tell in the first
> message...

A constraint is generally better because it flags to the developers
that you mean business. If you just create a unique index, then
developers may in the future relax the constraint thinking it is not
too necessary. But if there is a unique constraint declared, they may
think twice.

A Unique constraint is internally equivalent to an ascending index.

>
> select coalesce(max(N_Doc),0)+1
> from Docs
> where N_year=new.N_year
> into new.N_Doc;
>

The above query is not helped by a unique constraint or by an
ascending index. It needs a descending index on (N_Year, N_Doc) to
perform.

> and
>
> select *
> from Docs
> where N_year=:N_Year
> order by N_Doc;
>

The above query wont be helped by the descending index above, it will
want a simple ascending index on (N_Year, N_Doc).

I do not know which fields are unique, but if N_Year, N_Doc must be
unique, then I would recommend creating a unique constraint on
(N_Year, N_Doc) which takes care of the index requirement for the
second query and also a descending index as described for the first query.

Then generate some test data (or better still live data from a big
customer) and test it using your preferred tool. I use IBPlanalyzer to
check the plan is ok.

Adam