Hi Raimund, > > 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.
This is a floating point representation and it means 2 * (10 power 10). That's much more then 1024. > > 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 ? > The explain statement is ok. The SAP DB optimizer couldn't use the primary key on the tables bla or bar because the two from selects are executed before the join. They produce two temporary results without a primary key or index. In a further release we plan to implement a query rewrite optimization. Then the two statements should be handled equal. But this is a long way to go. > > 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... I'm very interested in this crashes, so please send your knldiag.err directly to me. Kind regards Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
