[Solved] Can this oracle query be tuned anymore?

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 24): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `FIRST_NAME` is indexed, the index won’t be used as it’s wrapped with the function `UPPER`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
  2. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  4. Use UNION ALL instead of UNION (query line: 26): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
CREATE INDEX customers_idx_customer_id ON CUSTOMERS (CUSTOMER_ID);
CREATE INDEX orders_idx_customer_id ON ORDERS (CUSTOMER_ID);
CREATE INDEX order_lines_idx_order_id ON ORDER_LINES (ORDER_ID);
CREATE INDEX products_idx_product_id ON PRODUCTS (PRODUCT_ID);
The optimized query:
SELECT
        ol_product_id 
    FROM
        ((SELECT
            DISTINCT OL.PRODUCT_ID AS ol_product_id 
        FROM
            ORDERS O 
        JOIN
            ORDER_LINES OL 
                ON (
                    O.ORDER_ID = OL.ORDER_ID
                ) 
        JOIN
            PRODUCTS P 
                ON (
                    OL.PRODUCT_ID = P.PRODUCT_ID
                ) 
        JOIN
            CUSTOMERS C 
                ON (
                    C.CUSTOMER_ID = O.CUSTOMER_ID
                ) 
        WHERE
            UPPER(C.FIRST_NAME) = 'EDGAR') 
    UNION
    DISTINCT (SELECT
        DISTINCT OL.PRODUCT_ID AS ol_product_id 
    FROM
        ORDERS O 
    JOIN
        ORDER_LINES OL 
            ON (O.ORDER_ID = OL.ORDER_ID) 
    JOIN
        PRODUCTS P 
            ON (OL.PRODUCT_ID = P.PRODUCT_ID) 
    JOIN
        CUSTOMERS C 
            ON (C.CUSTOMER_ID = O.CUSTOMER_ID) 
    WHERE
        C.CUSTOMER_ID = 474871)
) AS union1

Related Articles



* original question posted on StackOverflow here.