db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-649) Useful indexes not used in UNION ALL
Date Tue, 20 Dec 2005 21:17:31 GMT
    [ http://issues.apache.org/jira/browse/DERBY-649?page=comments#action_12360982 ] 

Daniel John Debrunner commented on DERBY-649:
---------------------------------------------

-1 on the patch, causes a regression. Will also vote -1 on the subsequent patch for 772 as
it is dependent on this patch.

Run this simple script, at svn revision 357054 the output is expected including the
two argument IN clause on the select from the view returning two rows.
Once this patch is applied, svn revision 357105, that select returns no rows.

DROP VIEW V1;
DROP TABLE D1;
CREATE TABLE D1 (A INT, B VARCHAR(4) FOR BIT DATA);

INSERT INTO D1 VALUES (1, x'600Eaaef') ; 
INSERT INTO D1 VALUES (2, x'83452213') ; 

select * from D1 where B IN (x'600Eaaef',x'83452213') ;  
select * from D1 where B IN (x'83452213') ; 
select * from D1 where B  IN (x'600Eaaef') ; 

CREATE VIEW V1 AS SELECT A,B FROM D1 UNION SELECT A,B FROM D1;

SELECT * FROM V1;

-- this fails! returns no rows
select * from V1 where B IN (x'600Eaaef',x'83452213') ;  
select * from V1 where B IN (x'83452213') ; 
select * from V1 where B  IN (x'600Eaaef') ; 

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

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