From: kuhn@rhlab.UUCP (mjk Michael Kuhn)
Newsgroups: comp.databases.informix
Subject: Self-join
Keywords: SQL
Date: 30 Sep 92 15:55:07 GMT
Organization: Baltimore Rh Laboratory, Inc., Maryland

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 responding.
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

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 


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' 

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 

-- 
Michael J. Kuhn  Consultant  phone:410-254-7060
Email: rhlab!kuhn@uunet.uu.net or uunet!rhlab!kuhn
       c/o Baltimore Rh Typing Laboratory, Inc.  phone:410-225-9595