I have an update that I thought would be very quick but its not. The plan it
gets is NATURAL which I think is really sub-optimal but I'm not sure how to
make it use a proper index. I've munged it down to a simple example which
I'll include at the end of this email. The point to notice is that the plan
shows:

PLAN (FOO NATURAL)
PLAN (BAR NATURAL)

for the update command when I think it should be able to use an index on the
primary key of foo.

In my real tables the foo table has 151210 records in it and the bar only 456
and what I hoped Firebird would do is to go through the 456 bar records and
do one lookup on foo to get the number. Instead I think its doing a complete
scan of the foo table 456 times which is not good!

This is using the original 1.0 release of Firebird on Linux. Any ideas would
be greatly appreciated since I'm at a customer site trying to run this and
its taking *forever* (my real script has a later update involving a bar table
with almost a million records so I think its doing a million scans of the
complete 151210 records in foo!!).

I'm running this example script on any old Firebird database using isql like
this:

isql -i foo.sql foo.fdb

Here is the example script:

--------------------------------------------------------------------
set echo on;
set plan on;

create table foo (
plu_id numeric(18,0) not null,
number varchar(18),
primary key (plu_id)
);
commit;

create table bar (
price_id numeric(18,0) not null,
plu_id numeric(18,0) not null,
number varchar(18),
primary key (price_id),
foreign key (plu_id) references foo (plu_id)
);
commit;

insert into foo (plu_id,number) values (1,'1234');
insert into foo (plu_id,number) values (2,'abcd');
insert into foo (plu_id,number) values (3,'5678');
insert into foo (plu_id,number) values (4,'efgh');
commit;

insert into bar (price_id,plu_id,number) values (1,1,'a');
insert into bar (price_id,plu_id,number) values (2,2,'a');
insert into bar (price_id,plu_id,number) values (3,3,'a');
insert into bar (price_id,plu_id,number) values (4,4,'a');
commit;

update bar set number = (select foo.number from foo where foo.plu_id =
bar.plu_id);
commit;
----------------------------------------------------------

--
Brad Pepers
brad@...