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 17:40:09 GMT
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