On Thu, Feb 14, 2013 at 4:12 AM, Ruben Marti <label@...> wrote:

>
> create table PRICES {
> START_DATE date default current_date not null,
> ITEM varchar(10) default '' not null,
> PRICE double precision default 0 not null
> };
> alter table PRICES add constraint PK_PRICES primary key (START_DATE, ITEM);
>
> START_DATE and ITEM fields are not updatable. In the before update trigger
> the old values are copied to the new values.

I'm not at all sure I understand. Are you actually overwriting whatever
values were supplied in an update with the original values rather than
returning an error?

>
> The data are usually accessed ordering by START_DATE and ITEM, but now in
> a procedure I use:
>
> for select 1 PRICE from PRICES where ITEM = :ITEM and START_DATE <=
> :START_DATE into :PRICE do ...
>

I assume that you mean SELECT FIRST 1 ... If so this query would be faster
if your primary key were ITEM, START_DATE rather than the other way around,
or if you had a second index on ITEM alone. If you want to be sure you get
the oldest start date, you should include an ORDER BY START_DATE ASCENDING.
If you want the most recent start date, include ORDER BY START_DATE
DESCENDING. Without the ORDER BY, Firebird will return the record with the
lowest record number (aka RDB$DB_KEY) that meets the criteria, which may or
may not be what you want.

>
> Without going into the issue of "natural primary keys vs subrogate", to
> optimize this query I created the index:
>
> create unique descending index IDX_PRICE_ITEM on PRICES (ITEM, START_DATE);
>

That index will do you no good without an ORDER BY.

>
> Does using "unique" I'm adding some checking to do that insertions are
> slower?
>

Since the unique check is already done on the primary key index, I doubt
you'd be able to measure the performance difference on inserts.
Discovering that there is no duplicate is very cheap.

> In other databases I know there is a difference between unique and
> non-unique indexes, using lists to keep duplicate values. In these cases
> unique indexes are simpler and faster.
>

Firebird indexes all have the same structure, and are effectively all
unique because the record number (always unique) is considered part of the
key. The optimizer may consider an index that is declared as unique a
better selectivity than one that just appears to be unique.

Good luck,

Ann
IBPhoenix, commercial grade support for Firebird


[Non-text portions of this message have been removed]