OK, having read Ann's post, it has just dawned on me what you are
trying to do, and I totally agree with Ann. Use a generator to get
the next available N_Doc.

Adam.

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- 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
>