Performance Tuning: Diagnosing Why an Index Wasn't Used

Built on Oracle Autonomous Database with a 500,000-row SALES_ORDERS table linked to CUSTOMERS and PRODUCTS. A query filtering by customer_id and recent order_date ran a Full Table Scan despite a matching composite index. EXPLAIN PLAN analysis showed that forcing the index was actually costlier (654 vs 619), so the optimizer was right. Root cause: a high Clustering Factor, as randomly inserted rows scattered each customer's data. I rebuilt the table physically ordered by customer_id and order_date using CTAS, after which the optimizer chose an INDEX RANGE SCAN automatically at far lower cost.