Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Unanswered: Question about indices

    If you have a table with 3 columns A, B and C and an composite index on A,B,C does it make any sense to have another index on A,B or B,C?

    If not, does it make a difference if index sort orders are different? I.e.
    A asc, B asc, C asc and
    A asc, B desc

    Last but not least what about
    A asc, B asc and
    A asc, B desc ?


    Thanks in advance,
    engram

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Regardless of sore order (indexes go both ways these days), assume an index with a,b,c exists. And index with a,b is totally redundant and not needed. An index on b,c, whether not redundant, may not be needed still depending on the data itself and the workload against the table.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, an index on A,B would have the (theoretical) advantage that it is smaller and the levels in the B-tree may have a higher fan-out, which means less I/O. However, usually that shouldn't make a noticeable/measurable difference and I wouldn't create such an index.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2012
    Posts
    5
    So, if you have an index on A,B,C you (normally) don't need another index on A,B because it's redundant. And index on B,C is not redundant, but normally not needed as well, correct?

    Another question: we actually have a table in our DB2 (v9.1, Windows) with these 3 indices (A,B,C / A,B / B,C). If I look at the indices size at the DB2 Control Center they are of exactly the same size (2620,12 MB). Is that just by chance or does it mean that these 3 indices are actually the same?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    All four co-existing indexes (a , b , c) , (a , b , c DESC) , (a , b) , (a , b DESC) could be used in different queries.

    I will publish DDLs and INSERT statement in my next post.

    Example 1: (a , b , c)
    Note: Index SYSIBM.SQL120127131830660 is an index corresponding to primary key which was created automaticaly.

    Extracted from output of db2exfmt.
    Code:
    Original Statement:
    ------------------
    SELECT a , b , MAX(c) AS min_c
    FROM index_test
    WHERE a < 2
    GROUP BY a , b
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.A AS "A", Q3.B AS "B", Q3.$C2 AS "MIN_C"
    FROM
       (SELECT Q2.A, Q2.B, MAX(Q2.C)
       FROM
          (SELECT Q1.A, Q1.B, Q1.C
          FROM DB2ADMIN.INDEX_TEST AS Q1
          WHERE (Q1.A < 2)) AS Q2
       GROUP BY Q2.B, Q2.A) AS Q3
    
    Access Plan:
    -----------
            Total Cost:             7.70881
            Query Degree:           1
    
            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
           31.031
           GRPBY
           (   2)
           7.69871
              1
             |
           72.3333
           IXSCAN
           (   3)
           7.68572
              1
             |
            1000
       INDEX: SYSIBM
     SQL120127131830660
             Q1

    Example 2: (a , b , c DESC)

    Extracted from output of db2exfmt.
    Code:
    Original Statement:
    ------------------
    SELECT a , b , c
    FROM
       (SELECT t.* , ROW_NUMBER() OVER(PARTITION BY a , b
       ORDER BY c DESC) AS rnum_desc
       FROM index_test t
       WHERE a < 2)
    WHERE rnum_desc = 1
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.A AS "A", Q3.B AS "B", Q3.C AS "C"
    FROM
       (SELECT Q2.A, Q2.B, Q2.C, ROW_NUMBER() OVER (PARTITION BY Q2.A, Q2.B ORDER
               BY Q2.C DESC)
       FROM
          (SELECT Q1.A, Q1.B, Q1.C
          FROM DB2ADMIN.INDEX_TEST AS Q1
          WHERE (Q1.A < 2)) AS Q2) AS Q3
    WHERE (Q3.$C3 = 1)
    
    Access Plan:
    -----------
            Total Cost:             7.74375
            Query Degree:           1
    
          Rows
         RETURN
         (   1)
          Cost
           I/O
           |
         31.031
         FILTER
         (   2)
         7.74375
            1
           |
         72.3333
         IXSCAN
         (   3)
         7.68572
            1
           |
          1000
     INDEX: DB2ADMIN
      IX_A_B_C_DESC
           Q1

    Example 3: (a , b)

    Extracted from output of db2exfmt.
    Code:
    Original Statement:
    ------------------
    SELECT a , MIN(b) AS min_b
    FROM index_test
    GROUP BY a
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.A AS "A", Q3.$C1 AS "MIN_B"
    FROM
       (SELECT Q2.A, MIN(Q2.B)
       FROM
          (SELECT Q1.A, Q1.B
          FROM DB2ADMIN.INDEX_TEST AS Q1) AS Q2
       GROUP BY Q2.A) AS Q3
    
    Access Plan:
    -----------
            Total Cost:             16.5741
            Query Degree:           1
    
          Rows
         RETURN
         (   1)
          Cost
           I/O
           |
           33
         GRPBY
         (   2)
         16.5634
            2
           |
          1000
         IXSCAN
         (   3)
         16.3861
            2
           |
          1000
     INDEX: DB2ADMIN
         IX_A_B
           Q1

    Example 4: (a , b DESC)

    Extracted from output of db2exfmt.
    Code:
    Original Statement:
    ------------------
    SELECT COUNT(*) count_rows
    FROM index_test
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.$C0 AS "COUNT_ROWS"
    FROM
       (SELECT COUNT(*)
       FROM
          (SELECT $RID$
          FROM DB2ADMIN.INDEX_TEST AS Q1) AS Q2) AS Q3
    
    Access Plan:
    -----------
            Total Cost:             16.564
            Query Degree:           1
    
          Rows
         RETURN
         (   1)
          Cost
           I/O
           |
            1
         GRPBY
         (   2)
         16.5634
            2
           |
          1000
         IXSCAN
         (   3)
         16.3861
            2
           |
          1000
     INDEX: DB2ADMIN
       IX_A_B_DESC
           Q1

    Example 5: tbscan

    Extracted from output of db2exfmt.
    Code:
    Original Statement:
    ------------------
    SELECT COUNT(*) AS count_rows , COUNT(DISTINCT a * 1000000 + b * 1000 + c) AS
            count_distinct
    FROM index_test
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.$C0 AS "COUNT_ROWS", Q3.$C1 AS "COUNT_DISTINCT"
    FROM
       (SELECT COUNT(*), COUNT(DISTINCT Q2.$C0)
       FROM
          (SELECT (((Q1.A * 1000000) + (Q1.B * 1000)) + Q1.C)
          FROM DB2ADMIN.INDEX_TEST AS Q1) AS Q2) AS Q3
    
    Access Plan:
    -----------
            Total Cost:             25.0789
            Query Degree:           1
    
          Rows
         RETURN
         (   1)
          Cost
           I/O
           |
            1
         GRPBY
         (   2)
         25.0783
            3
           |
           33
         TBSCAN
         (   3)
         25.0723
            3
           |
           33
         SORT
         (   4)
         25.0653
            3
           |
          1000
         TBSCAN
         (   5)
         23.9556
            3
           |
          1000
     TABLE: DB2ADMIN
       INDEX_TEST
           Q1
    Last edited by tonkuma; 01-27-12 at 05:52. Reason: Correct some minor spellings.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DDLs and INSERT statement used in the test.

    DB2 version/release/fix pack:
    Code:
    ------------------------------ Commands Entered ------------------------------
    db2level
    ------------------------------------------------------------------------------
    
    D:\IBM\SQLLIB_V97\tools>db2level
    DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with 
    level identifier "08060107".
    Informational tokens are "DB2 v9.7.500.4299", "special_27924", "IP23286_27924", 
    and Fix Pack "5".
    Product is installed at "D:\IBM\SQLLIB_V97" with DB2 Copy Name "DB2COPY1".

    CREATE table:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE index_test
    ( a INTEGER NOT NULL
    , b INTEGER NOT NULL
    , c INTEGER NOT NULL
    , PRIMARY KEY (a , b , c)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    INSERT statement:
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO index_test
    SELECT n / 31      AS a
         , MOD(n , 13) AS b
         , MOD(n ,  5) AS c
     FROM  (SELECT n1 + n2 * 10 + n3 * 100 AS n
             FROM  (VALUES 0,1,2,3,4,5,6,7,8,9) n(n1)
                 , (VALUES 0,1,2,3,4,5,6,7,8,9) n(n2)
                 , (VALUES 0,1,2,3,4,5,6,7,8,9) n(n3)
           )
     ORDER BY
           a , b , c
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    CREATE indexes:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE UNIQUE INDEX ix_a_b_c_desc
    ON index_test
    ( a , b , c DESC);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE INDEX ix_a_b
    ON index_test
    ( a , b);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE INDEX ix_a_b_desc
    ON index_test
    ( a , b DESC);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Values in the table.

    Code:
    ------------------------------------------------------------------------------
    
    A           COUNT_A     B           COUNT_B     C           COUNT_C    
    ----------- ----------- ----------- ----------- ----------- -----------
              0          31           0          77           0         200
              1          31           1          77           1         200
              2          31           2          77           2         200
              3          31           3          77           3         200
              4          31           4          77           4         200
              5          31           5          77           -           -
              6          31           6          77           -           -
              7          31           7          77           -           -
              8          31           8          77           -           -
              9          31           9          77           -           -
             10          31          10          77           -           -
             11          31          11          77           -           -
             12          31          12          76           -           -
             13          31           -           -           -           -
             14          31           -           -           -           -
             15          31           -           -           -           -
             16          31           -           -           -           -
             17          31           -           -           -           -
             18          31           -           -           -           -
             19          31           -           -           -           -
             20          31           -           -           -           -
             21          31           -           -           -           -
             22          31           -           -           -           -
             23          31           -           -           -           -
             24          31           -           -           -           -
             25          31           -           -           -           -
             26          31           -           -           -           -
             27          31           -           -           -           -
             28          31           -           -           -           -
             29          31           -           -           -           -
             30          31           -           -           -           -
             31          31           -           -           -           -
             32           8           -           -           -           -
    
      33 record(s) selected.

    The query use to produce the result:
    Code:
    SELECT a , count_a
         , b , count_b
         , c , count_c
     FROM  (
           SELECT a
                , COUNT(*) AS count_a
                , ROW_NUMBER() OVER(ORDER BY a) rnum_a
            FROM  index_test
            GROUP BY
                  a
           )
     FULL  OUTER JOIN (
           SELECT b
                , COUNT(*) AS count_b
                , ROW_NUMBER() OVER(ORDER BY b) rnum_b
            FROM  index_test
            GROUP BY
                  b
           )
       ON  rnum_b = rnum_a
     FULL  OUTER JOIN (
           SELECT c
                , COUNT(*) AS count_c
                , ROW_NUMBER() OVER(ORDER BY c) rnum_c
            FROM  index_test
            GROUP BY
                  c
           )
       ON  rnum_c = COALESCE(rnum_b , rnum_a)
     ORDER BY
           COALESCE(rnum_c , rnum_b , rnum_a)
    ;

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by tonkuma View Post
    All four co-existing indexes (a , b , c) , (a , b , c DESC) , (a , b) , (a , b DESC) could be used in different queries.
    One can often create a custom index for each different SQL statement that is optimized just for that SQL statement. However, there are costs of having additional indexes that need to be considered when determning whether an index is redundant or not:

    1. CPU cost of maintaining those indexes (on insert, update, or delete of the data row),
    2. Use of bufferpool memory for those indexes (possibly depriving other objects from staying in the bufferpool without getting flushed out),
    3. Cost of disk storage and I/O bandwith.

    In the above example and most of the responses, those who said the index is redundant are basically correct, especially when taking into account the cost of the extra index(es). That is based on the assumption that if you have an index that uses the B-tree for index access (rather than a table scan, or scan of the entire index), that is usually sufficient for good performance, and avoids the costs of too many "custom" indexes that might actually degrade overall performance of the system, even if a particular query might perform very slightly better with custom indexes.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Marcus_A,

    I agree with you.
    Extra indexes might give you better performance for some queries,
    but that would give you another costs that might be more than the saved cost of the queries.

    I want to show that those extra/redundant indexes are NOT COMPLETELY USELESS.
    Sometimes, those redundant indexes are used and may give you better performance.
    Extra indexes are realy problem for OLTP.
    But, more indexes might be built in DataWarehouse applications.


    I want to show another example.

    If removed the index (a , b , c DESC) in Example 2, another index (a , b , c) might be used.
    But, the query cost increased slightly.

    Remove index (a , b , c DESC)
    Code:
    ------------------------------ Commands Entered ------------------------------
    DROP INDEX ix_a_b_c_desc;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Then, access path was changed and cost was increased slightly.

    Total Cost: 7.7465 -- Example 2a
    Total Cost: 7.74375 -- Example 2

    Example 2a: (a , b , c)
    Note: Index SYSIBM.SQL120127131830660 is an index corresponding to primary key which was created automaticaly.
    Code:
    Original Statement:
    ------------------
    SELECT a , b , c
    FROM
       (SELECT t.* , ROW_NUMBER() OVER(PARTITION BY a , b
       ORDER BY c DESC) AS rnum_desc
       FROM index_test t
       WHERE a < 2)
    WHERE rnum_desc = 1
    
    
    Optimized Statement:
    -------------------
    SELECT Q3.A AS "A", Q3.B AS "B", Q3.C AS "C"
    FROM
       (SELECT Q2.A, Q2.B, Q2.C, ROW_NUMBER() OVER (PARTITION BY Q2.A, Q2.B ORDER
               BY Q2.C DESC)
       FROM
          (SELECT Q1.A, Q1.B, Q1.C
          FROM DB2ADMIN.INDEX_TEST AS Q1
          WHERE (Q1.A < 2)) AS Q2) AS Q3
    WHERE (Q3.$C3 = 1)
    
    Access Plan:
    -----------
            Total Cost:             7.7465
            Query Degree:           1
    
            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
           31.031
           FILTER
           (   2)
           7.7465
              1
             |
           72.3333
           IXSCAN
           (   3)
           7.68847
              1
             |
            1000
       INDEX: SYSIBM
     SQL120127131830660
             Q1
    Last edited by tonkuma; 01-27-12 at 12:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •