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] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Date Thu, 15 Dec 2005 18:47:09 GMT
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