From: kuhn@rhlab.UUCP (mjk Michael Kuhn)
Newsgroups: comp.databases.informix
Subject: SQL Query
Keywords: SQL, 4GL, Query
Date: 25 Sep 92 13:49:48 GMT
Organization: Baltimore Rh Laboratory, Inc., Maryland


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

Given the two tables with indexes shown below:


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