> A table with a long amount of records has two main fields
> N_Year as Smallint
> N_Doc as integer
>
> In the trigger before insert I load N_Doc as higher numer of same
> year, this way:
>
> select coalesce(max(N_Doc),0)+1
> from Docs
> where N_year=new.N_year
> into new.N_Doc;
>
> I need this operatios was quick, every time, (growing 500 rec/day)
>
> But I need also a quick search in sequental select for report
>
> select *
> from Docs
> where N_year=:N_Year
> order by N_Doc;
>
> I'm using index UNIQUE (N_Year, N_doc) for this table, but I think

A constraint or an index?

Indices can be ASCending or DESCending, constraints create indices
which are ASCending.

> this way is not much usefull for the insert trigger, but descending
> indes was not usefull for select... may be descending work better?
>
> Someone can help me? what can be TWO index optimized for both? or what
> only one can solve both problems...

Create an addition DESCending index and you should be home free.

> will be nice some REAL experience with same case, not only theoria.

Generate some test data and test it's behaviour yourself.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com