Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6939

Update statement poor performance with index using where in (select

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.11.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      An update statement such as below doesn't appear to be using the index:

      update mytable set col1 = 'myvalue' where indexed_col in (select index_col from mytable where col1 like 'myv%' fetch first 100 rows only);

      The table in this case has over a million rows. Running the sub-query:

      select index_col from mytable where col1 like 'myv%' fetch first 100 rows only

      Returns almost immediately with 100 rows but this update takes over two minutes. Update does not seem to be using index because originally we try the query without the index and performance after adding the index seemed to be the same.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              j1mcross Jim Cross
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: