db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Lichtman (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Date Wed, 26 Oct 2005 18:17:04 GMT
    [ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12356000 ] 

Jeff Lichtman commented on DERBY-649:
-------------------------------------

The problem is probably that the qualification is not being pushed down into the union. In
the example script, the select from the view is expanded to:

select a from (select all a,b from test.table1 union all
select a,b from test.table2) tab where b = 25

After this expansion, the compilation phase should push the qualification down into the union:

select a from (select all a,b from test.table1 where b = 25 union all
select a,b from test.table2 where b = 25) tab

This should probably happen in the preprocessing phase.


> 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

>
> 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