Hello Helen,

I use IB_WISQL to generate some of these test to substantiate
my questions. Since the generating source of the PLAN's seem
to be either IBO or IB6, where do I correctly post my questions.

Now I'm really confused ........

Honestly, I've become a master in work arounds if for some
reason the apparently simple methods don't work, but I still
hope to learn why the simple methods fail :)

Best Regards,
Hans

=============================

Helen Borrie wrote:

>
> 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
> _______________________________________________________
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/