Question about indices

engram · #1 (**permalink**) 01-26-12, 11:08

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

ARWinner · #2 (**permalink**) 01-26-12, 11:22

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

stolze · #3 (**permalink**) 01-26-12, 14:40

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.

engram · #4 (**permalink**) 01-27-12, 03:31

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?

tonkuma · #5 (**permalink**) 01-27-12, 04:40

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

tonkuma · #6 (**permalink**) 01-27-12, 04:48

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.

tonkuma · #7 (**permalink**) 01-27-12, 05:19

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

Marcus_A · #8 (**permalink**) 01-27-12, 06:56

Quote:

Originally Posted by tonkuma

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:

CPU cost of maintaining those indexes (on insert, update, or delete of the data row),
Use of bufferpool memory for those indexes (possibly depriving other objects from staying in the bufferpool without getting flushed out),
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.

tonkuma · #9 (**permalink**) 01-27-12, 11:05

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

Internet Services

Web Development

Digital Marketing

Consumer Tech