db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Date Sat, 01 Jul 2006 01:28:30 GMT
     [ http://issues.apache.org/jira/browse/DERBY-649?page=all ]

Kathey Marsden updated DERBY-649:
---------------------------------

    Component: SQL

> Useful indexes not used in UNION ALL
> ------------------------------------
>
>          Key: DERBY-649
>          URL: http://issues.apache.org/jira/browse/DERBY-649
>      Project: Derby
>         Type: Bug

>   Components: SQL
>     Reporter: Rick Hillegas
>      Fix For: 10.2.0.0, 10.1.3.0, 10.1.2.2
>  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> ;

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message