hello *,
i have some difficulties understanding the the optimizer decisions. my
application dynamically generates sql queries. oh, and just for the
record:
- linux
- kernel 7.3.0.23
- jdbc "7.4.3 Build 001-000-078-671"
- dbvisualizer 3-beta2
here is a little testbed to reproduce the situation:
---8<---
create table foo (oid int, foo int, constraint pk primary key (oid))
create index foo on foo (foo)
insert into foo values(23, 100)
create table bar (oid int, baz int, constraint pk primary key (oid))
create index bar on bar (baz)
insert into bar values (23, 1)
create table bla (oid int, blu int, constraint pk primary key (oid))
create index bla on bla (blu)
insert into bla values (23, -1)
--->8---
Statement A:
select foo.* from foo, bar, bla where
(foo.oid = bar.oid and foo.oid = bla.oid) and bar.baz=1 and bla.blu=-1
Statement B:
select foo.* from foo,
(select * from bar where baz=1) a,
(select * from bla where blu=-1) b
where (foo.oid = a.oid and foo.oid = b.oid)
both statements create the same result set. yes, i know that the
conditions are redundant but this is just for showing what i mean.
the Statement A is quite fast. uses indices and key-column joins. the
cost value is 4. Statement B, however, is more expensive (>1600) and
has two table scans each with a cost value of 500. this gets even
worse in a more realistic version of this situation (more tables, more
conditions) where the A-like statement has a cost value of some 300
and the B-like statement hits values "> 2 E10" [*].
[*] i am not sure if i read this number correctly. 2 to the power of 10
is only 1024 but i've seen value like "123456" during my tests. i assume
it represents some even higher number.
it seems in Statment B the "oid"-join is not done using the primary
key...
questions: does the "explain" statement not tell the truth ?
is the optimizer not optimizing correctly ? what am i missing ?
any hint is greatly appreciated.
Raimund
ps: when i play around a lot with those EXPLAINS (the ones above and
my own ones), i get the kernel to segfault. unfortunately not really
reproducable (but often). i can provide the knldiag.err entries from my
last crash...
--
Raimund Jacob
\|/ ____ \|/
Pinuts Media+Science GmbH "@'/ ,. \`@"
Potsdamer Str. 96 /_| \__/ |_\
D-10785 Berlin \__U_/
http://www.pinuts.de/ voice: +49 30 590090313 fax: +49 30 590090390
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
