|
Appendix A — Examples Queries and Possible Indexing Strategies
The following are some examples of SQL query requests with a recommended set of indexes to create. The purpose of these examples is to demonstrate the concept of proactive index creation based on the actual SQL request and knowledge of the query optimizer and database engine. These examples are only listed to illustrate one proactive methodology. The actual implementation and performance of these SQL requests will be dependent upon several factors, including, but not limited to: database table and index sizes, version of OS/400 and DB2 UDB for iSeries, query interface attributes, job and system attributes, and environment. The query plans and performance results will vary.
Example 1
SELECT *
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR)
Or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
Anticipating index probe or table probe with dynamic bitmap
Example 2
SELECT *
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE)
(keys can be in any order, most selective column first)
Or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
CREATE ENCODED VECTOR INDEX TABLE1_EVI2 ON TABLE1 (SIZE)
(anticipating index probe or table probe with dynamic bitmaps)
Example 3
SELECT *
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
AND A.STYLE = 'ADULT MENS T-SHIRT'
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE, STYLE)
(keys can be in any order, most selective columns first)
Or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
CREATE ENCODED VECTOR INDEX TABLE1_EVI2 ON TABLE1 (SIZE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI3 ON TABLE1 (STYLE)
(anticipating index probe or table probe with dynamic bitmaps)
Example 4
SELECT * FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
AND A.STYLE = 'ADULT MENS T-SHIRT'
AND A.INVENTORY > 100
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE, STYLE, INVENTORY)
(keys can be in any order, most selective columns first, non-equal predicate last)
Or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
CREATE ENCODED VECTOR INDEX TABLE1_EVI2 ON TABLE1 (SIZE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI3 ON TABLE1 (STYLE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI4 ON TABLE1 (INVENTORY)
(anticipating index probe or table probe with dynamic bitmaps)
Example 5
SELECT * FROM TABLE1 A, TABLE2 B
WHERE A.KEY = B.KEY
AND A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
AND A.STYLE = 'ADULT MENS T-SHIRT'
AND A.INVENTORY > 100
CREATE INDEX TABLE1_INDEX1 ON TABLE1
(COLOR, SIZE, STYLE, KEY, INVENTORY)
(keys can be in any order, most selective local selection columns first, non-equal predicate last)
CREATE INDEX TABLE2_INDEX1 ON TABLE2 (KEY)
And / or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
CREATE ENCODED VECTOR INDEX TABLE1_EVI2 ON TABLE1 (SIZE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI3 ON TABLE1 (STYLE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI4 ON TABLE1 (INVENTORY)
(anticipating index probe, or table probe with dynamic bitmaps, and nested loop join via index)
Example 6
SELECT A.STORE, A.STYLE, A.SIZE, A.COLOR SUM(A.QUANTITY_SOLD)
FROM TABLE1 A, TABLE2 B
WHERE A.KEY = B.KEY
AND A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
AND A.STYLE = 'ADULT MENS T-SHIRT'
GROUP BY A.STORE, A.STYLE, A.SIZE, A.COLOR
CREATE INDEX TABLE1_INDEX1 ON TABLE1
(COLOR, SIZE, STYLE, KEY)
(keys can be in any order, most selective local selection columns first)
CREATE INDEX TABLE1_INDEX2 ON TABLE1
(STORE, STYLE, SIZE, COLOR)
(keys must be in this order for grouping stats)
Generate column statistics on STORE, STYLE, SIZE, COLOR (V5R2 only)
CREATE INDEX TABLE2_INDEX1 ON TABLE2 (KEY)
And / or
CREATE ENCODED VECTOR INDEX TABLE1_EVI1 ON TABLE1 (COLOR)
CREATE ENCODED VECTOR INDEX TABLE1_EVI2 ON TABLE1 (SIZE)
CREATE ENCODED VECTOR INDEX TABLE1_EVI3 ON TABLE1 (STYLE)
(anticipating index probe, or table probe with dynamic bitmaps, and nested loop join via index)
Example 7
SELECT *
FROM TABLE1 A,
TABLE2 B
WHERE A.KEY_COL1 = B.KEY_COL2
AND A.COLOR IN ('BLUE', 'GREEN', 'RED')
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, KEY_COL1)
CREATE INDEX TABLE2_INDEX1 ON TABLE2 (KEY_COL2)
(anticipating index probe and nested loop join via index)
Example 8
SELECT A.COL3, A.COL4, B.COL2, C.COL6, C.COL7
FROM TABLE1 A,
TABLE2 B,
TABLE3 C
WHERE A.KEY_COL1 = B.KEY_COL1
AND A.KEY_COL2 = C.KEY_COL2
AND A.COLOR IN ('BLUE', 'GREEN', 'RED')
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, KEY_COL1)
CREATE INDEX TABLE1_INDEX2 ON TABLE1 (COLOR, KEY_COL2)
CREATE INDEX TABLE2_INDEX1 ON TABLE2 (KEY_COL1)
CREATE INDEX TABLE3_INDEX1 ON TABLE3 (KEY_COL2)
(anticipating index probe and nested loop join via index)
Example 9
SELECT A.COLOR, A.SIZE, SUM(A.SALES), SUM(A.QUANTITY)
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
GROUP BY A.COLOR, A.SIZE
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE)
(anticipating index probe and grouping via index)
Example 10
SELECT A.COLOR, A.SIZE, SUM(A.SALES), SUM(A.QUANTITY)
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
GROUP BY A.COLOR, A.SIZE
ORDER BY A.COLOR, A.SIZE
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE)
(anticipating index probe, grouping via index and ordering via index)
Example 11
SELECT A.COLOR, A.SIZE, MIN(A.QUANTITY)
FROM TABLE1 A
WHERE A.COLOR IN ('BLUE', 'GREEN', 'RED')
GROUP BY A.COLOR, A.SIZE
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE, QUANTITY)
(anticipating index probe and grouping via index with "early exit" for MIN)
Example 12
SELECT B.COLOR, B.SIZE, SUM(A.SALES), SUM(A.QUANTITY)
FROM TABLE1 A,
TABLE2 B
WHERE A.KEY_COL1 = B.KEY_COL1
AND B.COLOR IN ('BLUE', 'GREEN', 'RED')
AND B.SIZE IN ('LARGE', 'X-LARGE')
GROUP BY B.COLOR, B.SIZE
ORDER BY B.COLOR, B.SIZE
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (KEY_COL1)
CREATE INDEX TABLE2_INDEX1 ON TABLE2 (COLOR, SIZE, KEY_COL1)
(anticipating index probe, nested loop join via index, and grouping and ordering via index)
Example 13
SELECT A.COLOR, A.SIZE, A.SALES
FROM TABLE1 A
WHERE A.SALES < (SELECT AVG(B.SALES)
FROM TABLE1 B
WHERE B.SIZE IN ('LARGE', 'X-LARGE'))
AND A.COLOR IN ('BLUE', 'GREEN', 'RED')
AND A.SIZE IN ('LARGE', 'X-LARGE')
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (SIZE, COLOR)
(anticipating index probe and grouping via index)
Since SIZE is the first key column in the index, the index can be used for both the inner and outer queries.)
Example 14
SELECT A.COLOR, A.SIZE, SUM(A.SALES), SUM(A.QUANTITY)
FROM TABLE1 A
GROUP BY B.COLOR, B.SIZE
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE)
(anticipating grouping via index)
Or
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (COLOR, SIZE, SALES, QUANTITY)
[anticipating grouping via index and index only access (all columns in the index)]
Example 15
SELECT A.CUSTOMER, A.CUSTOMER_NUMBER, A.YEAR, A.MONTH, A.SALES
FROM TABLE1 A
WHERE A.SALES > (SELECT AVG(B.SALES)
FROM TABLE1 B
WHERE B.CUSTOMER = A.CUSTOMER
AND B.YEAR = A.YEAR)
AND A.YEAR = 2001
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (YEAR, CUSTOMER, SALES)
[anticipating index probe, index only access (subquery) and grouping via index (subquery) — index used for both queries]
Example 16
SELECT A.CUSTOMER, A.CUSTOMER_NUMBER, A.YEAR, A.MONTH, A.SALES
FROM TABLE1 A
WHERE A.CUSTOMER_NUMBER IN
(SELECT B.CUSTOMER_NUMBER
FROM TABLE2 B
WHERE NUMBER_OF_RETURNS > 10
AND B.YEAR = 2000
AND B.MONTH IN (10, 11, 12))
AND A.YEAR = 2001
AND A.MONTH IN (1, 2, 3)
CREATE INDEX TABLE1_INDEX1 ON TABLE1
(YEAR, MONTH, CUSTOMER_NUMBER)
CREATE INDEX TABLE2_INDEX1 ON TABLE2
(YEAR, MONTH, CUSTOMER_NUMBER, NUMBER_OF_RETURNS)
(anticipating index probe, subquery join composite via nested loop join via index)
Example 17
UPDATE TABLE1
SET COL1 = 125
SET COL2 = 'ABC'
SET COL3 = 'This is updated'
WHERE CUSTOMER_NUMBER IN (4537, 7824, 2907)
AND YEAR = 2001
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (YEAR, CUSTOMER_NUMBER)
Anticipating index probe
Example 18
UPDATE TABLE1 A
SET LAST_YEARS_MAX_SALES =
(SELECT MAX(SALES)
FROM TABLE2 B
WHERE B.YEAR = 2000
AND B.MONTH = A.MONTH
AND B.CUSTOMER = A.CUSTOMER)
WHERE A.YEAR = 2001
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (YEAR)
CREATE INDEX TABLE2_INDEX1 ON TABLE2
(YEAR, MONTH, CUSTOMER, SALES DESC)
(anticipating index probe and grouping via index with "early exit" for MAX)
Example 19
DELETE FROM TABLE1
WHERE ITEM_NUMBER IN ('23-462', '45-7124', '21-2007')
AND QUANTITY = 0
CREATE INDEX TABLE1_INDEX1 ON TABLE1 (ITEM_NUMBER, QUANTITY)
(anticipating index probe)
Example 20
SELECT T.CHAR_DATE,
C.COUNTRY,
C.CUSTOMER_NAME,
P.PART_NAME,
S.SUPPLIER_NAME,
SUM(F.QUANTITY),
SUM(F.REVENUE_WO_TAX)
FROM STARLIB/SALES_FACTS F,
STARLIB/PART_DIM P,
STARLIB/TIME_DIM T,
STARLIB/CUST_DIM C,
STARLIB/SUPP_DIM S
WHERE F.PARTKEY = P.PARTKEY
AND F.TIMEKEY = T.TIMEKEY
AND F.CUSTKEY = C.CUSTKEY
AND F.SUPPKEY = S.SUPPKEY
AND T.YEAR = 2000
AND T.MONTH = 06
AND T.DAY = 30
AND C.COUNTRY = 'JAPAN'
AND P.MFGR = 'Manufacturer#3'
GROUP BY T.CHAR_DATE,
C.COUNTRY
C.CUSTOMER_NAME,
P.PART_NAME,
S.SUPPLIER_NAME
ORDER BY T.CHAR_DATE,
C.COUNTRY,
C.CUSTOMER_NAME,
P.PART_NAME
CREATE INDEX SALES_FACTS_INDEX1 ON SALES_FACTS (PARTKEY)
CREATE INDEX SALES_FACTS_INDEX2 ON SALES_FACTS (TIMEKEY)
CREATE INDEX SALES_FACTS_INDEX3 ON SALES_FACTS (CUSTKEY)
CREATE INDEX SALES_FACTS_INDEX4 ON SALES_FACTS (SUPPKEY)
CREATE INDEX PART_DIM_INDEX1 ON PART_DIM (MFGR, PARTKEY, PART_NAME)
CREATE INDEX TIME_DIM_INDEX1 ON TIME_DIM (YEAR, MONTH, DAY, TIMEKEY)
CREATE INDEX CUST_DIM_INDEX1 ON CUST_DIM (COUNTRY, CUSTKEY)
CREATE INDEX SUPP_DIM_INDEX1 ON SUPP_DIM (SUPPKEY, SUPPLIER_NAME)
Generate column statistics on T.CHAR_DATE, C.COUNTRY, C.CUSTOMER_NAME,
P.PART_NAME, S.SUPPLIER_NAME (V5R2 only)
(anticipating index probe, index only access and nested loop join via index)
[Back | Next]
|
