Derby
  1. Derby
  2. DERBY-6045

in list multi-probe by primary key not chosen on tables with >256 rows

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.9.1.0, 10.10.1.1
    • Fix Version/s: 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Linux Debian 6.0.5
    • Issue & fix info:
      High Value Fix, Workaround attached
    • Bug behavior facts:
      Performance, Regression, Seen in production

      Description

      I have a table with a long integer primary key field and 11 million rows. I seem to be unable to load large chunks of rows via id in a reasonably efficient manner.

      1. If I do individual lookups via the primary key, then a fast indexed lookup occurs. However, if I do large numbers of such queries, then the time is overwhelmed by round-trip overhead which makes everything incredibly slow.

      2. If I use a single query with a disjunction of the primary keys of interest, then a table scan is performed (even if the clause only contains 1-3 items), which walks over 11 million rows...incredibly inefficient.

      3. If I use an IN clause, then a table scan is performed (even if the clause only contains 1-3 items), which walks over 11 million rows...incredibly inefficient.

      I'm guessing that this might have something to do with the fact that I'm using large integers and really big numbers that don't start anywhere at or about 1 for my keys. Could this possibly be confusing the optimizer?

      Here are the unlimited query plans for the 3 cases that I enumerated:
      *********************************************************************************************

      [EL Fine]: 2013-01-17 11:09:53.384-ServerSession(582235416)Connection(1430986883)Thread(Thread["Initial Lisp Listener",5,SubL Thread Group])-SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?)
      bind => [2251799814033500]

      Thu Jan 17 11:09:53 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711079), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?) ******* Project-Restrict ResultSet (3):
      Number of opens = 1
      Rows seen = 1
      Rows filtered = 0
      restriction = false
      projection = true
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      optimizer estimated row count: 1.00
      optimizer estimated cost: 6.59
      Source result set:
      Index Row to Base Row ResultSet for FORMULA_TERM:
      Number of opens = 1
      Rows seen = 1
      Columns accessed from heap =

      {1, 2, 3, 4, 5, 6, 7, 8}

      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      optimizer estimated row count: 1.00
      optimizer estimated cost: 6.59
      Index Scan ResultSet for FORMULA_TERM using constraint KB_FORMULA_TERM_TERM_ID_PK at read committed isolation level using share row locking chosen by the optimizer
      Number of opens = 1
      Rows seen = 1
      Rows filtered = 0
      Fetch Size = 1
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      next time in milliseconds/row = 0

      scan information:
      Bit set of columns fetched=All
      Number of columns fetched=2
      Number of deleted rows visited=0
      Number of pages visited=3
      Number of rows qualified=1
      Number of rows visited=1
      Scan type=btree
      Tree height=-1
      start position:
      >= on first 1 column(s).
      Ordered null semantics on the following columns:
      stop position:
      > on first 1 column(s).
      Ordered null semantics on the following columns:
      qualifiers:
      None
      optimizer estimated row count: 1.00
      optimizer estimated cost: 6.59

      [EL Fine]: 2013-01-17 11:01:00.732-ServerSession(1237006689)Connection(927179828)Thread(Thread["Initial Lisp Listener",5,SubL Thread Group])-SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID = ?)) OR (TERM_ID = ?))
      bind => [2251799814033500, 2251799814033501, 2251799814033499]

      Thu Jan 17 11:01:10 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711078), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID = ?)) OR (TERM_ID = ?)) ******* Project-Restrict ResultSet (3):
      Number of opens = 1
      Rows seen = 3
      Rows filtered = 0
      restriction = false
      projection = true
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54
      Source result set:
      Project-Restrict ResultSet (2):
      Number of opens = 1
      Rows seen = 11767298
      Rows filtered = 11767295
      restriction = true
      projection = false
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54
      Source result set:
      Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous share row locking chosen by the optimizer
      Number of opens = 1
      Rows seen = 11767298
      Rows filtered = 0
      Fetch Size = 16
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      next time in milliseconds/row = 0

      scan information:
      Bit set of columns fetched=All
      Number of columns fetched=9
      Number of pages visited=34358
      Number of rows qualified=11767298
      Number of rows visited=11767298
      Scan type=heap
      start position:
      null
      stop position:
      null
      qualifiers:
      None
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54

      [EL Fine]: 2013-01-17 11:27:00.627-ServerSession(1237006689)Connection(1688096771)Thread(Thread["Initial Lisp Listener",5,SubL Thread Group])-SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?))
      bind => [2251799814033500, 2251799814033501, 2251799814033499]

      Thu Jan 17 11:47:26 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711080), (SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?)) ******* Project-Restrict ResultSet (3):
      Number of opens = 1
      Rows seen = 3
      Rows filtered = 0
      restriction = false
      projection = true
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54
      Source result set:
      Project-Restrict ResultSet (2):
      Number of opens = 1
      Rows seen = 11767298
      Rows filtered = 11767295
      restriction = true
      projection = false
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54
      Source result set:
      Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous share row locking chosen by the optimizer
      Number of opens = 1
      Rows seen = 11767298
      Rows filtered = 0
      Fetch Size = 16
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      next time in milliseconds/row = 0

      scan information:
      Bit set of columns fetched=All
      Number of columns fetched=9
      Number of pages visited=34358
      Number of rows qualified=11767298
      Number of rows visited=11767298
      Scan type=heap
      start position:
      null
      stop position:
      null
      qualifiers:
      None
      optimizer estimated row count: 1176730.30
      optimizer estimated cost: 5931065.54

      1. optimizerTraceOutputWithUpdateStatsForTrunk.txt
        6 kB
        Mamta A. Satoor
      2. optimizerTraceOutputWithUpdateStatsFor10_8.txt
        6 kB
        Mamta A. Satoor
      3. optimizerTraceOutputForTrunk.txt
        6 kB
        Mamta A. Satoor
      4. optimizerTraceOutputFor10_8.txt
        6 kB
        Mamta A. Satoor
      5. DERBY6045_patch2_diff.txt
        4 kB
        Mamta A. Satoor
      6. DERBY6045_patch1_diff.txt
        3 kB
        Mamta A. Satoor

        Issue Links

          Activity

          Hide
          Myrna van Lunteren added a comment -

          bulk change to close all issues resolved but not closed and not changed since June 1, 2014.

          Show
          Myrna van Lunteren added a comment - bulk change to close all issues resolved but not closed and not changed since June 1, 2014.
          Hide
          Mamta A. Satoor added a comment - - edited

          I will go ahead and close this jira since we are following issue with OR on multiple columns as part of DERBY-6226

          Show
          Mamta A. Satoor added a comment - - edited I will go ahead and close this jira since we are following issue with OR on multiple columns as part of DERBY-6226
          Hide
          Mike Matrigali added a comment -

          I have created DERBY-6226 to track the discussion on the "delete/select" with OR's on multiple columns. Tony I think you can delete your private data as we actually did repro the problem, just did not know it.

          Show
          Mike Matrigali added a comment - I have created DERBY-6226 to track the discussion on the "delete/select" with OR's on multiple columns. Tony I think you can delete your private data as we actually did repro the problem, just did not know it.
          Hide
          Tony Brusseau added a comment -

          I tried the SELECT version of the DELETE statement:

          SELECT * FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049)

          It looks like it is doing a table scan as well.

          Number of pages visited=67
          Number of rows qualified=418
          Number of rows visited=29498

          Note: this was tried without the Mamta's patches for this bug so far. However, I am using table statics which would avoid this bug, so I'm pretty sure this is still a real issue.

          Here is the query plan:

          Thu May 23 15:17:22 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 80859), (SESSIONID = 1), SELECT COUNT FROM (SELECT * FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049)) C2668 ******* Project-Restrict ResultSet (4):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 1.00
          optimizer estimated cost: 12365.71
          Source result set:
          Scalar Aggregate ResultSet:
          Number of opens = 1
          Rows input = 418
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 14751.50
          optimizer estimated cost: 12365.71
          Index Key Optimization = false
          Source result set:
          Project-Restrict ResultSet (3):
          Number of opens = 1
          Rows seen = 418
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 14751.50
          optimizer estimated cost: 12365.71
          Source result set:
          Table Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 418
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=6
          Number of pages visited=67
          Number of rows qualified=418
          Number of rows visited=29498
          Scan type=heap
          start position:
          null
          stop position:
          null
          qualifiers:
          Column[1][0] Id: 3
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          Column[1][1] Id: 2
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          Column[1][2] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 14751.50
          optimizer estimated cost: 12365.71

          Show
          Tony Brusseau added a comment - I tried the SELECT version of the DELETE statement: SELECT * FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049) It looks like it is doing a table scan as well. Number of pages visited=67 Number of rows qualified=418 Number of rows visited=29498 Note: this was tried without the Mamta's patches for this bug so far. However, I am using table statics which would avoid this bug, so I'm pretty sure this is still a real issue. Here is the query plan: Thu May 23 15:17:22 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 80859), (SESSIONID = 1), SELECT COUNT FROM (SELECT * FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049)) C2668 ******* Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 1.00 optimizer estimated cost: 12365.71 Source result set: Scalar Aggregate ResultSet: Number of opens = 1 Rows input = 418 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 14751.50 optimizer estimated cost: 12365.71 Index Key Optimization = false Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 418 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 14751.50 optimizer estimated cost: 12365.71 Source result set: Table Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 418 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=6 Number of pages visited=67 Number of rows qualified=418 Number of rows visited=29498 Scan type=heap start position: null stop position: null qualifiers: Column [1] [0] Id: 3 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Column [1] [1] Id: 2 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Column [1] [2] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 14751.50 optimizer estimated cost: 12365.71
          Hide
          Tony Brusseau added a comment - - edited

          Is there a way I can get quick access to fixed jar files (client and embedded)? I'm not currently set up to do Derby development.

          Also, once you enable statistics with SYSCS_UTIL.SYSCS_UPDATE_STATISTICS, is there a way to turn it off again? Since this bypasses the issue, I'll need to undo this change so I can test accurately.

          Show
          Tony Brusseau added a comment - - edited Is there a way I can get quick access to fixed jar files (client and embedded)? I'm not currently set up to do Derby development. Also, once you enable statistics with SYSCS_UTIL.SYSCS_UPDATE_STATISTICS, is there a way to turn it off again? Since this bypasses the issue, I'll need to undo this change so I can test accurately.
          Hide
          Mike Matrigali added a comment - - edited

          just looked more carefully at the problem delete query, an optimal execution would do single probes in 3 different indexes. I am not sure if the in list optimization is coded to do that. This may be a feature request vs a bug. Could someone who knows the optimizer comment? It would be good to know if a select that looks like the delete query has a problem or not.

          Show
          Mike Matrigali added a comment - - edited just looked more carefully at the problem delete query, an optimal execution would do single probes in 3 different indexes. I am not sure if the in list optimization is coded to do that. This may be a feature request vs a bug. Could someone who knows the optimizer comment? It would be good to know if a select that looks like the delete query has a problem or not.
          Hide
          Mike Matrigali added a comment -

          i think we should move the delete bug to a new linked JIRA. Tony, can you verify that
          the current submitted fix, fixes your original reported select issue.

          Show
          Mike Matrigali added a comment - i think we should move the delete bug to a new linked JIRA. Tony, can you verify that the current submitted fix, fixes your original reported select issue.
          Hide
          Tony Brusseau added a comment - - edited

          I've attached a dump of the table that should reproduce the issue.
          The table was dumped with:
          CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE('KB', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', 'table.del', null, null, 'UTF-8');

          The current table definition looks like:

          CREATE TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS
          (
          id BIGINT NOT NULL,
          mt BIGINT NOT NULL,
          pred BIGINT NOT NULL,
          term BIGINT NOT NULL,
          term_index INTEGER NOT NULL,
          usage_count BIGINT NOT NULL
          );

          ALTER TABLE kb.mt_gaf_top_level_term_counts
          ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (id);
          ALTER TABLE kb.mt_gaf_top_level_term_counts
          ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_non_negative_usage_count_check CHECK (usage_count >= 0);

          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON kb.mt_gaf_top_level_term_counts(mt);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON kb.mt_gaf_top_level_term_counts(term);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_pred_index ON kb.mt_gaf_top_level_term_counts(pred);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term_index); – fast find of terms
          CREATE INDEX kb_mt_gaf_top_level_term_counts_pred_term_term_index_index ON kb.mt_gaf_top_level_term_counts(pred, term, term_index); – fast find of mts
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_term_term_index_index ON kb.mt_gaf_top_level_term_counts(mt, term, term_index); – fast find of preds
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term); – fast find of term indices
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index_term_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term_index, term); – fast find of ids

          The following delete currently produces a full table scan:

          DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049)

          However, breaking it up into 3 separate deletes does not produce a table scan.

          Note: I am maintaining statistics on the table if that matters:

          call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('KB', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', null);

          Show
          Tony Brusseau added a comment - - edited I've attached a dump of the table that should reproduce the issue. The table was dumped with: CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE('KB', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', 'table.del', null, null, 'UTF-8'); The current table definition looks like: CREATE TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS ( id BIGINT NOT NULL, mt BIGINT NOT NULL, pred BIGINT NOT NULL, term BIGINT NOT NULL, term_index INTEGER NOT NULL, usage_count BIGINT NOT NULL ); ALTER TABLE kb.mt_gaf_top_level_term_counts ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (id); ALTER TABLE kb.mt_gaf_top_level_term_counts ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_non_negative_usage_count_check CHECK (usage_count >= 0); CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON kb.mt_gaf_top_level_term_counts(mt); CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON kb.mt_gaf_top_level_term_counts(term); CREATE INDEX kb_mt_gaf_top_level_term_counts_pred_index ON kb.mt_gaf_top_level_term_counts(pred); CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term_index); – fast find of terms CREATE INDEX kb_mt_gaf_top_level_term_counts_pred_term_term_index_index ON kb.mt_gaf_top_level_term_counts(pred, term, term_index); – fast find of mts CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_term_term_index_index ON kb.mt_gaf_top_level_term_counts(mt, term, term_index); – fast find of preds CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term); – fast find of term indices CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_pred_term_index_term_index ON kb.mt_gaf_top_level_term_counts(mt, pred, term_index, term); – fast find of ids The following delete currently produces a full table scan: DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049) However, breaking it up into 3 separate deletes does not produce a table scan. Note: I am maintaining statistics on the table if that matters: call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('KB', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', null);
          Hide
          Tony Brusseau added a comment - - edited

          Ive attached table.del which is a table dump that should reproduce the disjunctive delete table scan. This is proprietary and should be deleted when testing is complete.

          Show
          Tony Brusseau added a comment - - edited Ive attached table.del which is a table dump that should reproduce the disjunctive delete table scan. This is proprietary and should be deleted when testing is complete.
          Hide
          Tony Brusseau added a comment - - edited

          Here is the query plan for the disjunctive delete, notice the full table scan on 29K rows even though term, pred and mt all have individual indexes (which I've confirmed):

          Tue May 21 11:28:34 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88632), (SESSIONID = 1), DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049) ******* Delete Cascade ResultSet using row locking:
          deferred: false
          Rows deleted = 418
          Indexes updated = 9
          Execute Time = 0
          Project-Restrict ResultSet (1):
          Number of opens = 1
          Rows seen = 418
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 14751.50
          optimizer estimated cost: 12365.71
          Source result set:
          Table Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS at read committed isolation level using exclusive row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 418
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=

          {0, 1, 2, 3, 4}

          Number of columns fetched=5
          Number of pages visited=67
          Number of rows qualified=418
          Number of rows visited=29498
          Scan type=heap
          start position:
          null
          stop position:
          null
          qualifiers:
          Column[1][0] Id: 3
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          Column[1][1] Id: 2
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          Column[1][2] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 14751.50
          optimizer estimated cost: 12365.71
          Referential Actions On Dependent Tables:
          Delete Cascade ResultSet using row locking:
          deferred: false
          Rows deleted = 860
          Indexes updated = 3
          Execute Time = 0
          Project-Restrict ResultSet (3):
          Number of opens = 1
          Rows seen = 860
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 129341.00
          optimizer estimated cost: 38529.20
          Source result set:
          Index Scan ResultSet for GAF_ARG using index On Foreign Key at serializable isolation level using exclusive table locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 860
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=837
          Number of rows qualified=860
          Number of rows visited=1278
          Scan type=btree
          Tree height=2
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 129341.00
          optimizer estimated cost: 38529.20
          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88634), (SESSIONID = 1), CALL SYSIBM.SQLTABLES(?,?,?,?,?) ******* null
          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88634), (SESSIONID = 1), EXECUTE STATEMENT SYS."getTables" ******* Sort ResultSet:
          Number of opens = 1
          Rows input = 0
          Rows returned = 0
          Eliminate duplicates = false
          In sorted order = false
          Sort information:
          Number of rows input=0
          Number of rows output=0
          Sort type=internal
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.00
          optimizer estimated cost: 6.08
          Source result set:
          Project-Restrict ResultSet (19):
          Number of opens = 1
          Rows seen = 0
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.00
          optimizer estimated cost: 6.08
          Source result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 48
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.00
          optimizer estimated cost: 6.08
          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 12
          Rows seen from the right = 48
          Rows filtered = 0
          Rows returned = 48
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.70
          optimizer estimated cost: 4.99
          Left result set:
          Project-Restrict ResultSet (5):
          Number of opens = 1
          Rows seen = 12
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          Source result set:
          Index Row to Base Row ResultSet for SYSSCHEMAS:
          Number of opens = 1
          Rows seen = 12
          Columns accessed from heap =

          {0, 1}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 12
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=12
          Number of rows visited=12
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99

          Right result set:
          Union ResultSet:
          Number of opens = 12
          Rows seen from the left = 36
          Rows seen from the right = 12
          Rows returned = 48
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.70
          optimizer estimated cost: 0.00
          Left result set:
          Union ResultSet:
          Number of opens = 12
          Rows seen from the left = 24
          Rows seen from the right = 12
          Rows returned = 36
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.28
          optimizer estimated cost: 0.00
          Left result set:
          Union ResultSet:
          Number of opens = 12
          Rows seen from the left = 12
          Rows seen from the right = 12
          Rows returned = 24
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.85
          optimizer estimated cost: 0.00
          Left result set:
          Normalize ResultSet:
          Number of opens = 12
          Rows seen = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.85
          optimizer estimated cost: 0.00
          Source result set:
          Row ResultSet:
          Number of opens = 12
          Rows returned = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 0.00


          Right result set:
          Row ResultSet:
          Number of opens = 12
          Rows returned = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 0.00


          Right result set:
          Normalize ResultSet:
          Number of opens = 12
          Rows seen = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.28
          optimizer estimated cost: 0.00
          Source result set:
          Row ResultSet:
          Number of opens = 12
          Rows returned = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 0.00



          Right result set:
          Normalize ResultSet:
          Number of opens = 12
          Rows seen = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 1.70
          optimizer estimated cost: 0.00
          Source result set:
          Row ResultSet:
          Number of opens = 12
          Rows returned = 12
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 0.00




          Right result set:
          Project-Restrict ResultSet (18):
          Number of opens = 48
          Rows seen = 0
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.00
          optimizer estimated cost: 1.08
          Source result set:
          Index Row to Base Row ResultSet for SYSTABLES:
          Number of opens = 48
          Rows seen = 0
          Columns accessed from heap = {1, 2, 3}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.00
          optimizer estimated cost: 1.08
          Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 48
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0
          Number of pages visited=48
          Number of rows qualified=0
          Number of rows visited=48
          Scan type=btree
          Tree height=1
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          1
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          Column[0][0] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 0.00
          optimizer estimated cost: 1.08



          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88635), (SESSIONID = 1), CALL SYSIBM.SQLPRIMARYKEYS(?,?,?,?) ******* null
          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88635), (SESSIONID = 1), EXECUTE STATEMENT SYS."getPrimaryKeys" ******* Sort ResultSet:
          Number of opens = 1
          Rows input = 1
          Rows returned = 1
          Eliminate duplicates = false
          In sorted order = false
          Sort information:
          Number of rows input=1
          Number of rows output=1
          Sort type=internal
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 47.58
          optimizer estimated cost: 688.05
          Source result set:
          Project-Restrict ResultSet (23):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 47.58
          optimizer estimated cost: 688.05
          Source result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 47.58
          optimizer estimated cost: 688.05
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.82
          optimizer estimated cost: 290.29
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.34
          optimizer estimated cost: 121.22
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.34
          optimizer estimated cost: 112.46
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.30
          optimizer estimated cost: 35.38
          Left result set:
          Project-Restrict ResultSet (8):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 2.70
          optimizer estimated cost: 15.68
          Source result set:
          Index Row to Base Row ResultSet for SYSTABLES:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap = {0, 1, 3}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.70
          optimizer estimated cost: 15.68
          User supplied optimizer overrides on SYSTABLES are { index=SYSTABLES_INDEX1 }
          Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched={0, 2}
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=2
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 2.70
          optimizer estimated cost: 15.68

          Right result set:
          Project-Restrict ResultSet (11):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 2.30
          optimizer estimated cost: 19.70
          Source result set:
          Index Row to Base Row ResultSet for SYSSCHEMAS:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap = {0, 1}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.30
          optimizer estimated cost: 19.70
          User supplied optimizer overrides on SYSSCHEMAS are

          { index=SYSSCHEMAS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=2
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 2.30
          optimizer estimated cost: 19.70


          Right result set:
          Project-Restrict ResultSet (14):
          Number of opens = 1
          Rows seen = 2
          Rows filtered = 1
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 2.34
          optimizer estimated cost: 77.08
          Source result set:
          Index Row to Base Row ResultSet for SYSCONSTRAINTS:
          Number of opens = 1
          Rows seen = 2
          Columns accessed from heap = {0, 1, 2, 3}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.34
          optimizer estimated cost: 77.08
          User supplied optimizer overrides on SYSCONSTRAINTS are { index=SYSCONSTRAINTS_INDEX3, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX3 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 2
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=2
          Number of rows visited=3
          Scan type=btree
          Tree height=2
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 2.34
          optimizer estimated cost: 77.08


          Right result set:
          Index Row to Base Row ResultSet for SYSKEYS:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap = {1}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 2.34
          optimizer estimated cost: 8.75
          User supplied optimizer overrides on SYSKEYS are { index=SYSKEYS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSKEYS using index SYSKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=1
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 2.34
          optimizer estimated cost: 8.75


          Right result set:
          Project-Restrict ResultSet (19):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 4.82
          optimizer estimated cost: 169.07
          Source result set:
          Index Row to Base Row ResultSet for SYSCONGLOMERATES:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap = {1, 5, 7}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.82
          optimizer estimated cost: 169.07
          User supplied optimizer overrides on SYSCONGLOMERATES are { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=1
          Number of rows visited=2
          Scan type=btree
          Tree height=2
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 4.82
          optimizer estimated cost: 169.07


          Right result set:
          Project-Restrict ResultSet (22):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 5
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 47.58
          optimizer estimated cost: 397.76
          Source result set:
          Index Row to Base Row ResultSet for SYSCOLUMNS:
          Number of opens = 1
          Rows seen = 6
          Columns accessed from heap = {0, 1, 2}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 47.58
          optimizer estimated cost: 397.76
          User supplied optimizer overrides on SYSCOLUMNS are { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched={0, 2}
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=6
          Number of rows visited=7
          Scan type=btree
          Tree height=2
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 47.58
          optimizer estimated cost: 397.76



          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88638), (SESSIONID = 1), CALL SYSIBM.SQLFOREIGNKEYS(?,?,?,?,?,?,?) ******* null
          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88638), (SESSIONID = 1), EXECUTE STATEMENT SYS."getImportedKeys" ******* Sort ResultSet:
          Number of opens = 1
          Rows input = 0
          Rows returned = 0
          Eliminate duplicates = false
          In sorted order = false
          Sort information:
          Number of rows input=0
          Number of rows output=0
          Sort type=internal
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1583882.90
          Source result set:
          Project-Restrict ResultSet (41):
          Number of opens = 1
          Rows seen = 0
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1583882.90
          Source result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1583882.90
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 18151.34
          optimizer estimated cost: 86691.48
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 23051.65
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 19756.65
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 15848.39
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 12553.39
          Left result set:
          Project-Restrict ResultSet (27):
          Number of opens = 1
          Rows seen = 0
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 7718.61
          Source result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 7718.61
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 0
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 89.30
          optimizer estimated cost: 352.73
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 2
          Rows seen from the right = 0
          Rows filtered = 0
          Rows returned = 0
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.34
          optimizer estimated cost: 39.63
          Left result set:
          User supplied optimizer overrides for join are { joinOrder=FIXED }
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 2
          Rows filtered = 0
          Rows returned = 2
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.34
          optimizer estimated cost: 20.86
          Left result set:
          Project-Restrict ResultSet (17):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 6.58
          Source result set:
          Nested Loop Exists Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 6.58
          Left result set:
          Project-Restrict ResultSet (14):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          Source result set:
          Index Row to Base Row ResultSet for SYSSCHEMAS:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap = {0}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          User supplied optimizer overrides on SYSSCHEMAS are { index=SYSSCHEMAS_INDEX1, joinStrategy=NESTEDLOOP }

          Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=2
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99

          Right result set:
          Index Row to Base Row ResultSet for SYSTABLES:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap =

          {0}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 1.59
          User supplied optimizer overrides on SYSTABLES are

          { index=SYSTABLES_INDEX1 }

          Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=1
          Scan type=btree
          Tree height=1
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          1
          stop position:
          > on first 2 column(s).
          Ordered null semantics on the following columns:
          1
          qualifiers:
          None
          optimizer estimated row count: 0.43
          optimizer estimated cost: 1.59

          Right result set:
          Index Row to Base Row ResultSet for SYSCONSTRAINTS:
          Number of opens = 1
          Rows seen = 2
          Columns accessed from heap =

          {0, 2}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.34
          optimizer estimated cost: 14.27
          User supplied optimizer overrides on SYSCONSTRAINTS are

          { index=SYSCONSTRAINTS_INDEX3, joinStrategy=NESTEDLOOP }

          Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX3 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 2
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=2
          Number of rows visited=3
          Scan type=btree
          Tree height=2
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 4.34
          optimizer estimated cost: 14.27

          Right result set:
          Index Row to Base Row ResultSet for SYSFOREIGNKEYS:
          Number of opens = 2
          Rows seen = 0
          Columns accessed from heap =

          {1, 2, 3}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 4.34
          optimizer estimated cost: 18.78
          User supplied optimizer overrides on SYSFOREIGNKEYS are

          { index=SYSFOREIGNKEYS_INDEX1, joinStrategy=NESTEDLOOP }

          Index Scan ResultSet for SYSFOREIGNKEYS using index SYSFOREIGNKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 2
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=0
          Number of rows visited=2
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          > on first 1 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 4.34
          optimizer estimated cost: 18.78

          Right result set:
          Index Row to Base Row ResultSet for SYSCONGLOMERATES:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap =

          {5}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 89.30
          optimizer estimated cost: 313.10
          User supplied optimizer overrides on SYSCONGLOMERATES are

          { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 89.30
          optimizer estimated cost: 313.10


          Right result set:
          Project-Restrict ResultSet (26):
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 7365.89
          Source result set:
          Index Row to Base Row ResultSet for SYSCOLUMNS:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {2}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 7365.89
          User supplied optimizer overrides on SYSCOLUMNS are { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 881.13
          optimizer estimated cost: 7365.89


          Right result set:
          Index Row to Base Row ResultSet for SYSCONSTRAINTS:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {1, 2}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 4834.78
          User supplied optimizer overrides on SYSCONSTRAINTS are { index=SYSCONSTRAINTS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 881.13
          optimizer estimated cost: 4834.78


          Right result set:
          Index Row to Base Row ResultSet for SYSTABLES:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {1, 3}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3295.00
          User supplied optimizer overrides on SYSTABLES are { index=SYSTABLES_INDEX2, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX2 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3295.00


          Right result set:
          Index Row to Base Row ResultSet for SYSSCHEMAS:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {1}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3908.27
          User supplied optimizer overrides on SYSSCHEMAS are { index=SYSSCHEMAS_INDEX2, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX2 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3908.27


          Right result set:
          Index Row to Base Row ResultSet for SYSKEYS:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {1}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3295.00
          User supplied optimizer overrides on SYSKEYS are { index=SYSKEYS_INDEX1, joinStrategy=NESTEDLOOP }
          Index Scan ResultSet for SYSKEYS using index SYSKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          Column[0][0] Id: 0
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 881.13
          optimizer estimated cost: 3295.00


          Right result set:
          Index Row to Base Row ResultSet for SYSCONGLOMERATES:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap = {5, 7}
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 18151.34
          optimizer estimated cost: 63639.83
          User supplied optimizer overrides on SYSCONGLOMERATES are { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP }

          Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 18151.34
          optimizer estimated cost: 63639.83

          Right result set:
          Project-Restrict ResultSet (40):
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1497191.43
          Source result set:
          Index Row to Base Row ResultSet for SYSCOLUMNS:
          Number of opens = 0
          Rows seen = 0
          Columns accessed from heap =

          {0, 1, 2}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1497191.43
          User supplied optimizer overrides on SYSCOLUMNS are

          { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP }

          Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 0
          Rows seen = 0
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0

          scan information:
          start position:
          Positioning information not available because this ResultSet was never opened.
          stop position:
          Positioning information not available because this ResultSet was never opened.
          qualifiers:
          None
          optimizer estimated row count: 17909.93
          optimizer estimated cost: 1497191.43

          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88640), (SESSIONID = 1), CALL SYSIBM.SQLCOLUMNS(?,?,?,?,?) ******* null
          Tue May 21 11:29:56 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 88640), (SESSIONID = 1), EXECUTE STATEMENT SYS."getColumns" ******* Sort ResultSet:
          Number of opens = 1
          Rows input = 6
          Rows returned = 6
          Eliminate duplicates = false
          In sorted order = false
          Sort information:
          Number of rows input=6
          Number of rows output=6
          Sort type=internal
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.86
          optimizer estimated cost: 10.74
          Source result set:
          Project-Restrict ResultSet (12):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          restriction = false
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.86
          optimizer estimated cost: 10.74
          Source result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 6
          Rows filtered = 0
          Rows returned = 6
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.86
          optimizer estimated cost: 10.74
          Left result set:
          Nested Loop Join ResultSet:
          Number of opens = 1
          Rows seen from the left = 1
          Rows seen from the right = 1
          Rows filtered = 0
          Rows returned = 1
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.11
          optimizer estimated cost: 6.42
          Left result set:
          Project-Restrict ResultSet (5):
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          Source result set:
          Index Row to Base Row ResultSet for SYSSCHEMAS:
          Number of opens = 1
          Rows seen = 1
          Columns accessed from heap =

          {0, 1}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99
          Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 1
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=2
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=1
          Number of rows visited=2
          Scan type=btree
          Tree height=1
          start position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          None
          optimizer estimated row count: 0.43
          optimizer estimated cost: 4.99

          Right result set:
          Project-Restrict ResultSet (8):
          Number of opens = 1
          Rows seen = 3
          Rows filtered = 2
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.11
          optimizer estimated cost: 1.43
          Source result set:
          Index Row to Base Row ResultSet for SYSTABLES:
          Number of opens = 1
          Rows seen = 3
          Columns accessed from heap =

          {0, 1, 3}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.11
          optimizer estimated cost: 1.43
          Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 3
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0
          Number of pages visited=1
          Number of rows qualified=3
          Number of rows visited=4
          Scan type=btree
          Tree height=1
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          1
          stop position:
          >= on first 1 column(s).
          Ordered null semantics on the following columns:
          qualifiers:
          Column[0][0] Id: 1
          Operator: =
          Ordered nulls: false
          Unknown return value: false
          Negate comparison result: false
          optimizer estimated row count: 0.11
          optimizer estimated cost: 1.43

          Right result set:
          Project-Restrict ResultSet (11):
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          restriction = true
          projection = true
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          restriction time (milliseconds) = 0
          projection time (milliseconds) = 0
          optimizer estimated row count: 0.86
          optimizer estimated cost: 4.32
          Source result set:
          Index Row to Base Row ResultSet for SYSCOLUMNS:
          Number of opens = 1
          Rows seen = 6
          Columns accessed from heap =

          {0, 1, 2, 3, 4, 7, 8}

          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          optimizer estimated row count: 0.86
          optimizer estimated cost: 4.32
          Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
          Number of opens = 1
          Rows seen = 6
          Rows filtered = 0
          Fetch Size = 16
          constructor time (milliseconds) = 0
          open time (milliseconds) = 0
          next time (milliseconds) = 0
          close time (milliseconds) = 0
          next time in milliseconds/row = 0

          scan information:
          Bit set of columns fetched=All
          Number of columns fetched=3
          Number of deleted rows visited=0
          Number of pages visited=2
          Number of rows qualified=6
          Number of rows visited=7
          Scan type=btree
          Tree height=2
          start position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          0
          stop position:
          >= on first 2 column(s).
          Ordered null semantics on the following columns:
          0
          qualifiers:
          None
          optimizer estimated row count: 0.86
          optimizer estimated cost: 4.32

          Show
          Tony Brusseau added a comment - - edited Here is the query plan for the disjunctive delete, notice the full table scan on 29K rows even though term, pred and mt all have individual indexes (which I've confirmed): Tue May 21 11:28:34 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88632), (SESSIONID = 1), DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = 1407374883554049) OR (pred = 1407374883554049) OR (mt = 1407374883554049) ******* Delete Cascade ResultSet using row locking: deferred: false Rows deleted = 418 Indexes updated = 9 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 418 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 14751.50 optimizer estimated cost: 12365.71 Source result set: Table Scan ResultSet for MT_GAF_TOP_LEVEL_TERM_COUNTS at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 418 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 1, 2, 3, 4} Number of columns fetched=5 Number of pages visited=67 Number of rows qualified=418 Number of rows visited=29498 Scan type=heap start position: null stop position: null qualifiers: Column [1] [0] Id: 3 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Column [1] [1] Id: 2 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Column [1] [2] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 14751.50 optimizer estimated cost: 12365.71 Referential Actions On Dependent Tables: Delete Cascade ResultSet using row locking: deferred: false Rows deleted = 860 Indexes updated = 3 Execute Time = 0 Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 860 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 129341.00 optimizer estimated cost: 38529.20 Source result set: Index Scan ResultSet for GAF_ARG using index On Foreign Key at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 860 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=837 Number of rows qualified=860 Number of rows visited=1278 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 129341.00 optimizer estimated cost: 38529.20 Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88634), (SESSIONID = 1), CALL SYSIBM.SQLTABLES(?,?,?,?,?) ******* null Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88634), (SESSIONID = 1), EXECUTE STATEMENT SYS."getTables" ******* Sort ResultSet: Number of opens = 1 Rows input = 0 Rows returned = 0 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.00 optimizer estimated cost: 6.08 Source result set: Project-Restrict ResultSet (19): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.00 optimizer estimated cost: 6.08 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 48 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.00 optimizer estimated cost: 6.08 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 12 Rows seen from the right = 48 Rows filtered = 0 Rows returned = 48 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.70 optimizer estimated cost: 4.99 Left result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 12 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Source result set: Index Row to Base Row ResultSet for SYSSCHEMAS: Number of opens = 1 Rows seen = 12 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 12 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=12 Number of rows visited=12 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Right result set: Union ResultSet: Number of opens = 12 Rows seen from the left = 36 Rows seen from the right = 12 Rows returned = 48 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.70 optimizer estimated cost: 0.00 Left result set: Union ResultSet: Number of opens = 12 Rows seen from the left = 24 Rows seen from the right = 12 Rows returned = 36 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.28 optimizer estimated cost: 0.00 Left result set: Union ResultSet: Number of opens = 12 Rows seen from the left = 12 Rows seen from the right = 12 Rows returned = 24 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.85 optimizer estimated cost: 0.00 Left result set: Normalize ResultSet: Number of opens = 12 Rows seen = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.85 optimizer estimated cost: 0.00 Source result set: Row ResultSet: Number of opens = 12 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 0.00 Right result set: Row ResultSet: Number of opens = 12 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 0.00 Right result set: Normalize ResultSet: Number of opens = 12 Rows seen = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.28 optimizer estimated cost: 0.00 Source result set: Row ResultSet: Number of opens = 12 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 0.00 Right result set: Normalize ResultSet: Number of opens = 12 Rows seen = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 1.70 optimizer estimated cost: 0.00 Source result set: Row ResultSet: Number of opens = 12 Rows returned = 12 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 0.00 Right result set: Project-Restrict ResultSet (18): Number of opens = 48 Rows seen = 0 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.00 optimizer estimated cost: 1.08 Source result set: Index Row to Base Row ResultSet for SYSTABLES: Number of opens = 48 Rows seen = 0 Columns accessed from heap = {1, 2, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.00 optimizer estimated cost: 1.08 Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 48 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=48 Number of rows qualified=0 Number of rows visited=48 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 1 stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: Column [0] [0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.00 optimizer estimated cost: 1.08 Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88635), (SESSIONID = 1), CALL SYSIBM.SQLPRIMARYKEYS(?,?,?,?) ******* null Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88635), (SESSIONID = 1), EXECUTE STATEMENT SYS."getPrimaryKeys" ******* Sort ResultSet: Number of opens = 1 Rows input = 1 Rows returned = 1 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=1 Number of rows output=1 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 47.58 optimizer estimated cost: 688.05 Source result set: Project-Restrict ResultSet (23): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 47.58 optimizer estimated cost: 688.05 Source result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 47.58 optimizer estimated cost: 688.05 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.82 optimizer estimated cost: 290.29 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.34 optimizer estimated cost: 121.22 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.34 optimizer estimated cost: 112.46 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.30 optimizer estimated cost: 35.38 Left result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2.70 optimizer estimated cost: 15.68 Source result set: Index Row to Base Row ResultSet for SYSTABLES: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0, 1, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.70 optimizer estimated cost: 15.68 User supplied optimizer overrides on SYSTABLES are { index=SYSTABLES_INDEX1 } Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 2.70 optimizer estimated cost: 15.68 Right result set: Project-Restrict ResultSet (11): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2.30 optimizer estimated cost: 19.70 Source result set: Index Row to Base Row ResultSet for SYSSCHEMAS: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.30 optimizer estimated cost: 19.70 User supplied optimizer overrides on SYSSCHEMAS are { index=SYSSCHEMAS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 2.30 optimizer estimated cost: 19.70 Right result set: Project-Restrict ResultSet (14): Number of opens = 1 Rows seen = 2 Rows filtered = 1 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 2.34 optimizer estimated cost: 77.08 Source result set: Index Row to Base Row ResultSet for SYSCONSTRAINTS: Number of opens = 1 Rows seen = 2 Columns accessed from heap = {0, 1, 2, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.34 optimizer estimated cost: 77.08 User supplied optimizer overrides on SYSCONSTRAINTS are { index=SYSCONSTRAINTS_INDEX3, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX3 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 2 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=2 Number of rows visited=3 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 2.34 optimizer estimated cost: 77.08 Right result set: Index Row to Base Row ResultSet for SYSKEYS: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 2.34 optimizer estimated cost: 8.75 User supplied optimizer overrides on SYSKEYS are { index=SYSKEYS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSKEYS using index SYSKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 2.34 optimizer estimated cost: 8.75 Right result set: Project-Restrict ResultSet (19): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 4.82 optimizer estimated cost: 169.07 Source result set: Index Row to Base Row ResultSet for SYSCONGLOMERATES: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {1, 5, 7} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.82 optimizer estimated cost: 169.07 User supplied optimizer overrides on SYSCONGLOMERATES are { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 4.82 optimizer estimated cost: 169.07 Right result set: Project-Restrict ResultSet (22): Number of opens = 1 Rows seen = 6 Rows filtered = 5 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 47.58 optimizer estimated cost: 397.76 Source result set: Index Row to Base Row ResultSet for SYSCOLUMNS: Number of opens = 1 Rows seen = 6 Columns accessed from heap = {0, 1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 47.58 optimizer estimated cost: 397.76 User supplied optimizer overrides on SYSCOLUMNS are { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 2} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=6 Number of rows visited=7 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 47.58 optimizer estimated cost: 397.76 Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88638), (SESSIONID = 1), CALL SYSIBM.SQLFOREIGNKEYS(?,?,?,?,?,?,?) ******* null Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88638), (SESSIONID = 1), EXECUTE STATEMENT SYS."getImportedKeys" ******* Sort ResultSet: Number of opens = 1 Rows input = 0 Rows returned = 0 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=0 Number of rows output=0 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 17909.93 optimizer estimated cost: 1583882.90 Source result set: Project-Restrict ResultSet (41): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 17909.93 optimizer estimated cost: 1583882.90 Source result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 17909.93 optimizer estimated cost: 1583882.90 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 18151.34 optimizer estimated cost: 86691.48 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 23051.65 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 19756.65 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 15848.39 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 12553.39 Left result set: Project-Restrict ResultSet (27): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 7718.61 Source result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 7718.61 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 0 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 89.30 optimizer estimated cost: 352.73 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 2 Rows seen from the right = 0 Rows filtered = 0 Rows returned = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.34 optimizer estimated cost: 39.63 Left result set: User supplied optimizer overrides for join are { joinOrder=FIXED } Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 2 Rows filtered = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.34 optimizer estimated cost: 20.86 Left result set: Project-Restrict ResultSet (17): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 6.58 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 6.58 Left result set: Project-Restrict ResultSet (14): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Source result set: Index Row to Base Row ResultSet for SYSSCHEMAS: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 User supplied optimizer overrides on SYSSCHEMAS are { index=SYSSCHEMAS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Right result set: Index Row to Base Row ResultSet for SYSTABLES: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 1.59 User supplied optimizer overrides on SYSTABLES are { index=SYSTABLES_INDEX1 } Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 1 stop position: > on first 2 column(s). Ordered null semantics on the following columns: 1 qualifiers: None optimizer estimated row count: 0.43 optimizer estimated cost: 1.59 Right result set: Index Row to Base Row ResultSet for SYSCONSTRAINTS: Number of opens = 1 Rows seen = 2 Columns accessed from heap = {0, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.34 optimizer estimated cost: 14.27 User supplied optimizer overrides on SYSCONSTRAINTS are { index=SYSCONSTRAINTS_INDEX3, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX3 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 2 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=2 Number of rows visited=3 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 4.34 optimizer estimated cost: 14.27 Right result set: Index Row to Base Row ResultSet for SYSFOREIGNKEYS: Number of opens = 2 Rows seen = 0 Columns accessed from heap = {1, 2, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 4.34 optimizer estimated cost: 18.78 User supplied optimizer overrides on SYSFOREIGNKEYS are { index=SYSFOREIGNKEYS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSFOREIGNKEYS using index SYSFOREIGNKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 2 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=0 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 1 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 4.34 optimizer estimated cost: 18.78 Right result set: Index Row to Base Row ResultSet for SYSCONGLOMERATES: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {5} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 89.30 optimizer estimated cost: 313.10 User supplied optimizer overrides on SYSCONGLOMERATES are { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 89.30 optimizer estimated cost: 313.10 Right result set: Project-Restrict ResultSet (26): Number of opens = 0 Rows seen = 0 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 7365.89 Source result set: Index Row to Base Row ResultSet for SYSCOLUMNS: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 7365.89 User supplied optimizer overrides on SYSCOLUMNS are { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 881.13 optimizer estimated cost: 7365.89 Right result set: Index Row to Base Row ResultSet for SYSCONSTRAINTS: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 4834.78 User supplied optimizer overrides on SYSCONSTRAINTS are { index=SYSCONSTRAINTS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 881.13 optimizer estimated cost: 4834.78 Right result set: Index Row to Base Row ResultSet for SYSTABLES: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {1, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 3295.00 User supplied optimizer overrides on SYSTABLES are { index=SYSTABLES_INDEX2, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 881.13 optimizer estimated cost: 3295.00 Right result set: Index Row to Base Row ResultSet for SYSSCHEMAS: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 3908.27 User supplied optimizer overrides on SYSSCHEMAS are { index=SYSSCHEMAS_INDEX2, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX2 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 881.13 optimizer estimated cost: 3908.27 Right result set: Index Row to Base Row ResultSet for SYSKEYS: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 881.13 optimizer estimated cost: 3295.00 User supplied optimizer overrides on SYSKEYS are { index=SYSKEYS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSKEYS using index SYSKEYS_INDEX1 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: Column [0] [0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 881.13 optimizer estimated cost: 3295.00 Right result set: Index Row to Base Row ResultSet for SYSCONGLOMERATES: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {5, 7} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 18151.34 optimizer estimated cost: 63639.83 User supplied optimizer overrides on SYSCONGLOMERATES are { index=SYSCONGLOMERATES_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCONGLOMERATES using index SYSCONGLOMERATES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 18151.34 optimizer estimated cost: 63639.83 Right result set: Project-Restrict ResultSet (40): Number of opens = 0 Rows seen = 0 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 17909.93 optimizer estimated cost: 1497191.43 Source result set: Index Row to Base Row ResultSet for SYSCOLUMNS: Number of opens = 0 Rows seen = 0 Columns accessed from heap = {0, 1, 2} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 17909.93 optimizer estimated cost: 1497191.43 User supplied optimizer overrides on SYSCOLUMNS are { index=SYSCOLUMNS_INDEX1, joinStrategy=NESTEDLOOP } Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 0 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: start position: Positioning information not available because this ResultSet was never opened. stop position: Positioning information not available because this ResultSet was never opened. qualifiers: None optimizer estimated row count: 17909.93 optimizer estimated cost: 1497191.43 Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88640), (SESSIONID = 1), CALL SYSIBM.SQLCOLUMNS(?,?,?,?,?) ******* null Tue May 21 11:29:56 CDT 2013 Thread [DRDAConnThread_3,5,main] (XID = 88640), (SESSIONID = 1), EXECUTE STATEMENT SYS."getColumns" ******* Sort ResultSet: Number of opens = 1 Rows input = 6 Rows returned = 6 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=6 Number of rows output=6 Sort type=internal constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.86 optimizer estimated cost: 10.74 Source result set: Project-Restrict ResultSet (12): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.86 optimizer estimated cost: 10.74 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 6 Rows filtered = 0 Rows returned = 6 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.86 optimizer estimated cost: 10.74 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.11 optimizer estimated cost: 6.42 Left result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Source result set: Index Row to Base Row ResultSet for SYSSCHEMAS: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {0, 1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=2 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 0.43 optimizer estimated cost: 4.99 Right result set: Project-Restrict ResultSet (8): Number of opens = 1 Rows seen = 3 Rows filtered = 2 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.11 optimizer estimated cost: 1.43 Source result set: Index Row to Base Row ResultSet for SYSTABLES: Number of opens = 1 Rows seen = 3 Columns accessed from heap = {0, 1, 3} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.11 optimizer estimated cost: 1.43 Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=4 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 1 stop position: >= on first 1 column(s). Ordered null semantics on the following columns: qualifiers: Column [0] [0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 0.11 optimizer estimated cost: 1.43 Right result set: Project-Restrict ResultSet (11): Number of opens = 1 Rows seen = 6 Rows filtered = 0 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 0.86 optimizer estimated cost: 4.32 Source result set: Index Row to Base Row ResultSet for SYSCOLUMNS: Number of opens = 1 Rows seen = 6 Columns accessed from heap = {0, 1, 2, 3, 4, 7, 8} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 0.86 optimizer estimated cost: 4.32 Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=2 Number of rows qualified=6 Number of rows visited=7 Scan type=btree Tree height=2 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 stop position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 qualifiers: None optimizer estimated row count: 0.86 optimizer estimated cost: 4.32
          Hide
          Tony Brusseau added a comment -

          I can confirm that the issue with disjunction in deletes is still an issue even in 10.10.1.1. I'm trying to create a reproducible test now. Should I open a new bug for this?

          Show
          Tony Brusseau added a comment - I can confirm that the issue with disjunction in deletes is still an issue even in 10.10.1.1. I'm trying to create a reproducible test now. Should I open a new bug for this?
          Hide
          Dag H. Wanvik added a comment -

          Tony, any results by now?

          Show
          Dag H. Wanvik added a comment - Tony, any results by now?
          Hide
          Mamta A. Satoor added a comment -

          It should be fine to backport this jira further back if someone is interested in doing so. I have backported it upto 10.8 so far.

          Also, I will leave the jira open for another week in case if Tony finds something while trying to reproduce the DELETE case.

          Show
          Mamta A. Satoor added a comment - It should be fine to backport this jira further back if someone is interested in doing so. I have backported it upto 10.8 so far. Also, I will leave the jira open for another week in case if Tony finds something while trying to reproduce the DELETE case.
          Hide
          Mike Matrigali added a comment -

          thanks for backporting this to 10.8. Even though the regression was introduced in 10.9, this change actually
          also helps previous releases. With this change some queries that use unique indexes will pick better plans.
          In those cases previous to this fix one would first have to update the statistics to get the right plan to be picked.

          Show
          Mike Matrigali added a comment - thanks for backporting this to 10.8. Even though the regression was introduced in 10.9, this change actually also helps previous releases. With this change some queries that use unique indexes will pick better plans. In those cases previous to this fix one would first have to update the statistics to get the right plan to be picked.
          Hide
          Mamta A. Satoor added a comment -

          Backported changes to 10.8 with revision 1480730

          Show
          Mamta A. Satoor added a comment - Backported changes to 10.8 with revision 1480730
          Hide
          Mamta A. Satoor added a comment -

          Backported changes to 10.9 with revision 1480320

          Show
          Mamta A. Satoor added a comment - Backported changes to 10.9 with revision 1480320
          Hide
          Mamta A. Satoor added a comment -

          Backported the code changes and some additional test to 10.10 with revision 1480153

          Show
          Mamta A. Satoor added a comment - Backported the code changes and some additional test to 10.10 with revision 1480153
          Hide
          Mamta A. Satoor added a comment -

          In the mean time, I will work on backporting the changes to 10.10 codeline since the changes certainly fix the problem with the SELECT statement in this jira not using the index scan.

          Show
          Mamta A. Satoor added a comment - In the mean time, I will work on backporting the changes to 10.10 codeline since the changes certainly fix the problem with the SELECT statement in this jira not using the index scan.
          Hide
          Mamta A. Satoor added a comment -

          I tried the new junit delete test case in 10.10 and I see that the test runs fine on 10.10 without any code changes that went in for this jira. It is possible that the delete test case does not exactly do what Tony is trying to do. Tony, thanks for doing the additional testing for a reproducible test case. I am copying part of the junit test here for easier reference

          CREATE TABLE MT_GAF_TOP_LEVEL_TERM_COUNTS (mt BIGINT NOT NULL, term BIGINT NOT NULL, term_index INTEGER NOT NULL, usage_count BIGINT NOT NULL );
          ALTER TABLE MT_GAF_TOP_LEVEL_TERM_COUNTS ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (mt, term, term_index);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON MT_GAF_TOP_LEVEL_TERM_COUNTS(mt);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON MT_GAF_TOP_LEVEL_TERM_COUNTS(term);

          Then I insert 10K rows in the table
          PreparedStatement ps = s.getConnection().prepareStatement(
          "insert into " + DERBY_6045_DATA_TABLE2 +
          " VALUES (?, ?, ?, ?)");
          int numberOfRows = 10000;
          for (int i=1; i<=numberOfRows; i++)

          { ps.setInt(1, i); ps.setInt(2, i); ps.setInt(3, i); ps.setInt(4, i); ps.executeUpdate(); }

          Next, I execute following 2 delete statements and check the runtime statistics for them
          if (useParameterMarkers)

          { ps = prepareStatement("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = ?) "); ps.setInt(1,1); ps.execute(); }

          else

          { s.execute("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = 2) "); }

          rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
          assertTrue(rtsp.usedIndexScan());

          if (useParameterMarkers)

          { ps = prepareStatement("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?)"); ps.setInt(1,3); ps.setInt(2,4); ps.execute(); }

          else

          { s.execute("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)"); }

          rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
          assertTrue(rtsp.usedIndexScan());

          Show
          Mamta A. Satoor added a comment - I tried the new junit delete test case in 10.10 and I see that the test runs fine on 10.10 without any code changes that went in for this jira. It is possible that the delete test case does not exactly do what Tony is trying to do. Tony, thanks for doing the additional testing for a reproducible test case. I am copying part of the junit test here for easier reference CREATE TABLE MT_GAF_TOP_LEVEL_TERM_COUNTS (mt BIGINT NOT NULL, term BIGINT NOT NULL, term_index INTEGER NOT NULL, usage_count BIGINT NOT NULL ); ALTER TABLE MT_GAF_TOP_LEVEL_TERM_COUNTS ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (mt, term, term_index); CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON MT_GAF_TOP_LEVEL_TERM_COUNTS(mt); CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON MT_GAF_TOP_LEVEL_TERM_COUNTS(term); Then I insert 10K rows in the table PreparedStatement ps = s.getConnection().prepareStatement( "insert into " + DERBY_6045_DATA_TABLE2 + " VALUES (?, ?, ?, ?)"); int numberOfRows = 10000; for (int i=1; i<=numberOfRows; i++) { ps.setInt(1, i); ps.setInt(2, i); ps.setInt(3, i); ps.setInt(4, i); ps.executeUpdate(); } Next, I execute following 2 delete statements and check the runtime statistics for them if (useParameterMarkers) { ps = prepareStatement("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = ?) "); ps.setInt(1,1); ps.execute(); } else { s.execute("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = 2) "); } rtsp = SQLUtilities.getRuntimeStatisticsParser(s); assertTrue(rtsp.usedIndexScan()); if (useParameterMarkers) { ps = prepareStatement("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?)"); ps.setInt(1,3); ps.setInt(2,4); ps.execute(); } else { s.execute("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)"); } rtsp = SQLUtilities.getRuntimeStatisticsParser(s); assertTrue(rtsp.usedIndexScan());
          Hide
          Tony Brusseau added a comment -

          OK. I'll do some addtional testing and try to create a reproducible test case. It may be a couple of days till I can get to this though.

          Show
          Tony Brusseau added a comment - OK. I'll do some addtional testing and try to create a reproducible test case. It may be a couple of days till I can get to this though.
          Hide
          Mike Matrigali added a comment -

          Did your new delete tests show any problem before your fix? It would be nice to get a junit test case checked in that shows the
          delete problem being reported, especially if it is still a problem.

          Show
          Mike Matrigali added a comment - Did your new delete tests show any problem before your fix? It would be nice to get a junit test case checked in that shows the delete problem being reported, especially if it is still a problem.
          Hide
          Mamta A. Satoor added a comment -

          I committed a junit test for DELETE statement on trunk with revision 1479607.
          Commiting test that shows that we use index scan for DELETE statement on a table with appropriate indexes. This happens with or without update statistics and with or without parameterized statement.

          Tony, can you please try the jars from the trunk and see if resolves the problem with the SELECT and DELETE test cases that you provided? The DELETE case may be different than the SELECTproblem that got fixed through the engine changes for this jira, and if so, then may be we can open a new jira for the DELETE case and pursue it as part of that jira.

          Show
          Mamta A. Satoor added a comment - I committed a junit test for DELETE statement on trunk with revision 1479607. Commiting test that shows that we use index scan for DELETE statement on a table with appropriate indexes. This happens with or without update statistics and with or without parameterized statement. Tony, can you please try the jars from the trunk and see if resolves the problem with the SELECT and DELETE test cases that you provided? The DELETE case may be different than the SELECTproblem that got fixed through the engine changes for this jira, and if so, then may be we can open a new jira for the DELETE case and pursue it as part of that jira.
          Hide
          Mamta A. Satoor added a comment -

          Committed few more regression tests in trunk(10.11) with revisions 1465830 and 1466097

          Show
          Mamta A. Satoor added a comment - Committed few more regression tests in trunk(10.11) with revisions 1465830 and 1466097
          Hide
          Tony Brusseau added a comment -

          Thanks Mamta and everyone involoved!

          Show
          Tony Brusseau added a comment - Thanks Mamta and everyone involoved!
          Hide
          Mamta A. Satoor added a comment -

          Committed changes for this jira with revision 1463378. The commit comments were as follows
          ****************************************************
          DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)

          Changes for DERBY-3790 (Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column.) caused Derby to use table scan rather than index scan since we do not generate stats for single column unique index. As part of that change, we forgot to make code changes in optimizer to compensate for missing stats for such indexes. As found in DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows), even though we do not keep the statistics for single-column unique indexes, we should improve the selectivity of such an index when the index is being considered by the optimizer.

          Making the relevant code changes now allows us to enable the tests in InListMultiProbeTest.java since they will now run correctly by picking up index scan rather than table scan.

          Additionally, one test in refActions1.sql does not have order by to it and there are only 3 rows in the table. Without the changes for DERBY-6045 we were using index scan for it but now it uses table scan which is giving the rows in different order. I have added order by to the query so plan selection does not change order of the rows. The reason behind table scan might be that with only 3 rows in the table, it is cheaper to do table sccan rather than index scan.
          ****************************************************

          Show
          Mamta A. Satoor added a comment - Committed changes for this jira with revision 1463378. The commit comments were as follows **************************************************** DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows) Changes for DERBY-3790 (Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column.) caused Derby to use table scan rather than index scan since we do not generate stats for single column unique index. As part of that change, we forgot to make code changes in optimizer to compensate for missing stats for such indexes. As found in DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows), even though we do not keep the statistics for single-column unique indexes, we should improve the selectivity of such an index when the index is being considered by the optimizer. Making the relevant code changes now allows us to enable the tests in InListMultiProbeTest.java since they will now run correctly by picking up index scan rather than table scan. Additionally, one test in refActions1.sql does not have order by to it and there are only 3 rows in the table. Without the changes for DERBY-6045 we were using index scan for it but now it uses table scan which is giving the rows in different order. I have added order by to the query so plan selection does not change order of the rows. The reason behind table scan might be that with only 3 rows in the table, it is cheaper to do table sccan rather than index scan. ****************************************************
          Hide
          Knut Anders Hatlen added a comment -

          The #2 patch probably also needs to update the canon for refActions1.

          Show
          Knut Anders Hatlen added a comment - The #2 patch probably also needs to update the canon for refActions1.
          Hide
          Mamta A. Satoor added a comment -

          I debugged the failure in refActions1. I found that with my changes, with only 3 rows in the table db2test.dept, we use table scan but without my changes, we use index scan. But if I add just few more rows in db2test.dept - totalling to be say 30 rows, we use index scan for both with and without my changes. I believe that the reason behind table scan (with only 3 rows in the table) with my changes is that there are not enough rows in the table for it to make sense to use index scan. I am attaching a new patch (DERBY6045_patch2_diff.txt) which uses order by for the query below so we will not see the order change in the rows. Additionally, I have taken care of comments from Knut. If there are no further comments, then I will go ahead and commit the patch soon, Thanks
          select * from db2test.dept where dno in (select vdno from
          db2test.vempunion)
          and dno in ('K55', 'K52')
          order by dno;

          Show
          Mamta A. Satoor added a comment - I debugged the failure in refActions1. I found that with my changes, with only 3 rows in the table db2test.dept, we use table scan but without my changes, we use index scan. But if I add just few more rows in db2test.dept - totalling to be say 30 rows, we use index scan for both with and without my changes. I believe that the reason behind table scan (with only 3 rows in the table) with my changes is that there are not enough rows in the table for it to make sense to use index scan. I am attaching a new patch (DERBY6045_patch2_diff.txt) which uses order by for the query below so we will not see the order change in the rows. Additionally, I have taken care of comments from Knut. If there are no further comments, then I will go ahead and commit the patch soon, Thanks select * from db2test.dept where dno in (select vdno from db2test.vempunion) and dno in ('K55', 'K52') order by dno;
          Hide
          Mike Matrigali added a comment -

          after verifying the plan is ok for the diff query, I suggest changing the test to add an order by to make the result order deterministic.

          Show
          Mike Matrigali added a comment - after verifying the plan is ok for the diff query, I suggest changing the test to add an order by to make the result order deterministic.
          Hide
          Mike Matrigali added a comment -

          With your change will the code now pick index even if update statistics has never been run? Looking to see if this would be a good change to backport to 10.8 and before. Have thought that it was bad that you needed update statistics at all to get this kind of query with a unique index to pick the index.

          Show
          Mike Matrigali added a comment - With your change will the code now pick index even if update statistics has never been run? Looking to see if this would be a good change to backport to 10.8 and before. Have thought that it was bad that you needed update statistics at all to get this kind of query with a unique index to pick the index.
          Hide
          Mike Matrigali added a comment -

          I agree with knut's comments. change looks good to me.

          Show
          Mike Matrigali added a comment - I agree with knut's comments. change looks good to me.
          Hide
          Knut Anders Hatlen added a comment -

          The suggested fix looks reasonable to me. I agree that the ordering change in refActions1 looks benign. Still, it might be prudent to compare the query plans of the query before and after the fix to verify that the change is not an unexpected one.

          The check for startStopPredCount being exactly one is probably redundant, since I don't think it is possible to have more start/stop predicates than the number of key columns. I'm not sure, though, and I don't think keeping it will do any harm.

          Some code nits:

          • Please add braces around the if branch in the new if/else statement.
          • The nested if statement inside the else branch could pulled up one level by changing "else" to "else if" in the parent if statement. That would save one indentation level and give more horizontal space to the code and comments inside it.
          • The irg variable is first initialized to null and then on the next line set to cd.getIndexDescriptor(). Setting it to the index descriptor immediately, and skipping the intermediate step where it's null, would make it a bit clearer.
          Show
          Knut Anders Hatlen added a comment - The suggested fix looks reasonable to me. I agree that the ordering change in refActions1 looks benign. Still, it might be prudent to compare the query plans of the query before and after the fix to verify that the change is not an unexpected one. The check for startStopPredCount being exactly one is probably redundant, since I don't think it is possible to have more start/stop predicates than the number of key columns. I'm not sure, though, and I don't think keeping it will do any harm. Some code nits: Please add braces around the if branch in the new if/else statement. The nested if statement inside the else branch could pulled up one level by changing "else" to "else if" in the parent if statement. That would save one indentation level and give more horizontal space to the code and comments inside it. The irg variable is first initialized to null and then on the next line set to cd.getIndexDescriptor(). Setting it to the index descriptor immediately, and skipping the intermediate step where it's null, would make it a bit clearer.
          Hide
          Mamta A. Satoor added a comment -

          I debugged the code a little bit to see why on trunk following query uses index scan
          SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11;
          when we have found that the following query uses table scan on trunk. Just to recap, table VARIABLE_TERM has primary key on TERM_ID and it has 25 rows
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24);

          I found that when optimizer is considering different plans for the query, one of things that get checked is
          if (! isOneRowResultSet(predList))
          This check is in FromBaseTable.nextAccessPath. For a query with IN clause, this isOneRowResultSet(predList) will always return false but for the query with one = predicate, isOneRowResultSet(predList) will return true and hence we will pick primary key index scan for that query. Hope this helps understanding why query with = predicate uses index scan.

          Show
          Mamta A. Satoor added a comment - I debugged the code a little bit to see why on trunk following query uses index scan SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11; when we have found that the following query uses table scan on trunk. Just to recap, table VARIABLE_TERM has primary key on TERM_ID and it has 25 rows SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24); I found that when optimizer is considering different plans for the query, one of things that get checked is if (! isOneRowResultSet(predList)) This check is in FromBaseTable.nextAccessPath. For a query with IN clause, this isOneRowResultSet(predList) will always return false but for the query with one = predicate, isOneRowResultSet(predList) will return true and hence we will pick primary key index scan for that query. Hope this helps understanding why query with = predicate uses index scan.
          Hide
          Mamta A. Satoor added a comment -

          derbyall ran fine with no errors.

          With junit suite, there was one new failure but it is just the order of the rows that is different.
          1) refActions1(org.apache.derbyTesting.functionTests.tests.lang.LangScripts)junit.framework.ComparisonFailure: Output at line 8859 expected:<[2 |K52|OFC |ROBIN] > but was:<[1 |K55|DB |JOHN ] >
          at org.apache.derbyTesting.functionTests.util.CanonTestCase.compareCanon(CanonTestCase.java:109)
          at org.apache.derbyTesting.functionTests.util.ScriptTestCase.runTest(ScriptTestCase.java:204)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:117)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:424)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:441)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

          The query in question in refActions1 is as follows and it returns 2 rows
          select * from db2test.dept where dno in (select vdno from
          db2test.vempunion)
          and dno in ('K55', 'K52');

          In the master file, the output for this query is as follows(looks like this test is still not completely converted to junit and that is why there is a master for it)
          select * from db2test.dept where dno in (select vdno from
          db2test.vempunion)
          and dno in ('K55', 'K52');
          C0 |DNO|DNAME |DMGRNAME
          -------------------------------------
          2 |K52|OFC |ROBIN
          1 |K55|DB |JOHN

          With my patch, the query returns the same rows but in different order as shown below
          select * from db2test.dept where dno in (select vdno from
          db2test.vempunion)
          and dno in ('K55', 'K52');
          C0 |DNO|DNAME |DMGRNAME
          -------------------------------------
          1 |K55|DB |JOHN
          2 |K52|OFC |ROBIN

          Since there is no order by clause, should this diff be considered harmless?

          Show
          Mamta A. Satoor added a comment - derbyall ran fine with no errors. With junit suite, there was one new failure but it is just the order of the rows that is different. 1) refActions1(org.apache.derbyTesting.functionTests.tests.lang.LangScripts)junit.framework.ComparisonFailure: Output at line 8859 expected:< [2 |K52|OFC |ROBIN] > but was:< [1 |K55|DB |JOHN ] > at org.apache.derbyTesting.functionTests.util.CanonTestCase.compareCanon(CanonTestCase.java:109) at org.apache.derbyTesting.functionTests.util.ScriptTestCase.runTest(ScriptTestCase.java:204) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:117) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:424) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:441) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) The query in question in refActions1 is as follows and it returns 2 rows select * from db2test.dept where dno in (select vdno from db2test.vempunion) and dno in ('K55', 'K52'); In the master file, the output for this query is as follows(looks like this test is still not completely converted to junit and that is why there is a master for it) select * from db2test.dept where dno in (select vdno from db2test.vempunion) and dno in ('K55', 'K52'); C0 |DNO|DNAME |DMGRNAME ------------------------------------- 2 |K52|OFC |ROBIN 1 |K55|DB |JOHN With my patch, the query returns the same rows but in different order as shown below select * from db2test.dept where dno in (select vdno from db2test.vempunion) and dno in ('K55', 'K52'); C0 |DNO|DNAME |DMGRNAME ------------------------------------- 1 |K55|DB |JOHN 2 |K52|OFC |ROBIN Since there is no order by clause, should this diff be considered harmless?
          Hide
          Mamta A. Satoor added a comment -

          I am running derbyall and junit with the patch

          Show
          Mamta A. Satoor added a comment - I am running derbyall and junit with the patch
          Hide
          Mamta A. Satoor added a comment -

          FromBaseTable.java has following code to change the selectivity if statistics exist for a conglomerate but since we do not collect statistics for single-column unique index anymore, following if condition will never be true for single-column unique index and hence we will not consider that index even if it is a faster plan)
          if (seenFirstColumn && statisticsForConglomerate &&
          (startStopPredCount > 0))

          { statStartStopSelectivity = tableDescriptor.selectivityForConglomerate(cd, startStopPredCount); }

          In my attached patch, I am changing the code above to as follows

          if (seenFirstColumn && (startStopPredCount > 0))
          {
          if (statisticsForConglomerate)
          statStartStopSelectivity =
          tableDescriptor.selectivityForConglomerate(cd,
          startStopPredCount);
          else {
          if (cd.isIndex())

          { //DERBY-3790 (Investigate if request for update // statistics can be skipped for certain kind of // indexes, one instance may be unique indexes based // on one column.) But as found in DERBY-6045 (in list // multi-probe by primary key not chosen on tables with // >256 rows), even though we do not keep the // statistics for single-column unique indexes, we // should improve the selectivity of such an index // when the index is being considered by the optimizer. IndexRowGenerator irg = null; irg = cd.getIndexDescriptor(); if (irg.isUnique() && irg.numberOfOrderedColumns() == 1 && startStopPredCount == 1) statStartStopSelectivity = (double)(1/(double)baseRowCount()); }

          }
          }

          With the new code, if we come across a conglomerate with no available statistics, then we check if
          1)we are dealing with an index and
          2)if it is single-column unique index and
          3)there is one start-stop predicate, then
          go ahead and set the start-stop selectivity to 1/number of rows in the table.

          I changed the InListMultiProbeTest.java by removing x in fron of the 2 fixtures xtestDerby6045 and xtestDerby6045InsertAllRowsAdditionalUniqueIndex and both those fixtures ran fine with my patch.

          I will appreciate another pair of eyes on the code rework, especially, the check for "there is one start-stop predicate" in the if clause. The reason I think we need this check is based on the existing code that we have in TableDescriptor for indexes with statistics
          public double selectivityForConglomerate(ConglomerateDescriptor cd, int numKeys)
          throws StandardException
          {
          UUID referenceUUID = cd.getUUID();
          List sdl = getStatistics();
          for (Iterator li = sdl.iterator(); li.hasNext(); )

          { StatisticsDescriptor statDesc = (StatisticsDescriptor) li.next(); if (!referenceUUID.equals(statDesc.getReferenceID())) continue; if (statDesc.getColumnCount() != numKeys) continue; return statDesc.getStatistic().selectivity((Object[])null); }
          Show
          Mamta A. Satoor added a comment - FromBaseTable.java has following code to change the selectivity if statistics exist for a conglomerate but since we do not collect statistics for single-column unique index anymore, following if condition will never be true for single-column unique index and hence we will not consider that index even if it is a faster plan) if (seenFirstColumn && statisticsForConglomerate && (startStopPredCount > 0)) { statStartStopSelectivity = tableDescriptor.selectivityForConglomerate(cd, startStopPredCount); } In my attached patch, I am changing the code above to as follows if (seenFirstColumn && (startStopPredCount > 0)) { if (statisticsForConglomerate) statStartStopSelectivity = tableDescriptor.selectivityForConglomerate(cd, startStopPredCount); else { if (cd.isIndex()) { //DERBY-3790 (Investigate if request for update // statistics can be skipped for certain kind of // indexes, one instance may be unique indexes based // on one column.) But as found in DERBY-6045 (in list // multi-probe by primary key not chosen on tables with // >256 rows), even though we do not keep the // statistics for single-column unique indexes, we // should improve the selectivity of such an index // when the index is being considered by the optimizer. IndexRowGenerator irg = null; irg = cd.getIndexDescriptor(); if (irg.isUnique() && irg.numberOfOrderedColumns() == 1 && startStopPredCount == 1) statStartStopSelectivity = (double)(1/(double)baseRowCount()); } } } With the new code, if we come across a conglomerate with no available statistics, then we check if 1)we are dealing with an index and 2)if it is single-column unique index and 3)there is one start-stop predicate, then go ahead and set the start-stop selectivity to 1/number of rows in the table. I changed the InListMultiProbeTest.java by removing x in fron of the 2 fixtures xtestDerby6045 and xtestDerby6045InsertAllRowsAdditionalUniqueIndex and both those fixtures ran fine with my patch. I will appreciate another pair of eyes on the code rework, especially, the check for "there is one start-stop predicate" in the if clause. The reason I think we need this check is based on the existing code that we have in TableDescriptor for indexes with statistics public double selectivityForConglomerate(ConglomerateDescriptor cd, int numKeys) throws StandardException { UUID referenceUUID = cd.getUUID(); List sdl = getStatistics(); for (Iterator li = sdl.iterator(); li.hasNext(); ) { StatisticsDescriptor statDesc = (StatisticsDescriptor) li.next(); if (!referenceUUID.equals(statDesc.getReferenceID())) continue; if (statDesc.getColumnCount() != numKeys) continue; return statDesc.getStatistic().selectivity((Object[])null); }
          Hide
          Mamta A. Satoor added a comment -

          After debugging the code, I have found that in o.a.d.impl.sql.compile.FromBaseTable, while estimating the cost for the given conglomerate and predicate list, in the method below,
          public CostEstimate estimateCost(OptimizablePredicateList predList,
          ConglomerateDescriptor cd,
          CostEstimate outerCost,
          Optimizer optimizer,
          RowOrdering rowOrdering)
          throws StandardException
          we do not find any statistics for one-column unique index below
          statisticsForConglomerate = tableDescriptor.statisticsExist(cd);
          This is because as part of DERBY-3790, we are not keeping the stats for one-column unique index any more. Later on in the same method, estimateCost, based on the existence of statistics and two other conditions, we change the selectivity value to be more favorable for the one-column unique index
          if (seenFirstColumn && statisticsForConglomerate &&
          (startStopPredCount > 0))

          but since the statistics don't exist for one-column unique indexanymore, we do not improve the selectivity in trunk anymore. I will look into fixing this to recognize one-column unique index.

          Show
          Mamta A. Satoor added a comment - After debugging the code, I have found that in o.a.d.impl.sql.compile.FromBaseTable, while estimating the cost for the given conglomerate and predicate list, in the method below, public CostEstimate estimateCost(OptimizablePredicateList predList, ConglomerateDescriptor cd, CostEstimate outerCost, Optimizer optimizer, RowOrdering rowOrdering) throws StandardException we do not find any statistics for one-column unique index below statisticsForConglomerate = tableDescriptor.statisticsExist(cd); This is because as part of DERBY-3790 , we are not keeping the stats for one-column unique index any more. Later on in the same method, estimateCost, based on the existence of statistics and two other conditions, we change the selectivity value to be more favorable for the one-column unique index if (seenFirstColumn && statisticsForConglomerate && (startStopPredCount > 0)) but since the statistics don't exist for one-column unique indexanymore, we do not improve the selectivity in trunk anymore. I will look into fixing this to recognize one-column unique index.
          Hide
          Mamta A. Satoor added a comment -

          I reran the query above with update statistics before running the query on both trunk and 10.8 to see what the optimizer trace looks like and indeed is different for the two codelines. I have attached the optimizer trace for the 2 codelines(optimizerTraceOutputWithUpdateStatsFor10_8.txt and optimizerTraceOutputWithUpdateStatsForTrunk.txt)
          java -Dij.exceptionTrace=true -Dderby.language.logQueryPlan=true org.apache.derby.tools.ij
          connect 'jdbc:derby:db1';
          create procedure trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.trace';
          create procedure print_trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.printTrace';
          call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null);
          call trace();
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24);
          call print_trace();

          Show
          Mamta A. Satoor added a comment - I reran the query above with update statistics before running the query on both trunk and 10.8 to see what the optimizer trace looks like and indeed is different for the two codelines. I have attached the optimizer trace for the 2 codelines(optimizerTraceOutputWithUpdateStatsFor10_8.txt and optimizerTraceOutputWithUpdateStatsForTrunk.txt) java -Dij.exceptionTrace=true -Dderby.language.logQueryPlan=true org.apache.derby.tools.ij connect 'jdbc:derby:db1'; create procedure trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.trace'; create procedure print_trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.printTrace'; call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null); call trace(); SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24); call print_trace();
          Hide
          Mamta A. Satoor added a comment -

          I was curious to see what query plans are considered by trunk and 10.8(with the selectivity details) to see if I can figure out what is different between the two codelines which requires us to use table scan in trunk and index scan on 10.8. This is for the test case provided in comment 18/Jan/13 22:53 (but I tried only with 25 rows. With 25 rows is when I see tunk using table scan where as 10.8 using index scan).

          To get the query plans, I followed Knut's suggestion in another email by defining following 2 stored procedures
          public static void trace()

          { org.apache.derby.iapi.db.OptimizerTrace.setOptimizerTrace(true); }

          public static void printTrace()

          { System.out.println(org.apache.derby.iapi.db.OptimizerTrace.getOptimizerTraceOutput()); }

          And then I ran the query in question in ij as follows
          java -Dij.exceptionTrace=true -Dderby.language.logQueryPlan=true org.apache.derby.tools.ij
          connect 'jdbc:derby:db1';
          create procedure trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.trace';
          create procedure print_trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.printTrace';
          call trace();
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24);
          call print_trace();

          With both 10.8 and trunk(as shown in the attached email), I see coming up with the same numbers for different query plans considered by optimizer and hence I am not sure what the difference might be. I will look in the actual code to see if I can figure something out. Thanks

          Show
          Mamta A. Satoor added a comment - I was curious to see what query plans are considered by trunk and 10.8(with the selectivity details) to see if I can figure out what is different between the two codelines which requires us to use table scan in trunk and index scan on 10.8. This is for the test case provided in comment 18/Jan/13 22:53 (but I tried only with 25 rows. With 25 rows is when I see tunk using table scan where as 10.8 using index scan). To get the query plans, I followed Knut's suggestion in another email by defining following 2 stored procedures public static void trace() { org.apache.derby.iapi.db.OptimizerTrace.setOptimizerTrace(true); } public static void printTrace() { System.out.println(org.apache.derby.iapi.db.OptimizerTrace.getOptimizerTraceOutput()); } And then I ran the query in question in ij as follows java -Dij.exceptionTrace=true -Dderby.language.logQueryPlan=true org.apache.derby.tools.ij connect 'jdbc:derby:db1'; create procedure trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.trace'; create procedure print_trace() language java parameter style java external name 'org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC.printTrace'; call trace(); SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24); call print_trace(); With both 10.8 and trunk(as shown in the attached email), I see coming up with the same numbers for different query plans considered by optimizer and hence I am not sure what the difference might be. I will look in the actual code to see if I can figure something out. Thanks
          Hide
          Mamta A. Satoor added a comment -

          Hi Tony, I am talking about the very first comment in this jira by you. There you have 1 primary key index and one unique index on the table. I will copy part of that code from 18th Jan for reference here
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID >= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check CHECK ((var_type >= 0) AND (var_type <= 4));\n");

          I have created a small JDBC program which I have in my previous comment. Maybe you can give that a try and see what plan gets used with and without the additional unique index.

          Show
          Mamta A. Satoor added a comment - Hi Tony, I am talking about the very first comment in this jira by you. There you have 1 primary key index and one unique index on the table. I will copy part of that code from 18th Jan for reference here buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID >= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check CHECK ((var_type >= 0) AND (var_type <= 4));\n"); I have created a small JDBC program which I have in my previous comment. Maybe you can give that a try and see what plan gets used with and without the additional unique index.
          Hide
          Mamta A. Satoor added a comment - - edited

          I am copying the JDBC program which will show that on trunk, SELECT query with OR clause does not use index scan if there is following constraint defined on the table(haven't tried 10.9 codeline because my 10.9 client has pending work from other jira). 10.8 and 10.7 codelines continue to use index scan even with the following constraint defined on the table.
          s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)");

          In trunk, if I comment out the code for creating kb_variable_term_variable_name_unique, the SELECT query with OR clause starts using index scan. NOTE that table has 10K rows in it. To run the program, do following and then look at derby.log for the query plans for the SELECT queries
          java -Dderby.language.logQueryPlan=true MamtaJDBC

          public class MamtaJDBC {
          public static void main(String[] args) {
          try {
          Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
          Connection conn = DriverManager
          .getConnection("jdbc:derby:db1;create=true");
          Statement s = conn.createStatement();
          s.execute("CREATE TABLE VARIABLE_TERM (" +
          " term_id int NOT NULL," +
          " var_name VARCHAR(1024) NOT NULL,"+
          " var_type SMALLINT NOT NULL, "+
          " kb_status INTEGER NOT NULL )");
          s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id)");
          s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)");
          int count = 10000;

          for (int i = 0; i < count; i++)

          { s.execute("INSERT INTO VARIABLE_TERM VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4)+",1)"); }


          s.execute("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 30");
          s.execute("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24)");
          } catch (SQLException se) {
          while (se != null)

          { System.out.println("SQLState=" + se.getSQLState() + se.getMessage()); se.printStackTrace(); se = se.getNextException(); }


          } catch (Exception ex)

          { System.out.println("exception " + ex.getMessage()); }

          finally

          { System.exit(0); }


          }
          }

          Show
          Mamta A. Satoor added a comment - - edited I am copying the JDBC program which will show that on trunk, SELECT query with OR clause does not use index scan if there is following constraint defined on the table(haven't tried 10.9 codeline because my 10.9 client has pending work from other jira). 10.8 and 10.7 codelines continue to use index scan even with the following constraint defined on the table. s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)"); In trunk, if I comment out the code for creating kb_variable_term_variable_name_unique, the SELECT query with OR clause starts using index scan. NOTE that table has 10K rows in it. To run the program, do following and then look at derby.log for the query plans for the SELECT queries java -Dderby.language.logQueryPlan=true MamtaJDBC public class MamtaJDBC { public static void main(String[] args) { try { Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); Connection conn = DriverManager .getConnection("jdbc:derby:db1;create=true"); Statement s = conn.createStatement(); s.execute("CREATE TABLE VARIABLE_TERM (" + " term_id int NOT NULL," + " var_name VARCHAR(1024) NOT NULL,"+ " var_type SMALLINT NOT NULL, "+ " kb_status INTEGER NOT NULL )"); s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id)"); s.execute("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)"); int count = 10000; for (int i = 0; i < count; i++) { s.execute("INSERT INTO VARIABLE_TERM VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4)+",1)"); } s.execute("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 30"); s.execute("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1) OR (TERM_ID =39) OR (TERM_ID = 24)"); } catch (SQLException se) { while (se != null) { System.out.println("SQLState=" + se.getSQLState() + se.getMessage()); se.printStackTrace(); se = se.getNextException(); } } catch (Exception ex) { System.out.println("exception " + ex.getMessage()); } finally { System.exit(0); } } }
          Hide
          Tony Brusseau added a comment - - edited

          ****
          (BTW, you probably meant 18th Jan rather than 8th Jan)
          ****
          Yes, you are absolutely correct, I meant 18/Jan/13 22:53.

          ***************
          I worked on the test case provided by you on 18/Jan/13 22:53 and found that if I remove the 2nd unique index(copying below for reference) from the table, we start using index scan with 10K rows. I plan to add a junit test for this case to trunk. That test will be named such that it won't get run for now until we understand the impact of the 2nd unique index on the selection of scan for 10K rows.
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n");
          ***************
          I'm confused about this, the comment from 18/Jan/13 22:53 only has 1 primary key index and no unique indexes. It also uses regular integers and not bigints.

          I'm definitely seeing slow index scans on the latest release even with the simplified table in that comment. Are you seeing a difference between the latest trunk and the last release when running this? If so, are you possibly running with derby.storage.indexStats.debug.keepDisposableStats=true which would change the results?

          Show
          Tony Brusseau added a comment - - edited **** (BTW, you probably meant 18th Jan rather than 8th Jan) **** Yes, you are absolutely correct, I meant 18/Jan/13 22:53. *************** I worked on the test case provided by you on 18/Jan/13 22:53 and found that if I remove the 2nd unique index(copying below for reference) from the table, we start using index scan with 10K rows. I plan to add a junit test for this case to trunk. That test will be named such that it won't get run for now until we understand the impact of the 2nd unique index on the selection of scan for 10K rows. buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n"); *************** I'm confused about this, the comment from 18/Jan/13 22:53 only has 1 primary key index and no unique indexes. It also uses regular integers and not bigints. I'm definitely seeing slow index scans on the latest release even with the simplified table in that comment. Are you seeing a difference between the latest trunk and the last release when running this? If so, are you possibly running with derby.storage.indexStats.debug.keepDisposableStats=true which would change the results?
          Hide
          Mamta A. Satoor added a comment -

          Committed the the test case in trunk with revision 1450363.

          Show
          Mamta A. Satoor added a comment - Committed the the test case in trunk with revision 1450363.
          Hide
          Mamta A. Satoor added a comment -

          Forgot to mention in the earlier comment that the primary key column doesn't have to be BIGINT. I saw during my testing that even when term_id is declared to be INTEGER NOT NULL, we use table scan for 10K rows if there is following additional unique index defined on the table(along with term_id being the primary key)
          ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)

          Show
          Mamta A. Satoor added a comment - Forgot to mention in the earlier comment that the primary key column doesn't have to be BIGINT. I saw during my testing that even when term_id is declared to be INTEGER NOT NULL, we use table scan for 10K rows if there is following additional unique index defined on the table(along with term_id being the primary key) ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type)
          Hide
          Mamta A. Satoor added a comment -

          Hi Tony, regarding your following comment(BTW, you probably meant 18th Jan rather than 8th Jan)
          ****************************************************
          I'm still seeing full heap scans even with 10k rows. I used the same table as in my comment on: 8/Jan/13 22:53, just with more rows.
          ****************************************************

          I worked on the test case provided by you on 18/Jan/13 22:53 and found that if I remove the 2nd unique index(copying below for reference) from the table, we start using index scan with 10K rows. I plan to add a junit test for this case to trunk. That test will be named such that it won't get run for now until we understand the impact of the 2nd unique index on the selection of scan for 10K rows.
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n");

          Show
          Mamta A. Satoor added a comment - Hi Tony, regarding your following comment(BTW, you probably meant 18th Jan rather than 8th Jan) **************************************************** I'm still seeing full heap scans even with 10k rows. I used the same table as in my comment on: 8/Jan/13 22:53, just with more rows. **************************************************** I worked on the test case provided by you on 18/Jan/13 22:53 and found that if I remove the 2nd unique index(copying below for reference) from the table, we start using index scan with 10K rows. I plan to add a junit test for this case to trunk. That test will be named such that it won't get run for now until we understand the impact of the 2nd unique index on the selection of scan for 10K rows. buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n");
          Hide
          Kristian Waagan added a comment -

          Ticking work-around field and lowering the priority.
          With Seen in production, Regression and Performance ticked the release manager should be able to notice this issue

          I agree this is something that must be fixed, and it should be fixed before 10.10 goes out.
          It can be done in two ways:
          a) change the optimizer behavior
          b) start generating statistics for single-column indexes again

          I'd prefer (a), but realize that's the more difficult approach.
          I know the community has been looking into some other optimizer issues lately, maybe we can take advantage of that to ease the investigation of this issue.

          Show
          Kristian Waagan added a comment - Ticking work-around field and lowering the priority. With Seen in production, Regression and Performance ticked the release manager should be able to notice this issue I agree this is something that must be fixed, and it should be fixed before 10.10 goes out. It can be done in two ways: a) change the optimizer behavior b) start generating statistics for single-column indexes again I'd prefer (a), but realize that's the more difficult approach. I know the community has been looking into some other optimizer issues lately, maybe we can take advantage of that to ease the investigation of this issue.
          Hide
          Tony Brusseau added a comment -

          Feel free to lower the priority since there is a work-around. However, I still feel like this is a very important issue. People shouldn't have to add special configurations to Derby in order for blatantly obvious indexing to take place.

          Show
          Tony Brusseau added a comment - Feel free to lower the priority since there is a work-around. However, I still feel like this is a very important issue. People shouldn't have to add special configurations to Derby in order for blatantly obvious indexing to take place.
          Hide
          Tony Brusseau added a comment - - edited

          I'm still seeing full heap scans even with 10k rows. I used the same table as in my comment on: 18/Jan/13 22:53, just with more rows.

          However, if I run derby with:
          derby.storage.indexStats.debug.keepDisposableStats=true
          and then explicitly update statistics, with CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null);
          , then I start seeing index scans on the 10k table. I imagine this will work as a temporary work-around on the 11m row table, although I haven't tried it yet.

          Show
          Tony Brusseau added a comment - - edited I'm still seeing full heap scans even with 10k rows. I used the same table as in my comment on: 18/Jan/13 22:53, just with more rows. However, if I run derby with: derby.storage.indexStats.debug.keepDisposableStats=true and then explicitly update statistics, with CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null); , then I start seeing index scans on the 10k table. I imagine this will work as a temporary work-around on the 11m row table, although I haven't tried it yet.
          Hide
          Mike Matrigali added a comment -

          Tony, at least for the int test that mamta has added we are seeing index scan used for 10k rows. I am worried now that our test cases may not
          be reflecting the actual problem you are seeing in your real environment of millions of rows. Can you verify in your test case if there are a large
          number of rows for the int case you are seeing the bug? Some interesting values for "large" are those that cause there to be multiple pages in the
          base table. In your case you have 32k pages and your test data looks like it is something like ~20 bytes per row (just a guess from the datatypes
          and that varchar only takes up the necessary room). I just pulled 10k out a hat for mamta's test.

          Also can you verify that update statistics has been run on your big table? I think it is a bug, but it looks like the existing behavior in all code lines
          is that you need to run update statistics for the optimizer to pick index scan in this case, even if the interesting index is unique.

          I always worry about optimizer cost bugs with test
          cases involving a really small number of rows, as the bugs uncovered there may not apply to the large number of row case. I think what mamta
          is seeing now is, in all cases run update statistics after data is loaded and before queries are executed:

          10.9 - 10 rows index scan, 24 rows table scan , 10k rows index scan
          10.8 through 10.3 - 10 rows index scan, 24 rows table scan, 10k rows test not tried, but assume it will pick index scan

          At the low number of rows end if you just look at costs it may make perfect sense from just a cycle cost perspective to choose a table scan
          vs. using index. The reason for this is that I/O cost dominates over the cost to scan every row on a page. So in the case where the base
          table has 1 page and the index has 1 page, pure costing will often choose to scan the 1 data page rather than pay 1 i/o to get index page and
          then another i/o to get data page. For other reasons (mostly to not have to lock all the rows you see during a table scan) the derby code has been
          changed to fudge the costs a little to get index scans chosen
          in this case, most notably a single probe query using with an exact key match using an index. These fixes tend to aplly only to low number of
          rows anomaly's, and not to the real life issues like your 11 million row case.

          Show
          Mike Matrigali added a comment - Tony, at least for the int test that mamta has added we are seeing index scan used for 10k rows. I am worried now that our test cases may not be reflecting the actual problem you are seeing in your real environment of millions of rows. Can you verify in your test case if there are a large number of rows for the int case you are seeing the bug? Some interesting values for "large" are those that cause there to be multiple pages in the base table. In your case you have 32k pages and your test data looks like it is something like ~20 bytes per row (just a guess from the datatypes and that varchar only takes up the necessary room). I just pulled 10k out a hat for mamta's test. Also can you verify that update statistics has been run on your big table? I think it is a bug, but it looks like the existing behavior in all code lines is that you need to run update statistics for the optimizer to pick index scan in this case, even if the interesting index is unique. I always worry about optimizer cost bugs with test cases involving a really small number of rows, as the bugs uncovered there may not apply to the large number of row case. I think what mamta is seeing now is, in all cases run update statistics after data is loaded and before queries are executed: 10.9 - 10 rows index scan, 24 rows table scan , 10k rows index scan 10.8 through 10.3 - 10 rows index scan, 24 rows table scan, 10k rows test not tried, but assume it will pick index scan At the low number of rows end if you just look at costs it may make perfect sense from just a cycle cost perspective to choose a table scan vs. using index. The reason for this is that I/O cost dominates over the cost to scan every row on a page. So in the case where the base table has 1 page and the index has 1 page, pure costing will often choose to scan the 1 data page rather than pay 1 i/o to get index page and then another i/o to get data page. For other reasons (mostly to not have to lock all the rows you see during a table scan) the derby code has been changed to fudge the costs a little to get index scans chosen in this case, most notably a single probe query using with an exact key match using an index. These fixes tend to aplly only to low number of rows anomaly's, and not to the real life issues like your 11 million row case.
          Hide
          Mamta A. Satoor added a comment -

          BTW, I found that on trunk, we do use index scan with 10K rows in table. So, for 10rows and 10K rows, we use index scan but for 24rows, we use table scan. These three test scenarios are in the junit test org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest:xtestDerby6045

          Show
          Mamta A. Satoor added a comment - BTW, I found that on trunk, we do use index scan with 10K rows in table. So, for 10rows and 10K rows, we use index scan but for 24rows, we use table scan. These three test scenarios are in the junit test org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest:xtestDerby6045
          Hide
          Mamta A. Satoor added a comment -

          Kristian, thanks for your suggestion about the property(derby.storage.indexStats.debug.keepDisposableStats=true) to revert back to old behavior. I tried that property on trunk with the experiment I tried on the trunk(on 25/Jan/13), and I see that now we pick up index scan both in case of 10rows and 24rows in the table. Like you said, I ran update statistics before running the SELECT statements. The number of rows in sys.systatistics is 0 before calling update statistics. After the call to update statistics, one row gets added(when the derby.storage.indexStats.debug.keepDisposableStats=true property is used) as shown below
          ij> select * from sys.sysstatistics;
          STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS
          --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          6839c016-013c-da22-2265-00000011dd90|560740aa-013c-da21-c463-000000124308|ed8940a7-013c-da21-c463-000000124308|2013-02-14 11:15:44.381 |I|true |1 |numunique= 25 &

          Without the property derby.storage.indexStats.debug.keepDisposableStats=true on trunk, the number of rows remain 0 in sys.systatistics before and after the update statisitcs call and that is the case when we use table scan rather than index scan for 24rows in the table.

          Show
          Mamta A. Satoor added a comment - Kristian, thanks for your suggestion about the property(derby.storage.indexStats.debug.keepDisposableStats=true) to revert back to old behavior. I tried that property on trunk with the experiment I tried on the trunk(on 25/Jan/13), and I see that now we pick up index scan both in case of 10rows and 24rows in the table. Like you said, I ran update statistics before running the SELECT statements. The number of rows in sys.systatistics is 0 before calling update statistics. After the call to update statistics, one row gets added(when the derby.storage.indexStats.debug.keepDisposableStats=true property is used) as shown below ij> select * from sys.sysstatistics; STATID |REFERENCEID |TABLEID |CREATIONTIMESTAMP |&|VALID|COLCOUNT |STATISTICS -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6839c016-013c-da22-2265-00000011dd90|560740aa-013c-da21-c463-000000124308|ed8940a7-013c-da21-c463-000000124308|2013-02-14 11:15:44.381 |I|true |1 |numunique= 25 & Without the property derby.storage.indexStats.debug.keepDisposableStats=true on trunk, the number of rows remain 0 in sys.systatistics before and after the update statisitcs call and that is the case when we use table scan rather than index scan for 24rows in the table.
          Hide
          Kristian Waagan added a comment -

          I think specifying derby.storage.indexStats.debug.keepDisposableStats=true and regenerating statistics for the relevant tables will suffice as a work-around for this issue.

          Given that we agree the statistics for single-column unique indexes aren't needed, do we know what puts off the optimizer?
          The lack of statistics, or stale row estimates?
          (I don't have time to dig into that, and I don't know what the optimizer does in this case.)

          Show
          Kristian Waagan added a comment - I think specifying derby.storage.indexStats.debug.keepDisposableStats=true and regenerating statistics for the relevant tables will suffice as a work-around for this issue. Given that we agree the statistics for single-column unique indexes aren't needed, do we know what puts off the optimizer? The lack of statistics, or stale row estimates? (I don't have time to dig into that, and I don't know what the optimizer does in this case.)
          Hide
          Mamta A. Satoor added a comment -

          I ran the new junit test org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest:xtestDerby6045 on my clients for trunk and all the codelines upto 10.3 release. I find that the test fails on trunk and 10.9 but passes on 10.8 through 10.3 codelines.

          What I found though is that in 10.8 through 10.3 codelines, if I do not have
          s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
          (actually 10.4 and 10.3 codelines do not have update statistics procedure. On those 2 codelines, I used compress table procedure to re-generate the statistics, s.execute("call syscs_util.syscs_compress_table('APP','VARIABLE_TERM',1)")
          after inserting total of 24 rows in the table, then we do not pick up index scan.

          I am copying following from the junit test for reference
          //Add 14 more rows
          for (int i=11; i<=25; i++)

          { ps.setInt(1, i); ps.executeUpdate(); }

          s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
          //Need to execute the query with an extra white space so that the
          // queries will get recompiled. If the following queries looked
          // exactly like the earlier queries in this fixture, we would
          // end up using the earloer query plan rather than creating a
          // new query plan which is going recognize the additional rows.
          runThreeQueries(1);
          If I take out update statistics call, following 2 queries(which are run inside runThreeQueries method) end up using table scan rather than index scan on 10.8 through 10.3 codelines
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31)
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31))

          trunk and 10.9 codelines use table scan no matter whether we run update statistics or not. This appears to be because of checkin r1341481 that went in for DERBY-3790 into 10.9 and trunk.

          Show
          Mamta A. Satoor added a comment - I ran the new junit test org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest:xtestDerby6045 on my clients for trunk and all the codelines upto 10.3 release. I find that the test fails on trunk and 10.9 but passes on 10.8 through 10.3 codelines. What I found though is that in 10.8 through 10.3 codelines, if I do not have s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)"); (actually 10.4 and 10.3 codelines do not have update statistics procedure. On those 2 codelines, I used compress table procedure to re-generate the statistics, s.execute("call syscs_util.syscs_compress_table('APP','VARIABLE_TERM',1)") after inserting total of 24 rows in the table, then we do not pick up index scan. I am copying following from the junit test for reference //Add 14 more rows for (int i=11; i<=25; i++) { ps.setInt(1, i); ps.executeUpdate(); } s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)"); //Need to execute the query with an extra white space so that the // queries will get recompiled. If the following queries looked // exactly like the earlier queries in this fixture, we would // end up using the earloer query plan rather than creating a // new query plan which is going recognize the additional rows. runThreeQueries(1); If I take out update statistics call, following 2 queries(which are run inside runThreeQueries method) end up using table scan rather than index scan on 10.8 through 10.3 codelines SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31) SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31)) trunk and 10.9 codelines use table scan no matter whether we run update statistics or not. This appears to be because of checkin r1341481 that went in for DERBY-3790 into 10.9 and trunk.
          Hide
          Mamta A. Satoor added a comment -

          Made some changes to the junit test and committed it into trunk with revision 1446048 with following commit comments


                                                • DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)

          Added a new test scenario where we add about 10K rows into a table with primary key and check if we are still using index scan for the queries being tested

          Additionally, cleaned up the test a little bit.

          Show
          Mamta A. Satoor added a comment - Made some changes to the junit test and committed it into trunk with revision 1446048 with following commit comments DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows) Added a new test scenario where we add about 10K rows into a table with primary key and check if we are still using index scan for the queries being tested Additionally, cleaned up the test a little bit.
          Hide
          Mamta A. Satoor added a comment -

          I have aded a junit test which won't run at this point since it starts with name xtest... rather than test... Once this jira is fixed, we should enable the test. The revision number for the checkin is 1445030 and the commit comments are
          ********************
          DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)

          Adding a junit test but it will not run since the the fixture name is not starting with "test"... We should remove the x in front of test fixture's name once DERBY-6045 has been fixed
          ********************

          Show
          Mamta A. Satoor added a comment - I have aded a junit test which won't run at this point since it starts with name xtest... rather than test... Once this jira is fixed, we should enable the test. The revision number for the checkin is 1445030 and the commit comments are ******************** DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows) Adding a junit test but it will not run since the the fixture name is not starting with "test"... We should remove the x in front of test fixture's name once DERBY-6045 has been fixed ********************
          Hide
          Mike Matrigali added a comment -

          since DERBY-6045 has to do with background statistics, it would be good to see if the problem existed in releases before automatic statistics.

          Also might be interesting to run test with automatic statistics disabled, in current system.

          DERBY-6045 was about not creating cardinality stats for unique one key indexes, which really should not be useful to the optimizer. Could see
          if running update statistics by hand before the query helps or not?

          Should see if the system did the right thing pre-auto statistics
          if table is first created with index and then loaded (so should mean no statistics),

          Show
          Mike Matrigali added a comment - since DERBY-6045 has to do with background statistics, it would be good to see if the problem existed in releases before automatic statistics. Also might be interesting to run test with automatic statistics disabled, in current system. DERBY-6045 was about not creating cardinality stats for unique one key indexes, which really should not be useful to the optimizer. Could see if running update statistics by hand before the query helps or not? Should see if the system did the right thing pre-auto statistics if table is first created with index and then loaded (so should mean no statistics),
          Hide
          Mamta A. Satoor added a comment -

          Checkin for DERBY-3790 may have caused the regression tracked under DERBY-6045

          Show
          Mamta A. Satoor added a comment - Checkin for DERBY-3790 may have caused the regression tracked under DERBY-6045
          Hide
          Mamta A. Satoor added a comment -

          I went through various checkins that went into 10.9 and found the checkin that has caused the regression to be r1341481. This checkin was made for DERBY-3790(Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column). When I try the test scenario from comment on 25/Jan/13 on codeline synced upto r1341481, I see us using table scans but if I sync up the client to the immediate earlier checkin 1341350, I see us using index scan. I will work on writing a junit test for test scenario that I have been trying.

          Show
          Mamta A. Satoor added a comment - I went through various checkins that went into 10.9 and found the checkin that has caused the regression to be r1341481. This checkin was made for DERBY-3790 (Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column). When I try the test scenario from comment on 25/Jan/13 on codeline synced upto r1341481, I see us using table scans but if I sync up the client to the immediate earlier checkin 1341350, I see us using index scan. I will work on writing a junit test for test scenario that I have been trying.
          Hide
          Tony Brusseau added a comment -

          I have another similar case that I believe might be related. If I have a disjunction in a delete statement it appears to use table scans:

          This takes 10.5 min on a very small DB to run the following command 17,200 times:
          DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?)

          However, if I break the delete statement into 2 separate calls, then the total time is only 17 secs:
          DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = ?)
          DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (mt = ?)

          The table has about 10k rows.

          Table looks like:
          DROP TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS;

          CREATE TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS
          (
          mt BIGINT NOT NULL,
          term BIGINT NOT NULL,
          term_index INTEGER NOT NULL,
          usage_count BIGINT NOT NULL
          );

          ALTER TABLE kb.mt_gaf_top_level_term_counts ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (mt, term, term_index);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON kb.mt_gaf_top_level_term_counts(mt);
          CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON kb.mt_gaf_top_level_term_counts(term);

          Show
          Tony Brusseau added a comment - I have another similar case that I believe might be related. If I have a disjunction in a delete statement it appears to use table scans: This takes 10.5 min on a very small DB to run the following command 17,200 times: DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?) However, if I break the delete statement into 2 separate calls, then the total time is only 17 secs: DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (term = ?) DELETE FROM kb.mt_gaf_top_level_term_counts WHERE (mt = ?) The table has about 10k rows. Table looks like: DROP TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS; CREATE TABLE KB.MT_GAF_TOP_LEVEL_TERM_COUNTS ( mt BIGINT NOT NULL, term BIGINT NOT NULL, term_index INTEGER NOT NULL, usage_count BIGINT NOT NULL ); ALTER TABLE kb.mt_gaf_top_level_term_counts ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk PRIMARY KEY (mt, term, term_index); CREATE INDEX kb_mt_gaf_top_level_term_counts_mt_index ON kb.mt_gaf_top_level_term_counts(mt); CREATE INDEX kb_mt_gaf_top_level_term_counts_term_index ON kb.mt_gaf_top_level_term_counts(term);
          Hide
          Mamta A. Satoor added a comment -

          The test scenario from comment on 25/Jan/13 show incorrect table scan usage on trunk and 10.9 releases. The same test behaves correctly (ie uses index scan) on all 3 releases of 10.8 codeline - 10.8.1.1, 10.8.1.2 and 10.8.3.0, The top of the 10.8 codeline also works correctly. So something has changed between 10.8 and 10.9 release apparently which has changed the behavior.

          Show
          Mamta A. Satoor added a comment - The test scenario from comment on 25/Jan/13 show incorrect table scan usage on trunk and 10.9 releases. The same test behaves correctly (ie uses index scan) on all 3 releases of 10.8 codeline - 10.8.1.1, 10.8.1.2 and 10.8.3.0, The top of the 10.8 codeline also works correctly. So something has changed between 10.8 and 10.9 release apparently which has changed the behavior.
          Hide
          Mamta A. Satoor added a comment -

          BTW, I tried the little experiment I tried on the trunk(on 25/Jan/13) on 10.9.1.0 release and saw the same behavior as trunk. But on 10.8.1.1 and 10.8.1.2, I saw that even after 25 rows, we still use index scan rather than switching to table scan. So something has changed between 10.8 and 10.9 release apparently which has changed the behavior.

          Show
          Mamta A. Satoor added a comment - BTW, I tried the little experiment I tried on the trunk(on 25/Jan/13) on 10.9.1.0 release and saw the same behavior as trunk. But on 10.8.1.1 and 10.8.1.2, I saw that even after 25 rows, we still use index scan rather than switching to table scan. So something has changed between 10.8 and 10.9 release apparently which has changed the behavior.
          Hide
          Tony Brusseau added a comment - - edited

          Thanks for confirming that Mamta. I get exactly the same behavior except for the breakpoints at which things switch from multiprobe to table scan which I believe is due to relative page size differences. My DB used 32k pages and the default is 4k (a factor of 8 difference). If you take your breakpoint of 25 and multiply by 8 you get 200 which is roughly around the breakpoint that I'm seeing.

          Show
          Tony Brusseau added a comment - - edited Thanks for confirming that Mamta. I get exactly the same behavior except for the breakpoints at which things switch from multiprobe to table scan which I believe is due to relative page size differences. My DB used 32k pages and the default is 4k (a factor of 8 difference). If you take your breakpoint of 25 and multiply by 8 you get 200 which is roughly around the breakpoint that I'm seeing.
          Hide
          Mamta A. Satoor added a comment -

          On the trunk, I tried experimenting with Tony's table with primary key as shown below
          CREATE TABLE VARIABLE_TERM
          (
          term_id INTEGER NOT NULL,
          var_name VARCHAR(1024) NOT NULL,
          var_type SMALLINT NOT NULL,
          kb_status INTEGER NOT NULL
          );

          ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);

          The following queries show different query plan(as pointed by Tony) depending on the number of rows in the variable_term table.
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31);
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31));

          The queries above used index scan with KB_VARIABLE_TERM_TERM_ID_PK when there were 10 rows in the table. But if I had even 25 rows, they started using table scan.

          The following query always used index scan with 10/25/64 rows
          SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11;

          Show
          Mamta A. Satoor added a comment - On the trunk, I tried experimenting with Tony's table with primary key as shown below CREATE TABLE VARIABLE_TERM ( term_id INTEGER NOT NULL, var_name VARCHAR(1024) NOT NULL, var_type SMALLINT NOT NULL, kb_status INTEGER NOT NULL ); ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id); The following queries show different query plan(as pointed by Tony) depending on the number of rows in the variable_term table. SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31); SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31)); The queries above used index scan with KB_VARIABLE_TERM_TERM_ID_PK when there were 10 rows in the table. But if I had even 25 rows, they started using table scan. The following query always used index scan with 10/25/64 rows SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11;
          Hide
          Tony Brusseau added a comment -

          I just tried it with regular integers, starting from 1 and the only indexing being the primary key. Still get the same issue. If I make a table with 64 rows, then it does fast b-tree scans using the IN query. If the table has 256 or more rows, it does slow heap scans over every row in the table when using the IN query. It looks like maybe there is a logic-po in the code. Doing full table scans over small tables might be faster than multi-probing, but instead it is doing it on large tables. This is just a wild guess.

          Here is the SQL:

          DROP TABLE VARIABLE_TERM;

          CREATE TABLE VARIABLE_TERM
          (
          term_id INTEGER NOT NULL,
          var_name VARCHAR(1024) NOT NULL,
          var_type SMALLINT NOT NULL,
          kb_status INTEGER NOT NULL
          );

          ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);

          INSERT INTO VARIABLE_TERM VALUES (1, '?var0', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (2, '?var1', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (3, '?var2', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (4, '?var3', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (5, '?var4', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (6, '?var5', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (7, '?var6', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (8, '?var7', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (9, '?var8', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (10, '?var9', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (11, '?var10', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (12, '?var11', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (13, '?var12', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (14, '?var13', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (15, '?var14', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (16, '?var15', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (17, '?var16', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (18, '?var17', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (19, '?var18', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (20, '?var19', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (21, '?var20', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (22, '?var21', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (23, '?var22', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (24, '?var23', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (25, '?var24', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (26, '?var25', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (27, '?var26', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (28, '?var27', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (29, '?var28', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (30, '?var29', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (31, '?var30', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (32, '?var31', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (33, '?var32', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (34, '?var33', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (35, '?var34', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (36, '?var35', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (37, '?var36', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (38, '?var37', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (39, '?var38', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (40, '?var39', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (41, '?var40', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (42, '?var41', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (43, '?var42', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (44, '?var43', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (45, '?var44', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (46, '?var45', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (47, '?var46', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (48, '?var47', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (49, '?var48', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (50, '?var49', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (51, '?var50', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (52, '?var51', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (53, '?var52', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (54, '?var53', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (55, '?var54', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (56, '?var55', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (57, '?var56', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (58, '?var57', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (59, '?var58', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (60, '?var59', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (61, '?var60', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (62, '?var61', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (63, '?var62', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (64, '?var63', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (65, '?var64', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (66, '?var65', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (67, '?var66', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (68, '?var67', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (69, '?var68', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (70, '?var69', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (71, '?var70', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (72, '?var71', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (73, '?var72', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (74, '?var73', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (75, '?var74', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (76, '?var75', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (77, '?var76', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (78, '?var77', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (79, '?var78', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (80, '?var79', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (81, '?var80', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (82, '?var81', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (83, '?var82', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (84, '?var83', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (85, '?var84', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (86, '?var85', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (87, '?var86', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (88, '?var87', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (89, '?var88', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (90, '?var89', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (91, '?var90', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (92, '?var91', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (93, '?var92', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (94, '?var93', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (95, '?var94', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (96, '?var95', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (97, '?var96', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (98, '?var97', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (99, '?var98', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (100, '?var99', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (101, '?var100', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (102, '?var101', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (103, '?var102', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (104, '?var103', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (105, '?var104', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (106, '?var105', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (107, '?var106', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (108, '?var107', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (109, '?var108', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (110, '?var109', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (111, '?var110', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (112, '?var111', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (113, '?var112', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (114, '?var113', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (115, '?var114', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (116, '?var115', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (117, '?var116', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (118, '?var117', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (119, '?var118', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (120, '?var119', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (121, '?var120', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (122, '?var121', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (123, '?var122', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (124, '?var123', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (125, '?var124', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (126, '?var125', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (127, '?var126', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (128, '?var127', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (129, '?var128', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (130, '?var129', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (131, '?var130', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (132, '?var131', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (133, '?var132', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (134, '?var133', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (135, '?var134', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (136, '?var135', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (137, '?var136', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (138, '?var137', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (139, '?var138', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (140, '?var139', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (141, '?var140', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (142, '?var141', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (143, '?var142', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (144, '?var143', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (145, '?var144', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (146, '?var145', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (147, '?var146', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (148, '?var147', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (149, '?var148', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (150, '?var149', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (151, '?var150', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (152, '?var151', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (153, '?var152', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (154, '?var153', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (155, '?var154', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (156, '?var155', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (157, '?var156', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (158, '?var157', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (159, '?var158', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (160, '?var159', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (161, '?var160', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (162, '?var161', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (163, '?var162', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (164, '?var163', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (165, '?var164', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (166, '?var165', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (167, '?var166', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (168, '?var167', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (169, '?var168', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (170, '?var169', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (171, '?var170', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (172, '?var171', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (173, '?var172', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (174, '?var173', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (175, '?var174', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (176, '?var175', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (177, '?var176', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (178, '?var177', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (179, '?var178', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (180, '?var179', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (181, '?var180', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (182, '?var181', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (183, '?var182', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (184, '?var183', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (185, '?var184', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (186, '?var185', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (187, '?var186', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (188, '?var187', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (189, '?var188', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (190, '?var189', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (191, '?var190', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (192, '?var191', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (193, '?var192', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (194, '?var193', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (195, '?var194', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (196, '?var195', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (197, '?var196', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (198, '?var197', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (199, '?var198', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (200, '?var199', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (201, '?var200', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (202, '?var201', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (203, '?var202', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (204, '?var203', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (205, '?var204', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (206, '?var205', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (207, '?var206', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (208, '?var207', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (209, '?var208', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (210, '?var209', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (211, '?var210', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (212, '?var211', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (213, '?var212', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (214, '?var213', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (215, '?var214', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (216, '?var215', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (217, '?var216', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (218, '?var217', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (219, '?var218', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (220, '?var219', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (221, '?var220', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (222, '?var221', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (223, '?var222', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (224, '?var223', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (225, '?var224', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (226, '?var225', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (227, '?var226', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (228, '?var227', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (229, '?var228', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (230, '?var229', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (231, '?var230', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (232, '?var231', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (233, '?var232', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (234, '?var233', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (235, '?var234', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (236, '?var235', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (237, '?var236', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (238, '?var237', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (239, '?var238', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (240, '?var239', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (241, '?var240', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (242, '?var241', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (243, '?var242', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (244, '?var243', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (245, '?var244', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (246, '?var245', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (247, '?var246', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (248, '?var247', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (249, '?var248', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (250, '?var249', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (251, '?var250', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (252, '?var251', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (253, '?var252', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (254, '?var253', 4, 1);
          INSERT INTO VARIABLE_TERM VALUES (255, '?var254', 1, 1);
          INSERT INTO VARIABLE_TERM VALUES (256, '?var255', 4, 1);

          CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null);

          SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11;
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31);
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31));

          Show
          Tony Brusseau added a comment - I just tried it with regular integers, starting from 1 and the only indexing being the primary key. Still get the same issue. If I make a table with 64 rows, then it does fast b-tree scans using the IN query. If the table has 256 or more rows, it does slow heap scans over every row in the table when using the IN query. It looks like maybe there is a logic-po in the code. Doing full table scans over small tables might be faster than multi-probing, but instead it is doing it on large tables. This is just a wild guess. Here is the SQL: DROP TABLE VARIABLE_TERM; CREATE TABLE VARIABLE_TERM ( term_id INTEGER NOT NULL, var_name VARCHAR(1024) NOT NULL, var_type SMALLINT NOT NULL, kb_status INTEGER NOT NULL ); ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id); INSERT INTO VARIABLE_TERM VALUES (1, '?var0', 1, 1); INSERT INTO VARIABLE_TERM VALUES (2, '?var1', 4, 1); INSERT INTO VARIABLE_TERM VALUES (3, '?var2', 1, 1); INSERT INTO VARIABLE_TERM VALUES (4, '?var3', 4, 1); INSERT INTO VARIABLE_TERM VALUES (5, '?var4', 1, 1); INSERT INTO VARIABLE_TERM VALUES (6, '?var5', 4, 1); INSERT INTO VARIABLE_TERM VALUES (7, '?var6', 1, 1); INSERT INTO VARIABLE_TERM VALUES (8, '?var7', 4, 1); INSERT INTO VARIABLE_TERM VALUES (9, '?var8', 1, 1); INSERT INTO VARIABLE_TERM VALUES (10, '?var9', 4, 1); INSERT INTO VARIABLE_TERM VALUES (11, '?var10', 1, 1); INSERT INTO VARIABLE_TERM VALUES (12, '?var11', 4, 1); INSERT INTO VARIABLE_TERM VALUES (13, '?var12', 1, 1); INSERT INTO VARIABLE_TERM VALUES (14, '?var13', 4, 1); INSERT INTO VARIABLE_TERM VALUES (15, '?var14', 1, 1); INSERT INTO VARIABLE_TERM VALUES (16, '?var15', 4, 1); INSERT INTO VARIABLE_TERM VALUES (17, '?var16', 1, 1); INSERT INTO VARIABLE_TERM VALUES (18, '?var17', 4, 1); INSERT INTO VARIABLE_TERM VALUES (19, '?var18', 1, 1); INSERT INTO VARIABLE_TERM VALUES (20, '?var19', 4, 1); INSERT INTO VARIABLE_TERM VALUES (21, '?var20', 1, 1); INSERT INTO VARIABLE_TERM VALUES (22, '?var21', 4, 1); INSERT INTO VARIABLE_TERM VALUES (23, '?var22', 1, 1); INSERT INTO VARIABLE_TERM VALUES (24, '?var23', 4, 1); INSERT INTO VARIABLE_TERM VALUES (25, '?var24', 1, 1); INSERT INTO VARIABLE_TERM VALUES (26, '?var25', 4, 1); INSERT INTO VARIABLE_TERM VALUES (27, '?var26', 1, 1); INSERT INTO VARIABLE_TERM VALUES (28, '?var27', 4, 1); INSERT INTO VARIABLE_TERM VALUES (29, '?var28', 1, 1); INSERT INTO VARIABLE_TERM VALUES (30, '?var29', 4, 1); INSERT INTO VARIABLE_TERM VALUES (31, '?var30', 1, 1); INSERT INTO VARIABLE_TERM VALUES (32, '?var31', 4, 1); INSERT INTO VARIABLE_TERM VALUES (33, '?var32', 1, 1); INSERT INTO VARIABLE_TERM VALUES (34, '?var33', 4, 1); INSERT INTO VARIABLE_TERM VALUES (35, '?var34', 1, 1); INSERT INTO VARIABLE_TERM VALUES (36, '?var35', 4, 1); INSERT INTO VARIABLE_TERM VALUES (37, '?var36', 1, 1); INSERT INTO VARIABLE_TERM VALUES (38, '?var37', 4, 1); INSERT INTO VARIABLE_TERM VALUES (39, '?var38', 1, 1); INSERT INTO VARIABLE_TERM VALUES (40, '?var39', 4, 1); INSERT INTO VARIABLE_TERM VALUES (41, '?var40', 1, 1); INSERT INTO VARIABLE_TERM VALUES (42, '?var41', 4, 1); INSERT INTO VARIABLE_TERM VALUES (43, '?var42', 1, 1); INSERT INTO VARIABLE_TERM VALUES (44, '?var43', 4, 1); INSERT INTO VARIABLE_TERM VALUES (45, '?var44', 1, 1); INSERT INTO VARIABLE_TERM VALUES (46, '?var45', 4, 1); INSERT INTO VARIABLE_TERM VALUES (47, '?var46', 1, 1); INSERT INTO VARIABLE_TERM VALUES (48, '?var47', 4, 1); INSERT INTO VARIABLE_TERM VALUES (49, '?var48', 1, 1); INSERT INTO VARIABLE_TERM VALUES (50, '?var49', 4, 1); INSERT INTO VARIABLE_TERM VALUES (51, '?var50', 1, 1); INSERT INTO VARIABLE_TERM VALUES (52, '?var51', 4, 1); INSERT INTO VARIABLE_TERM VALUES (53, '?var52', 1, 1); INSERT INTO VARIABLE_TERM VALUES (54, '?var53', 4, 1); INSERT INTO VARIABLE_TERM VALUES (55, '?var54', 1, 1); INSERT INTO VARIABLE_TERM VALUES (56, '?var55', 4, 1); INSERT INTO VARIABLE_TERM VALUES (57, '?var56', 1, 1); INSERT INTO VARIABLE_TERM VALUES (58, '?var57', 4, 1); INSERT INTO VARIABLE_TERM VALUES (59, '?var58', 1, 1); INSERT INTO VARIABLE_TERM VALUES (60, '?var59', 4, 1); INSERT INTO VARIABLE_TERM VALUES (61, '?var60', 1, 1); INSERT INTO VARIABLE_TERM VALUES (62, '?var61', 4, 1); INSERT INTO VARIABLE_TERM VALUES (63, '?var62', 1, 1); INSERT INTO VARIABLE_TERM VALUES (64, '?var63', 4, 1); INSERT INTO VARIABLE_TERM VALUES (65, '?var64', 1, 1); INSERT INTO VARIABLE_TERM VALUES (66, '?var65', 4, 1); INSERT INTO VARIABLE_TERM VALUES (67, '?var66', 1, 1); INSERT INTO VARIABLE_TERM VALUES (68, '?var67', 4, 1); INSERT INTO VARIABLE_TERM VALUES (69, '?var68', 1, 1); INSERT INTO VARIABLE_TERM VALUES (70, '?var69', 4, 1); INSERT INTO VARIABLE_TERM VALUES (71, '?var70', 1, 1); INSERT INTO VARIABLE_TERM VALUES (72, '?var71', 4, 1); INSERT INTO VARIABLE_TERM VALUES (73, '?var72', 1, 1); INSERT INTO VARIABLE_TERM VALUES (74, '?var73', 4, 1); INSERT INTO VARIABLE_TERM VALUES (75, '?var74', 1, 1); INSERT INTO VARIABLE_TERM VALUES (76, '?var75', 4, 1); INSERT INTO VARIABLE_TERM VALUES (77, '?var76', 1, 1); INSERT INTO VARIABLE_TERM VALUES (78, '?var77', 4, 1); INSERT INTO VARIABLE_TERM VALUES (79, '?var78', 1, 1); INSERT INTO VARIABLE_TERM VALUES (80, '?var79', 4, 1); INSERT INTO VARIABLE_TERM VALUES (81, '?var80', 1, 1); INSERT INTO VARIABLE_TERM VALUES (82, '?var81', 4, 1); INSERT INTO VARIABLE_TERM VALUES (83, '?var82', 1, 1); INSERT INTO VARIABLE_TERM VALUES (84, '?var83', 4, 1); INSERT INTO VARIABLE_TERM VALUES (85, '?var84', 1, 1); INSERT INTO VARIABLE_TERM VALUES (86, '?var85', 4, 1); INSERT INTO VARIABLE_TERM VALUES (87, '?var86', 1, 1); INSERT INTO VARIABLE_TERM VALUES (88, '?var87', 4, 1); INSERT INTO VARIABLE_TERM VALUES (89, '?var88', 1, 1); INSERT INTO VARIABLE_TERM VALUES (90, '?var89', 4, 1); INSERT INTO VARIABLE_TERM VALUES (91, '?var90', 1, 1); INSERT INTO VARIABLE_TERM VALUES (92, '?var91', 4, 1); INSERT INTO VARIABLE_TERM VALUES (93, '?var92', 1, 1); INSERT INTO VARIABLE_TERM VALUES (94, '?var93', 4, 1); INSERT INTO VARIABLE_TERM VALUES (95, '?var94', 1, 1); INSERT INTO VARIABLE_TERM VALUES (96, '?var95', 4, 1); INSERT INTO VARIABLE_TERM VALUES (97, '?var96', 1, 1); INSERT INTO VARIABLE_TERM VALUES (98, '?var97', 4, 1); INSERT INTO VARIABLE_TERM VALUES (99, '?var98', 1, 1); INSERT INTO VARIABLE_TERM VALUES (100, '?var99', 4, 1); INSERT INTO VARIABLE_TERM VALUES (101, '?var100', 1, 1); INSERT INTO VARIABLE_TERM VALUES (102, '?var101', 4, 1); INSERT INTO VARIABLE_TERM VALUES (103, '?var102', 1, 1); INSERT INTO VARIABLE_TERM VALUES (104, '?var103', 4, 1); INSERT INTO VARIABLE_TERM VALUES (105, '?var104', 1, 1); INSERT INTO VARIABLE_TERM VALUES (106, '?var105', 4, 1); INSERT INTO VARIABLE_TERM VALUES (107, '?var106', 1, 1); INSERT INTO VARIABLE_TERM VALUES (108, '?var107', 4, 1); INSERT INTO VARIABLE_TERM VALUES (109, '?var108', 1, 1); INSERT INTO VARIABLE_TERM VALUES (110, '?var109', 4, 1); INSERT INTO VARIABLE_TERM VALUES (111, '?var110', 1, 1); INSERT INTO VARIABLE_TERM VALUES (112, '?var111', 4, 1); INSERT INTO VARIABLE_TERM VALUES (113, '?var112', 1, 1); INSERT INTO VARIABLE_TERM VALUES (114, '?var113', 4, 1); INSERT INTO VARIABLE_TERM VALUES (115, '?var114', 1, 1); INSERT INTO VARIABLE_TERM VALUES (116, '?var115', 4, 1); INSERT INTO VARIABLE_TERM VALUES (117, '?var116', 1, 1); INSERT INTO VARIABLE_TERM VALUES (118, '?var117', 4, 1); INSERT INTO VARIABLE_TERM VALUES (119, '?var118', 1, 1); INSERT INTO VARIABLE_TERM VALUES (120, '?var119', 4, 1); INSERT INTO VARIABLE_TERM VALUES (121, '?var120', 1, 1); INSERT INTO VARIABLE_TERM VALUES (122, '?var121', 4, 1); INSERT INTO VARIABLE_TERM VALUES (123, '?var122', 1, 1); INSERT INTO VARIABLE_TERM VALUES (124, '?var123', 4, 1); INSERT INTO VARIABLE_TERM VALUES (125, '?var124', 1, 1); INSERT INTO VARIABLE_TERM VALUES (126, '?var125', 4, 1); INSERT INTO VARIABLE_TERM VALUES (127, '?var126', 1, 1); INSERT INTO VARIABLE_TERM VALUES (128, '?var127', 4, 1); INSERT INTO VARIABLE_TERM VALUES (129, '?var128', 1, 1); INSERT INTO VARIABLE_TERM VALUES (130, '?var129', 4, 1); INSERT INTO VARIABLE_TERM VALUES (131, '?var130', 1, 1); INSERT INTO VARIABLE_TERM VALUES (132, '?var131', 4, 1); INSERT INTO VARIABLE_TERM VALUES (133, '?var132', 1, 1); INSERT INTO VARIABLE_TERM VALUES (134, '?var133', 4, 1); INSERT INTO VARIABLE_TERM VALUES (135, '?var134', 1, 1); INSERT INTO VARIABLE_TERM VALUES (136, '?var135', 4, 1); INSERT INTO VARIABLE_TERM VALUES (137, '?var136', 1, 1); INSERT INTO VARIABLE_TERM VALUES (138, '?var137', 4, 1); INSERT INTO VARIABLE_TERM VALUES (139, '?var138', 1, 1); INSERT INTO VARIABLE_TERM VALUES (140, '?var139', 4, 1); INSERT INTO VARIABLE_TERM VALUES (141, '?var140', 1, 1); INSERT INTO VARIABLE_TERM VALUES (142, '?var141', 4, 1); INSERT INTO VARIABLE_TERM VALUES (143, '?var142', 1, 1); INSERT INTO VARIABLE_TERM VALUES (144, '?var143', 4, 1); INSERT INTO VARIABLE_TERM VALUES (145, '?var144', 1, 1); INSERT INTO VARIABLE_TERM VALUES (146, '?var145', 4, 1); INSERT INTO VARIABLE_TERM VALUES (147, '?var146', 1, 1); INSERT INTO VARIABLE_TERM VALUES (148, '?var147', 4, 1); INSERT INTO VARIABLE_TERM VALUES (149, '?var148', 1, 1); INSERT INTO VARIABLE_TERM VALUES (150, '?var149', 4, 1); INSERT INTO VARIABLE_TERM VALUES (151, '?var150', 1, 1); INSERT INTO VARIABLE_TERM VALUES (152, '?var151', 4, 1); INSERT INTO VARIABLE_TERM VALUES (153, '?var152', 1, 1); INSERT INTO VARIABLE_TERM VALUES (154, '?var153', 4, 1); INSERT INTO VARIABLE_TERM VALUES (155, '?var154', 1, 1); INSERT INTO VARIABLE_TERM VALUES (156, '?var155', 4, 1); INSERT INTO VARIABLE_TERM VALUES (157, '?var156', 1, 1); INSERT INTO VARIABLE_TERM VALUES (158, '?var157', 4, 1); INSERT INTO VARIABLE_TERM VALUES (159, '?var158', 1, 1); INSERT INTO VARIABLE_TERM VALUES (160, '?var159', 4, 1); INSERT INTO VARIABLE_TERM VALUES (161, '?var160', 1, 1); INSERT INTO VARIABLE_TERM VALUES (162, '?var161', 4, 1); INSERT INTO VARIABLE_TERM VALUES (163, '?var162', 1, 1); INSERT INTO VARIABLE_TERM VALUES (164, '?var163', 4, 1); INSERT INTO VARIABLE_TERM VALUES (165, '?var164', 1, 1); INSERT INTO VARIABLE_TERM VALUES (166, '?var165', 4, 1); INSERT INTO VARIABLE_TERM VALUES (167, '?var166', 1, 1); INSERT INTO VARIABLE_TERM VALUES (168, '?var167', 4, 1); INSERT INTO VARIABLE_TERM VALUES (169, '?var168', 1, 1); INSERT INTO VARIABLE_TERM VALUES (170, '?var169', 4, 1); INSERT INTO VARIABLE_TERM VALUES (171, '?var170', 1, 1); INSERT INTO VARIABLE_TERM VALUES (172, '?var171', 4, 1); INSERT INTO VARIABLE_TERM VALUES (173, '?var172', 1, 1); INSERT INTO VARIABLE_TERM VALUES (174, '?var173', 4, 1); INSERT INTO VARIABLE_TERM VALUES (175, '?var174', 1, 1); INSERT INTO VARIABLE_TERM VALUES (176, '?var175', 4, 1); INSERT INTO VARIABLE_TERM VALUES (177, '?var176', 1, 1); INSERT INTO VARIABLE_TERM VALUES (178, '?var177', 4, 1); INSERT INTO VARIABLE_TERM VALUES (179, '?var178', 1, 1); INSERT INTO VARIABLE_TERM VALUES (180, '?var179', 4, 1); INSERT INTO VARIABLE_TERM VALUES (181, '?var180', 1, 1); INSERT INTO VARIABLE_TERM VALUES (182, '?var181', 4, 1); INSERT INTO VARIABLE_TERM VALUES (183, '?var182', 1, 1); INSERT INTO VARIABLE_TERM VALUES (184, '?var183', 4, 1); INSERT INTO VARIABLE_TERM VALUES (185, '?var184', 1, 1); INSERT INTO VARIABLE_TERM VALUES (186, '?var185', 4, 1); INSERT INTO VARIABLE_TERM VALUES (187, '?var186', 1, 1); INSERT INTO VARIABLE_TERM VALUES (188, '?var187', 4, 1); INSERT INTO VARIABLE_TERM VALUES (189, '?var188', 1, 1); INSERT INTO VARIABLE_TERM VALUES (190, '?var189', 4, 1); INSERT INTO VARIABLE_TERM VALUES (191, '?var190', 1, 1); INSERT INTO VARIABLE_TERM VALUES (192, '?var191', 4, 1); INSERT INTO VARIABLE_TERM VALUES (193, '?var192', 1, 1); INSERT INTO VARIABLE_TERM VALUES (194, '?var193', 4, 1); INSERT INTO VARIABLE_TERM VALUES (195, '?var194', 1, 1); INSERT INTO VARIABLE_TERM VALUES (196, '?var195', 4, 1); INSERT INTO VARIABLE_TERM VALUES (197, '?var196', 1, 1); INSERT INTO VARIABLE_TERM VALUES (198, '?var197', 4, 1); INSERT INTO VARIABLE_TERM VALUES (199, '?var198', 1, 1); INSERT INTO VARIABLE_TERM VALUES (200, '?var199', 4, 1); INSERT INTO VARIABLE_TERM VALUES (201, '?var200', 1, 1); INSERT INTO VARIABLE_TERM VALUES (202, '?var201', 4, 1); INSERT INTO VARIABLE_TERM VALUES (203, '?var202', 1, 1); INSERT INTO VARIABLE_TERM VALUES (204, '?var203', 4, 1); INSERT INTO VARIABLE_TERM VALUES (205, '?var204', 1, 1); INSERT INTO VARIABLE_TERM VALUES (206, '?var205', 4, 1); INSERT INTO VARIABLE_TERM VALUES (207, '?var206', 1, 1); INSERT INTO VARIABLE_TERM VALUES (208, '?var207', 4, 1); INSERT INTO VARIABLE_TERM VALUES (209, '?var208', 1, 1); INSERT INTO VARIABLE_TERM VALUES (210, '?var209', 4, 1); INSERT INTO VARIABLE_TERM VALUES (211, '?var210', 1, 1); INSERT INTO VARIABLE_TERM VALUES (212, '?var211', 4, 1); INSERT INTO VARIABLE_TERM VALUES (213, '?var212', 1, 1); INSERT INTO VARIABLE_TERM VALUES (214, '?var213', 4, 1); INSERT INTO VARIABLE_TERM VALUES (215, '?var214', 1, 1); INSERT INTO VARIABLE_TERM VALUES (216, '?var215', 4, 1); INSERT INTO VARIABLE_TERM VALUES (217, '?var216', 1, 1); INSERT INTO VARIABLE_TERM VALUES (218, '?var217', 4, 1); INSERT INTO VARIABLE_TERM VALUES (219, '?var218', 1, 1); INSERT INTO VARIABLE_TERM VALUES (220, '?var219', 4, 1); INSERT INTO VARIABLE_TERM VALUES (221, '?var220', 1, 1); INSERT INTO VARIABLE_TERM VALUES (222, '?var221', 4, 1); INSERT INTO VARIABLE_TERM VALUES (223, '?var222', 1, 1); INSERT INTO VARIABLE_TERM VALUES (224, '?var223', 4, 1); INSERT INTO VARIABLE_TERM VALUES (225, '?var224', 1, 1); INSERT INTO VARIABLE_TERM VALUES (226, '?var225', 4, 1); INSERT INTO VARIABLE_TERM VALUES (227, '?var226', 1, 1); INSERT INTO VARIABLE_TERM VALUES (228, '?var227', 4, 1); INSERT INTO VARIABLE_TERM VALUES (229, '?var228', 1, 1); INSERT INTO VARIABLE_TERM VALUES (230, '?var229', 4, 1); INSERT INTO VARIABLE_TERM VALUES (231, '?var230', 1, 1); INSERT INTO VARIABLE_TERM VALUES (232, '?var231', 4, 1); INSERT INTO VARIABLE_TERM VALUES (233, '?var232', 1, 1); INSERT INTO VARIABLE_TERM VALUES (234, '?var233', 4, 1); INSERT INTO VARIABLE_TERM VALUES (235, '?var234', 1, 1); INSERT INTO VARIABLE_TERM VALUES (236, '?var235', 4, 1); INSERT INTO VARIABLE_TERM VALUES (237, '?var236', 1, 1); INSERT INTO VARIABLE_TERM VALUES (238, '?var237', 4, 1); INSERT INTO VARIABLE_TERM VALUES (239, '?var238', 1, 1); INSERT INTO VARIABLE_TERM VALUES (240, '?var239', 4, 1); INSERT INTO VARIABLE_TERM VALUES (241, '?var240', 1, 1); INSERT INTO VARIABLE_TERM VALUES (242, '?var241', 4, 1); INSERT INTO VARIABLE_TERM VALUES (243, '?var242', 1, 1); INSERT INTO VARIABLE_TERM VALUES (244, '?var243', 4, 1); INSERT INTO VARIABLE_TERM VALUES (245, '?var244', 1, 1); INSERT INTO VARIABLE_TERM VALUES (246, '?var245', 4, 1); INSERT INTO VARIABLE_TERM VALUES (247, '?var246', 1, 1); INSERT INTO VARIABLE_TERM VALUES (248, '?var247', 4, 1); INSERT INTO VARIABLE_TERM VALUES (249, '?var248', 1, 1); INSERT INTO VARIABLE_TERM VALUES (250, '?var249', 4, 1); INSERT INTO VARIABLE_TERM VALUES (251, '?var250', 1, 1); INSERT INTO VARIABLE_TERM VALUES (252, '?var251', 4, 1); INSERT INTO VARIABLE_TERM VALUES (253, '?var252', 1, 1); INSERT INTO VARIABLE_TERM VALUES (254, '?var253', 4, 1); INSERT INTO VARIABLE_TERM VALUES (255, '?var254', 1, 1); INSERT INTO VARIABLE_TERM VALUES (256, '?var255', 4, 1); CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null); SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 11; SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 11) OR (TERM_ID =21) OR (TERM_ID = 31); SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (11, 21, 31));
          Hide
          Mike Matrigali added a comment -

          at the high level for join workaround i am suggesting - syntax is problably not exact

          // table with 11 milion rows
          create table a (primary key a int, data int)

          instead of
          select * from a where a IN (num1, num2, num3)

          do
          create table temp (a int)
          insert values num1, num2, and num2 3 into temp

          and then
          select * from a where a.a = temp.a

          if that works then you can optimize by using user temp tables.
          And also you may be able to optimize by building a user defined table that just materializes the rows from an in memory array and avoid inserts, but
          the optimizer support of user defined tables is still evolving so not sure if it would pick right plan.

          Show
          Mike Matrigali added a comment - at the high level for join workaround i am suggesting - syntax is problably not exact // table with 11 milion rows create table a (primary key a int, data int) instead of select * from a where a IN (num1, num2, num3) do create table temp (a int) insert values num1, num2, and num2 3 into temp and then select * from a where a.a = temp.a if that works then you can optimize by using user temp tables. And also you may be able to optimize by building a user defined table that just materializes the rows from an in memory array and avoid inserts, but the optimizer support of user defined tables is still evolving so not sure if it would pick right plan.
          Hide
          Mike Matrigali added a comment -

          if regular ints work, it might be a casting issue. Not necessarily a problem with BIGINT itself, but somehow derby having the wrong type for the constant in the
          query.

          Show
          Mike Matrigali added a comment - if regular ints work, it might be a casting issue. Not necessarily a problem with BIGINT itself, but somehow derby having the wrong type for the constant in the query.
          Hide
          Tony Brusseau added a comment -

          CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null);
          had no effect.

          Show
          Tony Brusseau added a comment - CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null); had no effect.
          Hide
          Tony Brusseau added a comment - - edited

          *******************
          >the bug description is confusing to me. Is the following a valid statement of the 2 problem queries:
          >
          >1) in clause with 3 values on primary key on table with 11 million rows does not use index.
          >2) or with 2 equality constaints on primary key does not use index.
          >
          >I don't think the optmizer will do #2 as currently designed. I thought the optmizer had been improved to do #1.

          The main problem is that both of these 2 queries do a complete table scan for me on tables with more than 256 entries:
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (1688849860263937, 1688849860263975, 1688849860263960));
          SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID =1688849860263975) OR (TERM_ID = 1688849860263960);

          If either one did a multiprobe, I'd be happy although it is pretty clear that both of them should be doing multiprobes in this case.

          *******************
          > DERBY-47 is the original project that implemented the IN-LIST optimization that seems like it should be being applied in this case.

          DERBY-47 sounds like it might be related, but that bug is resolved and I'm able to do an IN query and it is still doing an entire table scan. Maybe this is a regression of that bug?

          *******************
          >you might see if your test case is affected by adding an update statistics call after loading the data in the base table. with unique indexes I would not think so, but
          >something wierd is going on so may lend some light.

          I'll try that out, but none of my queries use the unique index columns in the WHERE, so this doesn't sound very likely and the FormulaTerm table that I'm able to do this on, doesn't have a unique index defined for it.

          *******************
          > also for debugging would be good to understand if the problem is specific to BIGINT or not.

          I'll try it with regular ints.

          *******************
          > since just doing a single select has more overhead than you want, does your real use case want a lot of values in the in list? As a workarount you might try loading your
          > search values into a temp table and doing a join for the result, and see if the optimizer will internally do the index probes that make sense.

          Hmm, can you give me an example on how to do this? The common case is that I'll have 10's to 100's of rows to look up by id at once in tables with > 10 million rows.

          *******************
          > a union might be another workaround.

          I'm using JPA which doesn't support UNIONs.

          Show
          Tony Brusseau added a comment - - edited ******************* >the bug description is confusing to me. Is the following a valid statement of the 2 problem queries: > >1) in clause with 3 values on primary key on table with 11 million rows does not use index. >2) or with 2 equality constaints on primary key does not use index. > >I don't think the optmizer will do #2 as currently designed. I thought the optmizer had been improved to do #1. The main problem is that both of these 2 queries do a complete table scan for me on tables with more than 256 entries: SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (1688849860263937, 1688849860263975, 1688849860263960)); SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID =1688849860263975) OR (TERM_ID = 1688849860263960); If either one did a multiprobe, I'd be happy although it is pretty clear that both of them should be doing multiprobes in this case. ******************* > DERBY-47 is the original project that implemented the IN-LIST optimization that seems like it should be being applied in this case. DERBY-47 sounds like it might be related, but that bug is resolved and I'm able to do an IN query and it is still doing an entire table scan. Maybe this is a regression of that bug? ******************* >you might see if your test case is affected by adding an update statistics call after loading the data in the base table. with unique indexes I would not think so, but >something wierd is going on so may lend some light. I'll try that out, but none of my queries use the unique index columns in the WHERE, so this doesn't sound very likely and the FormulaTerm table that I'm able to do this on, doesn't have a unique index defined for it. ******************* > also for debugging would be good to understand if the problem is specific to BIGINT or not. I'll try it with regular ints. ******************* > since just doing a single select has more overhead than you want, does your real use case want a lot of values in the in list? As a workarount you might try loading your > search values into a temp table and doing a join for the result, and see if the optimizer will internally do the index probes that make sense. Hmm, can you give me an example on how to do this? The common case is that I'll have 10's to 100's of rows to look up by id at once in tables with > 10 million rows. ******************* > a union might be another workaround. I'm using JPA which doesn't support UNIONs.
          Hide
          Mike Matrigali added a comment - - edited

          DERBY-47 is the orignal project that implemented the IN-LIST optimization that seems like it should be being applied in this case.

          Show
          Mike Matrigali added a comment - - edited DERBY-47 is the orignal project that implemented the IN-LIST optimization that seems like it should be being applied in this case.
          Hide
          Mike Matrigali added a comment -

          also for debugging would be good to understand if the problem is specific to BIGINT or not.

          For in list optmization we use to just translate it to a index scan bounded by the min and max values in the list, which would be bad for your case. I thought it had been changed to do multiple probes into the index.

          Show
          Mike Matrigali added a comment - also for debugging would be good to understand if the problem is specific to BIGINT or not. For in list optmization we use to just translate it to a index scan bounded by the min and max values in the list, which would be bad for your case. I thought it had been changed to do multiple probes into the index.
          Hide
          Mike Matrigali added a comment - - edited

          since just doing a single select has more overhead than you want, does your real use case want a lot of values in the in list? As a workarount you might try loading your search values into a temp table and doing a join for the result, and see if the optimizer will internally do the index probes that make sense.

          a union might be another workaround.

          Show
          Mike Matrigali added a comment - - edited since just doing a single select has more overhead than you want, does your real use case want a lot of values in the in list? As a workarount you might try loading your search values into a temp table and doing a join for the result, and see if the optimizer will internally do the index probes that make sense. a union might be another workaround.
          Hide
          Mike Matrigali added a comment -

          you might see if your test case is affected by adding an update statistics call after loading the data in the base table. with unique indexes I would not think so, but
          something wierd is going on so may lend some light.

          Show
          Mike Matrigali added a comment - you might see if your test case is affected by adding an update statistics call after loading the data in the base table. with unique indexes I would not think so, but something wierd is going on so may lend some light.
          Hide
          Mike Matrigali added a comment -

          the bug description is confusing to me. Is the following a valid statement of the 2 problem queries:

          1) in clause with 3 values on primary key on table with 11 million rows does not use index.
          2) or with 2 equality constaints on primary key does not use index.

          I don't think the optmizer will do #2 as currently designed. I thought the optmizer had been improved to do #1.

          Show
          Mike Matrigali added a comment - the bug description is confusing to me. Is the following a valid statement of the 2 problem queries: 1) in clause with 3 values on primary key on table with 11 million rows does not use index. 2) or with 2 equality constaints on primary key does not use index. I don't think the optmizer will do #2 as currently designed. I thought the optmizer had been improved to do #1.
          Hide
          Tony Brusseau added a comment - - edited

          I did some more testing and it looks like if I create a table with 128 rows, then b-tree searches are performed in every case. However, if I increase the table to 256 rows or larger, then I start getting heap searches that scan every row when querying for multiple rows via primary key. Here is some code that will generate the SQL for a table that demonstrates the problem:

          public static void main(String[] args) {
          final Logger logger = Logger.getLogger(DerbyTester.class.toString());
          try {
          StringBuilder buf = new StringBuilder(1024*1024);
          buf.append("DROP TABLE VARIABLE_TERM;\n");
          buf.append("\n");
          buf.append("CREATE TABLE VARIABLE_TERM\n");
          buf.append("(\n");
          buf.append(" term_id BIGINT NOT NULL,\n");
          buf.append(" var_name VARCHAR(1024) NOT NULL,\n");
          buf.append(" var_type SMALLINT NOT NULL, \n");
          buf.append(" kb_status INTEGER NOT NULL \n");
          buf.append(");\n");
          buf.append("\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID >= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n");
          buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check CHECK ((var_type >= 0) AND (var_type <= 4));\n");
          buf.append("\n");
          int count = 256; // @note count should be at least 40
          long idStart = 1688849860263936L;
          for (int i = 0; i < count; i++)

          { buf.append("INSERT INTO VARIABLE_TERM VALUES (").append(idStart++).append(", \'?var").append(i).append("\', ").append(((i %2) == 0) ? 1 : 4).append(", 1);\n"); }

          buf.append("\n");
          buf.append("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 1688849860263966;\n");
          buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID =1688849860263975) OR (TERM_ID = 1688849860263960);\n");
          buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (1688849860263937, 1688849860263975, 1688849860263960));\n");
          buf.append("\n");
          System.out.println(buf.toString());
          System.out.flush();
          } catch (Exception e)

          { logger.log(Level.SEVERE, e.getMessage(), e); }

          finally

          { System.exit(0); }

          }

          Show
          Tony Brusseau added a comment - - edited I did some more testing and it looks like if I create a table with 128 rows, then b-tree searches are performed in every case. However, if I increase the table to 256 rows or larger, then I start getting heap searches that scan every row when querying for multiple rows via primary key. Here is some code that will generate the SQL for a table that demonstrates the problem: public static void main(String[] args) { final Logger logger = Logger.getLogger(DerbyTester.class.toString()); try { StringBuilder buf = new StringBuilder(1024*1024); buf.append("DROP TABLE VARIABLE_TERM;\n"); buf.append("\n"); buf.append("CREATE TABLE VARIABLE_TERM\n"); buf.append("(\n"); buf.append(" term_id BIGINT NOT NULL,\n"); buf.append(" var_name VARCHAR(1024) NOT NULL,\n"); buf.append(" var_type SMALLINT NOT NULL, \n"); buf.append(" kb_status INTEGER NOT NULL \n"); buf.append(");\n"); buf.append("\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_term_id_pk PRIMARY KEY (term_id);\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT variable_term_id_check CHECK ((TERM_ID >= 1688849860263936) AND (TERM_ID <= 1970324836974591));\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_variable_name_unique UNIQUE (var_name, var_type);\n"); buf.append("ALTER TABLE variable_term ADD CONSTRAINT kb_variable_term_var_type_enum_check CHECK ((var_type >= 0) AND (var_type <= 4));\n"); buf.append("\n"); int count = 256; // @note count should be at least 40 long idStart = 1688849860263936L; for (int i = 0; i < count; i++) { buf.append("INSERT INTO VARIABLE_TERM VALUES (").append(idStart++).append(", \'?var").append(i).append("\', ").append(((i %2) == 0) ? 1 : 4).append(", 1);\n"); } buf.append("\n"); buf.append("SELECT * FROM VARIABLE_TERM WHERE TERM_ID = 1688849860263966;\n"); buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID = 1688849860263937) OR (TERM_ID =1688849860263975) OR (TERM_ID = 1688849860263960);\n"); buf.append("SELECT * FROM VARIABLE_TERM WHERE (TERM_ID IN (1688849860263937, 1688849860263975, 1688849860263960));\n"); buf.append("\n"); System.out.println(buf.toString()); System.out.flush(); } catch (Exception e) { logger.log(Level.SEVERE, e.getMessage(), e); } finally { System.exit(0); } }

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Tony Brusseau
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development