SQL Tuning [Electronic resources] نسخه متنی

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