You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Frederic MOREAU <fr...@ds-fr.com> on 2005/10/26 13:47:10 UTC
performance issue : indexes not taken into account on unions
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> ;
Re: performance issue : indexes not taken into account on unions
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Frederic,
This looks like a bug. I have logged bug 649 to track this issue. Thanks
for the detailed test case.
Regards,
-Rick
Frederic MOREAU wrote:
>
> 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> ;