Hi Satheesh,
I have taken a quick look at your patch. It's a very useful improvement.
Your approach was very cautious. It might be useful to comment the code
with an explanation of why you decided to be so cautious.
It appears that your patch does the following for a UNION with a top
level predicate list:
o Copies each predicate to all the branches of the UNION while retaining
the predicate in the top level list. Why do you need to retain the top
level predicates once they have been cloned and pushed down?
o Performs this push-down only for predicates with a specific shape.
Shouldn't the whole predicate list be evaluatable on every branch of the
UNION? Why do you limit this patch to one specific shape of predicate?
Thanks,
-Rick
Satheesh Bandaram wrote:
Ouch... Rick... I have submitted my fix, based on Dan's comments. But I
will address any input you may have. I am still working on enhancing the
patch to cover more cases.
Satheesh
Rick Hillegas wrote:
Hi Satheesh,
I will take a look at this later on today. I'm down with a cold and so
only intermittently vertical.
Cheers,
-Rick
Satheesh Bandaram (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-649?page=all ]
Satheesh Bandaram updated DERBY-649:
------------------------------------
Attachment: DERBY-649.stat
DERBY-649.patch
First version of the patch. I am still testing and enhancing the
patch. Appreciate any comments.
Useful indexes not used in UNION ALL
------------------------------------
Key: DERBY-649
URL: http://issues.apache.org/jira/browse/DERBY-649
Project: Derby
Type: Bug
Reporter: Rick Hillegas
Attachments: DERBY-649.patch, DERBY-649.stat
Frederic Moreau reports
(http://mail-archives.apache.org/mod_mbox/db-derby-user/200510.mbox/browser):
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:\>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> connect 'testdb' ;
ij> ;
ij> create table test.table1(a integer, b integer, c integer);
ij> create index test.table1idx on test.table1(b);
ij> ;
ij> create table test.table2(a integer, b integer, c integer);
ij> create index test.table2idx on test.table2(b);
ij> ;
ij> create view test.view0 as select all a,b from test.table1 union
all select a,b from test.table2;
ij> ;
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
ij> select a from test.table1 where b=25;
ij> 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> select a from test.table2 where b=25;
ij> 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> select a from test.view0 where b=25;
ij> 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> ;
