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