At 03:38 PM 18-08-01 -0600, you wrote:

>Puzzled,
>
>I simplyfied a table update which took forever to the following
>
>Using IB_WISQL
>
>create table ta (a integer not null, b integer,
>primary key (a))
>
>create table tb (a integer not null, b integer,
>primary key (a))
>
>Now
>
>update ta set ta.b =
>(select tb.b from tb where tb.a = ta.a)
>
>generates
>
>PLAN (TB NATURAL)
>PLAN (TA NATURAL)
>
>Of course without any indexes this could take some time, but why ?

This update walks the table from beginning to end searching for matching rows and updating them one by one. Your subselect is asking it to match up the most recent ta.a with "some row from tb" where tb.a matches this value. IOW, because the optimizer is viewing the set in natural order, the index on ta.a is already defeated.

The exact explanation for it ignoring the index on tb.a is more obscure. Does it allow you to set an explicit plan that uses the RDB$PRIMARYnn on tb? Albeit, it won't be of much use to set an explicit plan on that index because it will be blown next time you restore...it just might help to see what the optimizer is thinking...

Experiment with the plan generated for a SP that capsizes the operation by finding the update value first, making it unnecessary for it to consider the index on tb at all, e.g.

create procedure Test_my_Update as
declare variable va integer;
declare variable vb integer;
begin
for select a, b from tb
into :va, :vb do
begin
update ta set b = :vb
where a = :va
end
end

Tip: Try posting SQL problems to ib-support and IBO questions to this list. Your postings seem to be a bit back-to-front today! :-)

Helen


All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________