db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Date Thu, 15 Dec 2005 21:14:08 GMT
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> ;
>>>>  
>>>>        
>>>>
>>> 
>>>
>>>      
>>>
>>
>>    
>>
>
>  
>


Mime
View raw message