B.5 Altering the SQL to Enable the Best Plan

You already suspect that the solution to getting a good plan on Oracle is to eliminate the type inconsistency on that platform. After all, the other databases avoided the type conversion on the indexed column and delivered a good plan. Therefore, immediately try the query again on Oracle, but with the corrected comparison C.Phone_Number = '6505551212' to avoid the implicit datatype conversion. Use the original setting for rule-based optimization to check the execution plan:

PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
  SORT ORDER BY
    NESTED LOOPS
      NESTED LOOPS
        NESTED LOOPS
          NESTED LOOPS
            NESTED LOOPS
              TABLE ACCESS BY INDEX ROWID 4*CUSTOMERS
                INDEX RANGE SCAN CUSTOMER_PHONE_NUMBER
              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 3*PRODUCTS
          INDEX UNIQUE SCAN PRODUCT_PKEY
      TABLE ACCESS BY INDEX ROWID 6*ADDRESSES
        INDEX UNIQUE SCAN ADDRESS_PKEY

This is precisely the execution plan you want. Suspecting that the application will soon switch to cost-based optimization, you check the cost-based execution plan, and it turns out to be the same.

Both Oracle optimizers now return the optimal plan, so you should be done! To verify this, you run the SQL with the sqlplus option set timing on and find that Oracle returns the result in just 40 milliseconds, compared to the earlier performance of 2.4 seconds for the original rule-based execution plan and 8.7 seconds for the original cost-based execution plan