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: [jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Date Wed, 21 Dec 2005 02:17:14 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanks for catching the regression and resolving it so quickly. Is
there anything you cann't do?<span class="moz-smiley-s1"><span> :-) </span></span><br>
<br>
Yes, I goofed in creating a new copy of the BinaryRelationalOperator.
Not sure why I put BINARY_EQUALS operator always, may be initial
cut_and_paste from some other part of the code.<br>
<br>
I thought the reason for having TRUE constant node was because of need
to have the predicate in CNF form... AndNode is always at the top,
which assists (or simplifies) logic in the optimizer. I am still not
done with changes in 10.1 branch. I will add a comment for the need to
have constant node. Other change I would like to make is how the patch
finds matching column reference. (findColumnReferenceInResult) Instead
of matching by column name, I think, it should be benificial to match
by columnPosition. This increases chances of finding a matching column.
For example:<br>
<br>
    Select * from (SELECT a, b from T1 union SELECT c, d from T2)
tab(col1, col2) where col1=5<br>
<br>
The predicate "col1=5" once gets pushed into ProjectRestrictNode,
becomes "SQLCol&lt;num&gt;=5" and trying to find a match in SELECT
statements would fail. If we match by columnNumber instead, it should
be possible to transform "col1=5" into "SQLCol&lt;num&gt;=5" to finally
"a=5" or "c=5". I am still investigating this... currently busy baby
sitting.<br>
<br>
Sorry for the trouble... <br>
<br>
Satheesh<br>
<br>
Daniel John Debrunner (JIRA) wrote:<br>
<blockquote
 cite="mid67994562.1135122631497.JavaMail.jira@ajax.apache.org"
 type="cite">
  <pre wrap="">    [ <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360997">http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360997</a>
] 

Daniel John Debrunner commented on DERBY-649:
---------------------------------------------

I think I've found the bug in the patch. I'd appreciate any optimizer experts looking at this.

In PredicateList.pushExpressionsIntoSelect when the predicate is copied, any type of binary
relational node can be copied, but the new relational node create is always an quality node,
it is not based upon the type being pushed.

I'm replacing this code, difference is first argument to getNode() :
around line 1438 - changes would also be made to te variable name, to correctly represent
its use.

				BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
							getNodeFactory().getNode(
										C_NodeTypes.BINARY_EQUALS_OPERATOR_NODE,
										newCRNode,
										opNode.getRightOperand(),
										getContextManager());

with

				BinaryRelationalOperatorNode newEquals = (BinaryRelationalOperatorNode)
							getNodeFactory().getNode(
									    opNode.getNodeType(),
										newCRNode,
										opNode.getRightOperand(),
										getContextManager());

I'd incorrectly assumed in the review that the (incorrect) new equality node was related to
the boolean constant TRUE being created. So sort of '(pushed expression) = TRUE' was being
pushed and required for some reason. I knew there was a good reason I'd asked for comments
on this code section:

  </pre>
  <blockquote type="cite">
    <pre wrap="">Dec 15th 08:19
For example, why do we need a new nodes that
represent '= TRUE', I'm sure it's required but to a reader of the code
    </pre>
  </blockquote>
  <pre wrap=""><!---->it's not obvious why.


  </pre>
  <blockquote type="cite">
    <pre wrap="">Useful indexes not used in UNION ALL
------------------------------------

         Key: DERBY-649
         URL: <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-649">http://issues.apache.org/jira/browse/DERBY-649</a>
     Project: Derby
        Type: Bug
    Reporter: Rick Hillegas
 Attachments: DERBY-649.patch, DERBY-649.stat

Frederic Moreau reports (<a class="moz-txt-link-freetext" href="http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser">http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser</a>):
Hello,
The optimizer does not take my indexes into account when I do a select on 
a 'UNION ALL' type of view ; therefore, table scans are done and 
performances are bad.
Note : my indexes are taken into account if I try equivalent selects on 
tables (instead of views).
Please find below a sample illustrating the problem using the 
RUNTIMESTATISTICS calls.
Could anynone help me on this subject ?
Thank you.
My cloudscape version is the 10.0.2.1 one.
I also tried it on the 10.1.1.0 version (same result).
  c:\&gt;java -classpath 
"%CLOUDSCAPE_INSTALL%\lib\derbyclient.jar;%CLOUDSCAPE_INSTALL%\lib\derbytools.jar" 
-Dij.driver=org.apache.derby.jdbc.ClientDriver 
-Dij.protocol=jdbc:derby://localhost:1527/ -Dij.user=APP -Dij.password=APP 
-Dij.maximumDisplayWidth=32768 org.apache.derby.tools.ij  ij&gt; connect 
'testdb' ;
  ij&gt; ;
  ij&gt; create table test.table1(a integer, b integer, c integer);
  ij&gt; create index test.table1idx on test.table1(b);
  ij&gt; ;
  ij&gt; create table test.table2(a integer, b integer, c integer);
  ij&gt; create index test.table2idx on test.table2(b);
  ij&gt; ;
  ij&gt; create view test.view0 as select all a,b from test.table1 union all 
select a,b from test.table2;
  ij&gt; ;
  ij&gt; CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
  ij&gt; select a from test.table1 where b=25;
  ij&gt; VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
        ...
        Index Scan ResultSet for TABLE1 using index TABLE1IDX at read 
committed isolation level using instantaneous share row locking chosen by 
the optimizer
        ...
  ij&gt; select a from test.table2 where b=25;
  ij&gt; VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
        ...
        Index Scan ResultSet for TABLE2 using index TABLE2IDX at read 
committed isolation level using instantaneous share row locking chosen by 
the optimizer
        ...
  ij&gt; select a from test.view0 where b=25;
  ij&gt; VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
        ...
        Table Scan ResultSet for TABLE1 at read committed isolation level 
using share row locking chosen by the optimizer
        ...
        Table Scan ResultSet for TABLE2 at read committed isolation level 
using share row locking chosen by the optimizer
        ...
  ij&gt; ;
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message