From: bob@dmrinc.com (Bob Hadden)
Newsgroups: comp.databases.informix
Subject: Re: Self-join
Date: 1 Oct 92 17:13:11 GMT
Reply-To: bob@dmrinc.com
X-Informix-List-ID: <list.1504>


    I am posting this again because...

    a. My first posting never made it out and nobody saw it.
    or
    b. The question was soooo stupid, that nobody would even consider respondin
    g.
    or
    c. The question was soooo hard that everybody was stumped.

    Can anyone offer an explanation on why this happens...

    Given the two tables with indexes shown below and the
    following self-join selects...

    This select runs real slow. I never let it finish because I
    got tired of waiting.

    select d1.md_lno, d1.md_data, d2.md_lno, d2.md_data
    from machdata d1, outer machdata d2
    where d1.md_fnm = "Q920723.123850"
    and d2.md_fnm = "Q920723.123850"
    and d1.md_lno < 51
    and d2.md_lno = d1.md_lno+50


    This one runs real fast. Instantaneous to be exact.

    select d1.md_lno, d1.md_data, d2.md_lno, d2.md_data, mc.mc_fnm
    from machctrl mc, machdata d1, outer machdata d2
    where mc.mc_fnm = "Q920723.123850"
    and d1.md_fnm = mc.mc_fnm
    and d2.md_fnm = mc.mc_fnm
    and d1.md_lno < 51
    and d2.md_lno = d1.md_lno+50


    For any given md_fnm: md_lno goes sequentially from 1 to n

    It certainly looks like in the first example the engine is
    building an index as a result of the outer join.  

    In the second example with the addition of another table
    is uses the existing index on machdata in the outer join.
    The field mc_fnm is supplying exactly the same value as I coded
    manually in the first select statement.

    It appears the query optimizer uses different rules depending on
    where/how it gets expressions in the where clause.

    Why?


    create table machctrl 
    (
    mc_fnm char(14) not null,
    mc_tnm char(10),
    mc_date char(8),
    mc_time char(8),
    mc_trayid integer,
    mc_activity date,
    mc_code char(1)
    );

    create unique index xmachctrl_1 on machctrl (mc_fnm);
    create index xmachctrl_2 on machctrl (mc_trayid);


    create table machdata 
    (
    md_fnm char(14) not null,
    md_lno smallint,
    md_data char(40)
    );

    create index xmachdata_1 on machdata (md_fnm);


    QUERY:
    ------
    select d1.md_lno, d1.md_data, d2.md_lno, d2.md_data
    from machdata d1, outer machdata d2
    where d1.md_fnm = "Q920723.123850"
    and d2.md_fnm = "Q920723.123850"
    and d1.md_lno < 51
    and d2.md_lno= d1.md_lno+50


    Estimated Cost: 13572
    Estimated # of Rows Returned: 4



    QUERY:
    ------
    select d1.md_lno,d1.md_data, d2.md_lno, d2.md_data, mc.mc_fnm
    from machctrl mc, machdata d1, outer machdata d2
    where mc.mc_fnm = "Q920723.123850"
    and d1.md_fnm = mc.mc_fnm
    and d2.md_fnm = mc.mc_fnm
    and d1.md_lno < 51
    and d2.md_lno = d1.md_lno+50


    Estimated Cost: 74
    Estimated # of Rows Returned: 11

    1) mc: INDEX PATH

    (1) Index Keys: mc_fnm 
    Lower Index Filter: mc.mc_fnm = 'Q920723.123850' 

___________

The answer to your question should be obvious when you look
at how informix links its index paths in the two Info Dumps:

First Example
    1) d1: INDEX PATH
    Filters: d1.md_lno < 51 
-->    (1) Index Keys: md_fnm 
    Lower Index Filter: d1.md_fnm = 'Q920723.123850' 

    2) d2: AUTOINDEX PATH
    Filters: d2.md_fnm = 'Q920723.123850' 
-->    (1) Index Keys: md_lno 
    Lower Index Filter: d2.md_lno = d1.md_lno + 50 

Second Example
    2) d1: INDEX PATH
    Filters: d1.md_lno < 51 
-->    (1) Index Keys: md_fnm 
   Lower Index Filter: d1.md_fnm = mc.mc_fnm 

    3) d2: INDEX PATH
    Filters: d2.md_lno = d1.md_lno + 50 
-->    (1) Index Keys: md_fnm 
    Lower Index Filter: d2.md_fnm = mc.mc_fnm 

In the first example, Informix is using "md_lno" as an index
key, which is a field without an index. It has no other way
to mutually exclude D2 from D1. So yes, Informix
will have to build the index in real time while also
performing the search. In the second example, Informix
treats both D1, and D2 as mutually exclusive because they
each can be linked to "mc_fnm".

If you had to write code for a DB engine to handle this type
of search, how would you do it?

I like puzzles like this!