db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [OPTIMIZER] Optimizer "timeout" for subqueries?
Date Fri, 03 Mar 2006 19:53:21 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
Army wrote:<br>
<blockquote cite="mid44089030.3040805@sbcglobal.net" type="cite"><br>
All of that said, my changes for DERBY-805 won't actually push the
predicate in the above example--DERBY-805 will only push predicates
into UNION nodes.&nbsp; But the argument is still the same...
Right... Some day extending your current optimizer changes to support
all table expressions would be a great enhancement to Derby optimizer.
I do believe Derby's optimizer can improve in the area of large query
optimization, especially with subqueries. Here are some of other
thoughts I have on where Derby can be improved. Let me know if this
seems incorrect. I have not done detailed analysis yet, but based on
some <b>vague </b>and <b>preliminary </b>findings.<br>
  <li>Extend join predicate push downs into all table expressions.
Continuation of your work.</li>
  <li>Extend all scalar single table expression pushdowns during
preprocessing into table expressions. (like T1.a+T1.b=5)&nbsp; Continuation
of my scalar predicate work.</li>
  <li>Enable hash-joins on select subqueries. <b>DERBY-781</b>.</li>
  <li>Consider materialization (like a VTI) as one possible execution
path for subqueries where hash-joins are not possible.</li>
  <li>Use invariant interface to determine if an expression could
qualify as predicate or search clause. (for cases like 'name =
upper(c)') <b>DERBY-824</b> and <b>DERBY-813</b>.<br>
  <li>Push some preprocessing work into bind phase, <b>where possible</b>.
Examples could be constant folding (a = upper('Sam'), re-arranging
predicates like <b>2&gt;a</b> to <b>a&gt;2</b>. This is causing
missing of correctly identifying predicates early enough to enable
  <li>Better join search algorithms could help large query optimization.</li>
  <li>Enhanced OR/IN processing. (<b>DERBY-47</b>)<br>
  <li>Multiple index scans for one table. Extension of above to cover
cases like <b>a=2 and b=4</b> with index on both <b>a </b>and <b>b</b>.<br>
  <li>I am sure there are others....</li>
I can file JIRA improvement entries if this list is correct.<br>
<blockquote cite="mid44089030.3040805@sbcglobal.net" type="cite">Does
that answer your question?
Completely... Thanks.<br>

View raw message