This section shows a sample tuning procedure based on pdvwopt's output result. Use the information provided in subsections 17.6.2 Notes about index definitions through 17.6.4 Notes about join retrieval as guidelines for tuning.

 
*****     Result of SQL Optimizer     *****
Version        : HiRDB/Parallel Server VV-RR 4BES
UAP Name       : pdsql   -----------------------> Is the UAP name correct?
UAP Source     : pdsql-21775
Authorization  : user1   -----------------------> Is the authorization identifier correct?
--------------------------------------------------------------------------------
Section No     : 1
Optimize Time  : 2001-01-07 10:59:16.591727  ---> Is the SQL statement execution time correct?
Optimize Mode  : COST_BASE_2
SQL Opt Level  : 0x00000420(1056) = "PRIOR_NEST_JOIN"(32),"RAPID_GROUPING"(1024)
Add Opt Level  : 0x00000003(3) = "COST_BASE_2"(1),"APPLY_HASH_JOIN"(2)
  ------------> Are the SQL optimization option and extended SQL optimization option optimum?#1
SQL            : select T1.C1,T1.C2 from T1,T2,T3
                     where T1.C1='a' and T1.C2 like '%A'
                       and T1.C1=T2.C1 and T1.C2=T2.C2 and T2.C3>=1995
                       and T1.C1=T3.C1 and T1.C2=T3.C2 order by T1.C2
                                              --> Is the SQL statement correct?
Work Table     : 3
Total Cost     : 3314.567244
----- QUERY ID : 1 -----
Query Type     : QUERY LIST(SORT) {LLID1(2)}
Order by Mode  : FLOATABLE SORT
FLT Server     : 2 (ORDER BY)  2-CLM 1TO1
JOIN
 # Join  ID    : 1   ----------------------------> Are the tables joined in the expected order?
   L Table     :  T1
   R Table     :  T2
   Join Type   : 2-CLM NESTED LOOPS JOIN(INNER) BROADCAST(FROM L TO R)
 -------------> Is it possible to change the inner table's partitioning key to something other than BROADCAST?#2
   FLT Server  : 0
 # Join  ID    : 2
   L Table     :  T3
   R Join ID   :  1 LIST{JRLST(1)}
   Join Type   : 2-CLM HASH JOIN(INNER) 1-CLM HASH(FROM R TO L)
 -------------> Is this the expected join method?#3
   FLT Server  : 0
 
SCAN
 # Table Name  : T1
   Cost        : N (10000000ROW) {T-2962.358541,I-763.682244,AND-766.202330}
   RDAREA      : NON DIVISION (1RD/1BES) [0x07(7)] ALL
   Scan Type   : MULTI COLUMNS KEY SCAN
   Index Name  : X1 (2) (+C2,+C1)   --------------> Is this the expected index?
                     SearchCnd : RANGE(CS-CE) [(MIN,'a    '),(MAX,'a    ')] (FULL SCAN)
                     -----------------------------> Is the search range narrowed?#4
                     KeyCnd    : T1.C1='a    ' AND T1.C2 like '%A'
 # Table Name  : T2
   Cost        : N (20000000ROW) {T-1388.652368,I-408.252884,AND-2647.479838}
   RDAREA      : 1-CLM FIX HASH(HASH6) (2RD/2BES) [0x07(7),0x0d(13)] ALL
   Scan Type   : MULTI COLUMNS KEY SCAN
   Index Name  : X2 (3) (+C1,+C2,+C3)
                     SearchCnd : RANGE(CS-CE) [(T1.C1,T1.C2,1995),(T1.C1,T1.C2,MAX)]
 # Table Name  : T3
   Cost        : N (20000000ROW) {T-1621.172368}
   RDAREA      : 1-CLM FIX HASH(HASH6)(4RD/2BES)[0x07(7),0x08(8),0x0d(13),0x0e(14)]ALL
   Scan Type   : TABLE SCAN   ------------------> Is this the expected scan type?
 

Note

This output result is partially edited; it is not the actual display example.

#1: Check to see if the specified SQL optimization option and extended SQL optimization option are optimum to the database status. For details about the SQL optimization options and extended SQL optimization options, see the HiRDB Version 9 UAP Development Guide.

#2: If the transfer method is BROADCAST, KEY RANGE PARTIAL BROADCAST, or PARTIAL BROADCAST during the nested loop join, performance may improve if the inner table's partitioning key subject to nested loop join is changed to something other than BROADCAST, KEY RANGE PARTIAL BROADCAST, or PARTIAL BROADCAST.

For details, see 17.6.4(1) Nested loop join using a partitioning key.

Before change:

# Join ID : 1 L Table : T1 R Table : T2 Join Type : 2-CLM NESTED LOOPS JOIN(INNER) BROADCAST(FROM L TO R) FLT Server : 0

Changing table T2's partitioning key to C1 (join key).

After change:

# Join ID : 1 L Table : T1 R Table : T2 Join Type : 2-CLM NESTED LOOPS JOIN(INNER) 1-CLM HASH(FROM L TO R) FLT Server : 0

Use the column with the most even data distribution as the partitioning key. If there is no such column or multiple columns are joined, select more than one joined column as the partitioning key in such a manner that data becomes fairly even.

#3: If the join result of T1 and T2 is narrowed down, you can improve the retrieval speed by applying a nested loop join to T3 and the join result of T1 and T2. Define multicolumn index T3 that contains all columns of T3 (C1, C2) from the join condition including the columns of T3 (T1.C1=T3.C1 and T1.C2=T3.C2). For details, see 17.6.2(2) Index definitions for a table used for join retrieval.

Before change:

# Join ID : 2 L Table : T3 R Join ID : 1 LIST{JRLST(1)} Join Type : 2-CLM HASH JOIN(INNER) 1-CLM HASH(FROM R TO L) FLT Server : 0 : : : # Table Name : T3 Cost : N (2000000000ROW) {T-1621.172368} RDAREA : 1-CLM FIX HASH (HASH6) (4RD/2BES) [...] ALL Scan Type : TABLE SCAN

Defining an index for T3(C1, C2)

After change:

# Join ID : 2 L Join ID : 1 R Table : T3 Join Type : 2-CLM NESTED LOOPS JOIN(INNER) 1-CLM HASH(FROM L TO R) FLT Server : 0 : : : # Table Name : T3 Cost : N (2000000000ROW) {T-1621.172368,T-1581.852884} RDAREA : 1-CLM FIX HASH (HASH6) (4RD/2BES) [...] ALL Scan Type : MULTI COLUMNS KEY SCAN Index Name : X3 (2D) (+C1,+C2) SearchCnd : AT [(T1.C1,T1,C2)]

#4: The utility searches the entire range of index because it cannot form the range of conditions for the first index component column. The utility reverses index component columns 1 and 2 because = is specified for the second index component column. For details, see 17.6.2(1) Index definitions.

Before change:

Index Name : X1 (2) (+C2,+C1) SearchCnd : RANGE(CS-CE) [(MIN,'a '),(MAX,'a ')] (FULL SCAN) KeyCnd : T1.C1='a ' AND T1.C2 like '%A'

Deleting the index for T1(C2, C1) and defining an index for T3(C1,C2)

After change:

Index Name : X1 (2) (+C1,+C2) SearchCnd : RANGE(CS-CE) [('a ',MIN),('a '),MAX] KeyCnd : T1.C2 like '%A'