As a starting point, place a random alias in the center of the diagram under construction. For illustration purposes, I’ll begin with the node O. Draw arrows downward from that node to any nodes that join to O through their primary key (named, for all these tables, by the same name as the table, with the s at the end replaced by _ID). Draw a downward-pointing arrow from any alias to O for any join that joins to O on the Orders table’s primary key, Order_ID. The beginning of the query skeleton should look like Figure B-1.

Figure B-1. The beginning of the query skeleton

Now, shift focus to OD. Find joins from that node, and add those links to the join skeleton. The result is shown in Figure B-2.

Figure B-2. An intermediate stage of the query skeleton

Find undiagramed join conditions. The only one left is S.Address_ID = A.Address_ID, so add a link for that join to complete the query skeleton, as shown in Figure B-3.

Figure B-3. The completed query skeleton

To build the simplified query diagram, find the most selective filter and identify it with an underlined F next to the filtered node. The condition on the customer’s phone number is almost certainly the most selective filter. Add a small underlined f for the only other filter, the much less selective condition on Business_Unit_ID for Orders. The result, shown in Figure B-4, is the simplified query diagram.

Figure B-4. The simplified query diagram

The simplified query diagram is sufficient to tune this particular query. However, for purposes of illustration, I will show the creation of a full query diagram, with all the details. Use the following queries to gather statistics necessary for a full query diagram. The results I’m using for this example are shown following each query. As an exercise, you might wish to work out the filter and join ratios for yourself.

Q1:  SELECT SUM(COUNT(Phone_Number)*COUNT(Phone_Number))/
                            (SUM(COUNT(Phone_Number))*SUM(COUNT(*))) A1
                    FROM Customers 
                    GROUP BY Phone_Number;
A1:  0.000003

Q2:  SELECT COUNT(*) A2 FROM Customers;
A2:  500,000

Q3:  SELECT SUM(COUNT(Business_Unit_ID)*COUNT(Business_Unit_ID))/
                            (SUM(COUNT(Business_Unit_ID))*SUM(COUNT(*))) A3
                    FROM Orders 
                    GROUP BY Business_Unit_ID;
A3:  0.2

Q4:  SELECT COUNT(*) A4 FROM Orders;
A4:  400,000

Q5:  SELECT COUNT(*) A5 
                    FROM Orders O, Customers C 
                    WHERE O.Customer_ID = C.Customer_ID;
A5:  400,000

Q6:  SELECT COUNT(*) A6 FROM Order_Details;
A6:  1,200,000

Q7:  SELECT COUNT(*) A7 
                    FROM Orders O, Order_Details OD 
                    WHERE OD.Order_ID = O.Order_ID;
A7:  1,2000,000

Q8:  SELECT COUNT(*) A8 FROM Shipments;
A8:  540,000

Q9:  SELECT COUNT(*) A9
                    FROM Shipments S, Order_Details OD 
                    WHERE OD.Shipment_ID = S.Shipment_ID;
A9:  1,080,000

Q10: SELECT COUNT(*) A10 FROM Products;
A10: 12,000

Q11: SELECT COUNT(*) A11
                    FROM Products P, Order_Details OD 
                    WHERE OD.Product_ID = P.Product_ID;
A11: 1,200,000

Q12: SELECT COUNT(*) A12 FROM Addresses;
A12: 135,000

Q13: SELECT COUNT(*) A13
                    FROM Addresses A, Shipments S 
                    WHERE S.Address_ID = A.Address_ID;
A13: 540,000

Beginning with filter ratios, get the weighted-average filter ratio for the condition on Customers Phone_Number directly from A1, which is the result from query Q1 (0.000003). Find the filter ratio on Orders the same way, from Q3, which returns the result of 0.2 for A3.

Since no other alias has any filters, the filter ratios on the other four are 1.0, which you imply by just leaving filter ratios off the query diagram for the other nodes.

For each join, find the detail join ratio, to place alongside the upper end of each join arrow, by dividing the count on the join of the two tables by the count on the lower table (the master table of that master-detail relationship). The ratios for the upper ends of the joins from OD to S, O, and P are 2 (A9/A8), 3 (A7/A4), and 100 (A11/A10), respectively. The ratio for the upper end of the join from S to A is 4 (A13/A12). The ratio for the upper ends of the join from O to C is 0.8 (A5/A2).

Find the master join ratios, to place alongside the lower end of each join arrow, by dividing the count on the join of the two tables by the count on each upper table (the detail table of a master-detail relationship). The ratio for the lower end of the join from OD to S is 0.9 (A9/A6). All the other master join ratios turn out to be 1.0, so leave these off the diagram.

Add filter ratios and join ratios to the query skeleton (see Figure B-3) to create the full query diagram, as shown in Figure B-5.

Figure B-5. The full query diagram

Place the SQL in a file named tmp.sql and run the script ex.sql, as described in Chapter 3. The result is as follows:

PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
  SORT ORDER BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              TABLE ACCESS FULL 4*CUSTOMERS
              TABLE ACCESS BY INDEX ROWID 1*ORDERS
                INDEX RANGE SCAN ORDER_CUSTOMER_ID
            TABLE ACCESS BY INDEX ROWID 2*ORDER_DETAILS
              INDEX RANGE SCAN ORDER_DETAIL_ORDER_ID
          TABLE ACCESS BY INDEX ROWID 5*SHIPMENTS
            INDEX UNIQUE SCAN SHIPMENT_PKEY
        TABLE ACCESS BY INDEX ROWID 6*ADDRESSES
          INDEX UNIQUE SCAN ADDRESS_PKEY
      TABLE ACCESS BY INDEX ROWID 3*PRODUCTS
        INDEX UNIQUE SCAN PRODUCT_PKEY

You notice that your database is set up to use the rule-based optimizer, so you switch to cost-based optimization, check that you have statistics on the tables and indexes, and check the plan again, finding a new result:

PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
  SORT ORDER BY
    HASH JOIN
      TABLE ACCESS FULL 3*PRODUCTS
      HASH JOIN
        HASH JOIN
          HASH JOIN
            HASH JOIN
              TABLE ACCESS FULL 4*CUSTOMERS
              TABLE ACCESS FULL 1*ORDERS
            TABLE ACCESS FULL 2*ORDER_DETAILS
          TABLE ACCESS FULL 5*SHIPMENTS
        TABLE ACCESS FULL 6*ADDRESSES

Neither execution plan is close to the optimum plan. Instead, both the rule-based and the cost-based optimization plans drive from full table scans of large tables. The database ought to reach the driving table on a highly selective index, so you know that an improvement is certainly both necessary and possible.

Place the SQL in tmp.sql and run the following command according to the process described in Chapter 3:

cat head.sql tmp.sql tail.sql | db2 +c +p -t

The result is an error; DB2 complains that it sees inconsistent column types in the condition on Phone_Number. You discover that the Phone_Number column is of the VARCHAR type, which is incompatible with the number type of the constant 6505551212.

Unlike Oracle, DB2 does not implicitly convert character-type columns to numbers when SQL compares inconsistent datatypes. This is just as well, in this case, since such a conversion might deactivate an index on Phone_Number, if there is one. You might even suspect, already, that this is precisely what has caused poor performance in the Oracle baseline development environment.

You fix the problem in the most obvious way, placing quotes around the phone number constant to make it a character type:

SELECT C.Phone_Number, C.Honorific, C.First_Name, C.Last_Name, C.Suffix, 
C.Address_ID, A.Address_ID, A.Street_Addr_Line1, A.Street_Addr_Line2, 
A.City_Name, A.State_Abbreviation, A.ZIP_Code, OD.Deferred_Ship_Date, 
OD.Item_Count, P.Prod_Description, S.Shipment_Date 
FROM Orders O, Order_Details OD, Products P, Customers C, Shipments S, 
Addresses A
WHERE OD.Order_ID = O.Order_ID
AND O.Customer_ID = C.Customer_ID
AND OD.Product_ID = P.Product_ID
AND OD.Shipment_ID = S.Shipment_ID
AND S.Address_ID = A.Address_ID
AND C.Phone_Number = '6505551212'
AND O.Business_Unit_ID = 10
ORDER BY C.Customer_ID, O.Order_ID Desc, S.Shipment_ID, OD.Order_Detail_ID;

Placing this new version of the SQL in tmp.sql, you again attempt to get the execution plan:

$cat head.sql tmp.sql tail.sql | db2 +c +p -t
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

OPERATOR_ID TARGET_ID OPERATOR_TYPE OBJECT_NAME        COST
----------- --------- ------------- ------------------ -----------
          1         - RETURN        -                          260
          2         1 NLJOIN        -                          260
          3         2 NLJOIN        -                          235
          4         3 NLJOIN        -                          210
          5         4 TBSCAN        -                          185
          6         5 SORT          -                          185
          7         6 NLJOIN        -                          185
          8         7 NLJOIN        -                          135
          9         8 FETCH         CUSTOMERS                   75
         10         9 IXSCAN        CUST_PH_NUMBER              50
         11         8 FETCH         ORDERS                      70
         12        11 IXSCAN        ORDER_CUST_ID               50
         13         7 FETCH         ORDER_DETAILS               75
         14        13 IXSCAN        ORDER_DTL_ORD_ID            50
         15         4 FETCH         PRODUCTS                    50
         16        15 IXSCAN        PRODUCT_PKEY                25
         17         3 FETCH         SHIPMENTS                   75
         18        17 IXSCAN        SHIPMENT_PKEY               50
         19         2 FETCH         ADDRESSES                   75
         20        19 IXSCAN        ADDRESS_PKEY                50

  20 record(s) selected.

DB20000I  The SQL command completed successfully.
$

That’s more like it, just the execution plan you chose when you analyzed the SQL top-down, except for the minor issue of reaching Products before Shipments, which will have virtually no effect on the runtime. Since the type inconsistency involving Phone_Number might require correcting on SQL Server and Oracle, you need to try this modified version immediately on the other databases.

Suspecting that you already have the solution to slow performance for this query, you fire up SQL Server’s Query Analyzer and use set showplan_text on to see a concise view of the execution plan of the statement modified with C.Phone_Number = '6505551212' to correct the type inconsistency. A click on the Query Analyzer’s Execute-Query button results in the following output:

StmtText                                        
-------------------------------------------------------------------------------
  |--Bookmark Lookup(...(...[Products] AS [P]))
       |--Nested Loops(Inner Join)
            |--Bookmark Lookup(...(...[Addresses] AS [A]))
            |    |--Nested Loops(Inner Join)
            |         |--Sort(ORDER BY:([O].[Customer_ID] ASC, [O].[Order_ID] DESC,(wrapped line) [OD].[Shipment_ID] ASC, [OD].[Order_Detail_ID] ASC))
            |         |    |--Bookmark Lookup(...(...[Shipments] AS [S]))
            |         |         |--Nested Loops(Inner Join)
            |         |              |--Bookmark Lookup(...(...[Order_Details] AS 
[OD]))
            |         |              |    |--Nested Loops(Inner Join)
            |         |              |         |--Filter(WHERE:([O].[Business_Unit_
ID]=10))
            |         |              |         |    |--Bookmark Lookup(...(...
[Orders] AS [O]))
            |         |              |         |         |--Nested Loops(Inner Join)
            |         |              |         |              |--Bookmark Lookup(...
(...
(wrapped line) [Customers] AS [C]))
            |         |              |         |              |    |--Index Seek(...
(...
(wrapped line) [Customers].[Customer_Phone_Number] 
(wrapped line) AS [C]), SEEK:([C].[Phone_Number]='6505551212') ORDERED)
            |         |              |         |              |--Index Seek(...(...
(wrapped line) [Orders].[Order_Customer_ID] AS [O]), 
(wrapped line) SEEK:([O].[Customer_ID]=[C].[Customer_ID]) ORDERED)
            |         |              |         |--Index Seek(...(...
(wrapped line) [Order_Details].[Order_Detail_Order_ID] 
(wrapped line) AS [OD]), SEEK:([OD].[Order_ID]=[O].[Order_ID]) ORDERED)
            |         |              |--Index Seek(...(...[Shipments].[Shipment_PKey] 
(wrapped line) AS [S]), SEEK:([S].[Shipment_ID]=[OD].[Shipment_ID]) ORDERED)
            |         |--Index Seek(...(...[Addresses].[Address_PKey] 
(wrapped line) AS [A]), SEEK:([A].[Address_ID]=[S].[Address_ID]) ORDERED)
            |--Index Seek(...(...[Products].[Product_PKey] 
(wrapped line) AS [P]), SEEK:([P].[Product_ID]=[OD].[Product_ID]) ORDERED)

(19 row(s) affected)

Good news! The corrected SQL leads to exactly the optimum plan here. Just out of curiosity, you check the execution plan for the original SQL, and you find the same result! Evidently, SQL Server is doing the data conversion on the constant, avoiding disabling the index.