SQL Query Optimization Messages › Examples

Examples

MSG=DD

In this example, the user is only concerned with customers with IND_CD = 'A', so IND_CD was omitted from the SELECT and GROUP BY list. Since CUST_NO only appears as the second column of index ACSTI, this causes a sort for the GROUP BY. Adding IND_CD eliminates this inefficiency.

Before Adding IND_CD to SELECT and GROUP BY List

 PLAN:SYSADM         .ISDBXXXX80270477   STM:000000001. DT: 07/28/1993 11.51.49
 DECLARE C0         CURSOR FOR
 SELECT CUST_NO, SUM(ORD_AMT)
 FROM ACCTS
 WHERE IND_CD  = 'A'
 GROUP BY CUST_NO
            ***** BIND MESSAGES FOR SUBSELECT 01 *****
 INDEX DEFINITIONS FOR: ACT/010 SYSADM.ACCTS
 KEYS= 2, IDXLVLS=1, LN=   31, ROWS/BLK= 95 ROWS=        3
     KEY ACTOR id=  4 FLG=D0 00 FLDS= 1 DXX= 300 BLKCHG= 204 ROWS=3
         OFFSET=    0, LN=  5, DIR=ASC  SENS=N CARD=        3 ORD_ID
     KEY ACSTI id=  1 FLG=04 00 FLDS= 2 DXX= 300 BLKCHG= 204 ROWS=3
         OFFSET=   26, LN=  1, DIR=ASC  SENS=N CARD=        2 IND_CD
         OFFSET=   27, LN=  4, DIR=ASC  SENS=N CARD=        3 CUST_NO
 TBL ACT DBID 010 RESTRICTION COSTS:
         KEY ACTOR 1ST  0 FLDS SELECTIVITY= 1.000000000
              0 LOW-ORDER FLDS SELECTIVITY= 1.000000000
                          DATA SELECTIVITY= 0.099999964
             INDEX         1 DATA         1 SORT         3 ROWS         1
         KEY ACSTI 1ST  1 FLDS SELECTIVITY= 0.500000000
              0 LOW-ORDER FLDS SELECTIVITY= 1.000000000
                          DATA SELECTIVITY= 1.000000000
             INDEX         1 DATA         1 SORT         3 ROWS         2
         *** KEY ACTOR HAS LOWEST ESTIMATED COST OF         5
     *** RESTRICTION COST     =        5
 ESTIMATED GROUP SORT COST= 3
 KEY ACTOR HAS LOWEST COST OF         5 FOR 1 ROWS
 GROUP BY SRT REASONS= CBS ORDER
 BUILDING SORTED RESULT TABLE FOR SUBSELECT 1
 ACTIVITY FOR TBL <SYSADM.ACCTS
    SETS=000000001 INDEX=000000001 DATA=000000001 QUAL=000000001 I/O=000000001
    CBS OPTIMIZER REASONS: <       I YY Y           >
 SORT COMPLETED: ROWS EST=1 IN=1, OUT=1
 HIGHEST SORT MERGE LEVEL = 0

After Adding IND_CD to SELECT and GROUP BY List

 PLAN:SYSADM         .ISDBXXXX80270477   STM:000000002 DT: 07/28/1993 11.51.50
 DECLARE C0         CURSOR FOR
 SELECT IND_CD, CUST_NO, SUM(ORD_AMT)
 FROM ACCTS
 WHERE IND_CD  = 'A'
 GROUP BY IND_CD, CUST_NO
            ***** BIND MESSAGES FOR SUBSELECT 01 *****
 INDEX DEFINITIONS FOR: ACT/010 SYSADM.ACCTS
 KEYS= 2, IDXLVLS=1, LN=   31, ROWS/BLK= 95 ROWS=        3
     KEY ACTOR id=  4 FLG=D0 00 FLDS= 1 DXX= 300 BLKCHG= 204 ROWS=3
         OFFSET=    0, LN=  5, DIR=ASC  SENS=N CARD=        3 ORD_ID
     KEY ACSTI id=  1 FLG=04 00 FLDS= 2 DXX= 300 BLKCHG= 204 ROWS=3
         OFFSET=   26, LN=  1, DIR=ASC  SENS=N CARD=        2 IND_CD
         OFFSET=   27, LN=  4, DIR=ASC  SENS=N CARD=        3 CUST_NO
 KEY ACSTI SATISFIES GROUP BY
 TBL ACT DBID 010 RESTRICTION COSTS:
         KEY ACTOR 1ST  0 FLDS SELECTIVITY= 1.000000000
              0 LOW-ORDER FLDS SELECTIVITY= 1.000000000
                          DATA SELECTIVITY= 0.099999964
             INDEX         1 DATA         1 SORT         3 ROWS         1
         KEY ACSTI 1ST  1 FLDS SELECTIVITY= 0.500000000
              0 LOW-ORDER FLDS SELECTIVITY= 1.000000000
                          DATA SELECTIVITY= 1.000000000
             INDEX         1 DATA         1 SORT         0 ROWS         2
         *** KEY ACSTI HAS LOWEST ESTIMATED COST OF         2
     *** RESTRICTION COST     =        2
 ESTIMATED GROUP SORT COST= 3
 KEY ACSTI USED FOR GROUPING
 ACTIVITY FOR TBL <SYSADM.ACCTS
    SETS=000000001 INDEX=000000001 DATA=000000001 QUAL=000000001 I/O=000000001
    CBS OPTIMIZER REASONS: <       P YY Y           >