ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Visual Explain "Indexes do not match" meaning?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Visual Explain "Indexes do not match" meaning?

    I am running this query
    Code:
    SELECT *
      FROM table1 a
     INNER JOIN table2 b
        ON a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
    Both tables have a non-unique index on key1,2,3 in that order.

    However the SQL processor is not using the table1 index, it's doing a Table Scan (over 6.3 million rows) instead.

    Visual Explain, when I click on the Table Scan and look at "Information About the Plan Performed", I see:
    Code:
    Information About the Plan Performed    
    Scrollable    Yes
    Plan Name    Table Scan
    Plan Step Type    Logic
    Reason Code    Indexes Do Not Match
    Plan Step Name    Node_40
    List of Indexes Optimized    MYLIB/TABLE1LA 11, MYLIB/TABLE1LB 4
    Interestingly, TABLE1LB is the logical that provides the index it should be using.

    The only thing of note, is that TABLE1LB is a DDS logical that specifies the keyword FIFO, which I have never seen before. The table has many other DDS logicals, but no actual SQL indexes (that I can find)

    Can anyone tell me what "Indexes do not match" means, and/or why the SQL processor may not be ignoring the index provided by the TABLE1LB logical?



  • #2
    Typo on the last line. Should be: "...why the SQL processor seems to be ignoring the index..."

    Comment


    • #3
      Visual Explain is ingoring the index because where are no WHERE conditions to reduce the number of rows to be returned. So a table scan is performed. For each row in the first table the appropriate rows from the second tablel are joined. (BTW a binary radix tree index is only used if maximum 15-20% of the data in a table is returned. Otherwise an index access is to slow.
      You may try whether the index is used if you add an order by in the Key1, Key2, Key3 sequence.

      Birgitta

      Comment


      • #4
        Thanks Birgitta. So it sounds like "Indexes do not match" is the same as "Table scan cost is better"?

        However, I have now tried adding a Where field that would mean the query only returns 6 rows out of the 6 million:

        PHP Code:
        SELECT *
        FROM table1 a
        INNER JOIN table2 b
        ON a
        .key1 b.key1
        AND a.key2 b.key2
        AND a.key3 b.key3
        WHERE b
        .field4 100 and b.field4 50
        The Where clause matches 2000 rows out of 73000 in table2, and those rows match only 6 in table1. There is a key on field4.

        But it's still doing a table scan for the 6 million row table1?
        Last edited by jamief; March 17, 2020, 03:15 PM.

        Comment

        Working...
        X