Derby
  1. Derby
  2. DERBY-649

Useful indexes not used in UNION ALL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.1.3.1, 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

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

      1. DERBY-649.patch
        31 kB
        Satheesh Bandaram
      2. DERBY-649.stat
        0.5 kB
        Satheesh Bandaram

        Activity

        Gavin made changes -
        Workflow jira [ 12343138 ] Default workflow, editable Closed status [ 12797559 ]
        Kathey Marsden made changes -
        Component/s SQL [ 11408 ]
        Andrew McIntyre made changes -
        Resolution Fixed [ 1 ]
        Status Reopened [ 4 ] Closed [ 6 ]
        Andrew McIntyre made changes -
        Resolution Fixed [ 1 ]
        Status Closed [ 6 ] Reopened [ 4 ]
        Satheesh Bandaram made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Satheesh Bandaram made changes -
        Fix Version/s 10.1.3.0 [ 12310616 ]
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 10.2.0.0 [ 11187 ]
        Resolution Fixed [ 1 ]
        Fix Version/s 10.1.2.2 [ 12310631 ]
        Satheesh Bandaram made changes -
        Field Original Value New Value
        Attachment DERBY-649.patch [ 12321341 ]
        Attachment DERBY-649.stat [ 12321340 ]
        Rick Hillegas created issue -

          People

          • Assignee:
            Unassigned
            Reporter:
            Rick Hillegas
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development