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