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