Re: Self-join
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!