Search code examples
sqloraclejoinquery-optimization

what to consider before creating index?

I have the following query: 

SELECT OEH.HEADER_ID, OEH.ORDER_NUMBER
                ,oel.line_number
                ,oel.pricing_quantity_uom as UOM
                ,oel.attribute1 as customer_length
                ,oel.attribute6 as theoretical_weight
FROM OE_ORDER_HEADERS_ALL OEH JOIN
     OE_ORDER_LINES_ALL OEL
     ON OEH.ORG_ID = OEL.ORG_ID AND
        OEH.HEADER_ID = OEL.HEADER_ID
WHERE OEL.LINK_TO_LINE_ID IS NULL;

I am facing Nested loops and want to tune this query, on what columns I need to create an index?

Solution

  • A convenient rule is to index the columns used in the JOINs: OE_ORDER_HEADERS_ALL(ORG_ID, HEADER_ID) and OE_ORDER_LINES_ALL(ORG_ID, HEADER_ID).

    In fact, Oracle will probably use only one of these for the JOIN, but it will choose the better one.