Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!elroy.jpl.nasa.gov!decwrl!nsc!voder!blia!nobody From: nobody@blia.sharebase.com (Nobody at all) Newsgroups: comp.databases Subject: Re: Query Optimization Keywords: magic sets Message-ID: <13574@blia.sharebase.com> Date: 25 Feb 91 23:36:45 GMT References: <1991Feb14.192012.6084@doe.utoronto.ca> <13511@blia.sharebase.com> <11262@pasteur.Berkeley.EDU> Reply-To: mike@sharebase.com (Mike Ubell) Organization: ShareBase Lines: 45 In article <11262@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes: >In <13511@blia.sharebase.com>, mike@woodstock.UUCP (Mike Ubell) writes > >> ShareBase III (in beta test, scheduled for >> release around June) does a complete optimization search for up to >> about 10 way joins and a pruned search up to 32 way. As far as I know >> it will generate the same query plan for any semanticly equivalent SQL >> statements. > >does this optimizer consider nested subqueries to be semantically equivalent >to their flattened expressions? do you have to flatten subqueries in order >to make this happen? this seems kind of tricky. > > mike olson > postgres research group > uc berkeley > mao@postgres.berkeley.edu I like a good straight man, thanks Mike: The answer is that it does optimizations across subqueries. Remembering that subqueries form an existensial join with the outer query makes this somewhat tricky since it is not legal to generate multiple values where the original query would only generate one. An example using a query that was popular when I was at Berkeley: The following are not equivalent since the second query is required to generate one m.name per qualifying record in the cross product while the first generates one m.name per qualifying record in the single table. select m.name from emp m where m.eno = (select e.manager from emp e where e.sal > m.sal) select m.name from emp m, emp e where m.eno = e.manager and e.sal > m.sal; ShareBase III, however, can use an index on eno to resolve the first query if that is the best way to reslove it and will get the correct cardinality of the result. That is the reason that you might want to flatten the query. The first query is equivalent to a select distinct of the second query. Michael Ubell ShareBase Corp. mike@sharebase.com Brought to you by Super Global Mega Corp .com