SQL Query optimization

Query Optimization is very important while in development and also when project grows big we have optimize the existing scripts.

Below are some best practises to optimize the Query

• Tables must have proper joins
• Ensure the joins should be with primary key
• Group by and Order by clause should be used only when needed
• Use only IN clause and subquery when needed
• Avoid Select DISTINCT
• Avoid Select * from
• Use Index when needed

Here we will demonstrate Index with a use case

explain plan for 
SELECT DISTINCT
        ooh.creation_date    order_creation_date,
        CASE
            WHEN wdd.source_header_type_name = 'INTERNAL' THEN
                'INTERNAL'
            ELSE
                'WHOLESALE'
        END                  order_type,
        ooh.order_number,
        ooh.cust_po_number,
        hp.party_name        customer_name,
        hl.address1
        || '-'
        || hl.address2
        || '-'
        || hl.address3       customer_site,
        ooh.booked_date      booked_date,
--        ooh.booked_date,
        ooh.flow_status_code order_status,
        CASE
            WHEN released_flag = 'Y' THEN
                'RELEASED'
            WHEN released_flag = 'N' THEN
                'HOLD'
            ELSE
                'OPEN'
        END                  hold_status,
        CASE
            WHEN released_flag = 'Y' THEN
                oohl.last_update_date
            ELSE
                NULL
        END                  hold_release_date,
        CASE
            WHEN wdd.released_status = 'C' THEN
                'INVOICED'
            WHEN wdd.released_status = 'R' THEN
                'READY TO RELEASE'
            WHEN wdd.released_status = 'Y' THEN
                'STAGED - AWAITING SHIP CONFIRM'
            ELSE
                'RELEASED TO WAREHOUSE'
        END                  ship_status,
        wnd.name             delivery_number,
        ool.ship_from_org_id organization_id,
        ooh.header_id,
        ooh.sold_to_org_id,
        hp.party_id
    FROM
        apps.oe_order_headers_all ooh,
        ar.hz_cust_accounts       hca,
        ar.hz_parties             hp,
        hz_party_sites            hps,
        hz_cust_acct_sites_all    hcasa,
        hz_cust_site_uses_all     hcsua,
        hz_locations              hl,
        (select header_id,  RELEASED_FLAG , max(LAST_UPDATE_DATE) LAST_UPDATE_DATE from oe_order_holds_all  group by header_id,  RELEASED_FLAG)   oohl, --Changes done by 4iapps on 02-May-2024 to restrict Line and Header Order hold Duplicates
        wsh_new_deliveries        wnd,
        wsh_delivery_assignments  wda,
        wsh_delivery_details      wdd,
        apps.oe_order_lines_all   ool
    WHERE
            1 = 1
        AND ooh.sold_to_org_id = hca.cust_account_id
        AND hca.party_id = hp.party_id
        AND oohl.header_id (+) = ooh.header_id
        AND hps.party_id = hp.party_id
        AND hcasa.party_site_id = hps.party_site_id
        AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
        AND ooh.ship_to_org_id = hcsua.site_use_id
        AND hps.location_id = hl.location_id (+)
        AND ooh.header_id = wdd.source_header_id (+)
        AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
        AND ool.line_id = wdd.source_line_id (+)
        AND wdd.released_status NOT IN ( 'B', 'D' )
        AND wda.delivery_id = wnd.delivery_id (+)
        AND ooh.header_id = ool.header_id
        AND hps.location_id = hps.location_id
        AND hps.party_site_id = hps.party_site_id
        AND hps.party_site_number = hps.party_site_number
        AND ooh.header_id = ooh.header_id
    GROUP BY
        ooh.creation_date,
        wdd.source_header_type_name,
        ooh.order_number,
        ooh.cust_po_number,
        hp.party_name,
        hl.address1
        || '-'
        || hl.address2
        || '-'
        || hl.address3,
        ooh.booked_date,
        ooh.flow_status_code,
        released_flag,
        wnd.name,
        oohl.last_update_date,
        wdd.released_status,
        ool.ship_from_org_id,
        ooh.header_id,
        ooh.sold_to_org_id,
        hp.party_id
        order by ooh.header_id desc;

Once the above code is run then execute the below query

SELECT * FROM TABLE(dbms_xplan.display);

The above query gives us the cost of the query and if we observe, on line 21 Table access is full.

So we first create a Index on OE_ORDER_HEADERS_all based on the joins. As joins are proper and required we are creating index.

create index ont.XX_OE_ORDER_HDRS_N1 on ont.OE_ORDER_HEADERS_all (header_id,ship_to_org_id,sold_to_org_id,booked_date) nologging;

explain plan for 
SELECT DISTINCT
        ooh.creation_date    order_creation_date,
        CASE
            WHEN wdd.source_header_type_name = 'INTERNAL' THEN
                'INTERNAL'
            ELSE
                'WHOLESALE'
        END                  order_type,
        ooh.order_number,
        ooh.cust_po_number,
        hp.party_name        customer_name,
        hl.address1
        || '-'
        || hl.address2
        || '-'
        || hl.address3       customer_site,
        ooh.booked_date      booked_date,
--        ooh.booked_date,
        ooh.flow_status_code order_status,
        CASE
            WHEN released_flag = 'Y' THEN
                'RELEASED'
            WHEN released_flag = 'N' THEN
                'HOLD'
            ELSE
                'OPEN'
        END                  hold_status,
        CASE
            WHEN released_flag = 'Y' THEN
                oohl.last_update_date
            ELSE
                NULL
        END                  hold_release_date,
        CASE
            WHEN wdd.released_status = 'C' THEN
                'INVOICED'
            WHEN wdd.released_status = 'R' THEN
                'READY TO RELEASE'
            WHEN wdd.released_status = 'Y' THEN
                'STAGED - AWAITING SHIP CONFIRM'
            ELSE
                'RELEASED TO WAREHOUSE'
        END                  ship_status,
        wnd.name             delivery_number,
        ool.ship_from_org_id organization_id,
        ooh.header_id,
        ooh.sold_to_org_id,
        hp.party_id
    FROM
        apps.oe_order_headers_all ooh,
        ar.hz_cust_accounts       hca,
        ar.hz_parties             hp,
        hz_party_sites            hps,
        hz_cust_acct_sites_all    hcasa,
        hz_cust_site_uses_all     hcsua,
        hz_locations              hl,
        (select header_id,  RELEASED_FLAG , max(LAST_UPDATE_DATE) LAST_UPDATE_DATE from oe_order_holds_all  group by header_id,  RELEASED_FLAG)   oohl, --Changes done by 4iapps on 02-May-2024 to restrict Line and Header Order hold Duplicates
        wsh_new_deliveries        wnd,
        wsh_delivery_assignments  wda,
        wsh_delivery_details      wdd,
        apps.oe_order_lines_all   ool
    WHERE
            1 = 1
        AND ooh.sold_to_org_id = hca.cust_account_id
        and hca.cust_account_id = hca.cust_account_id+0
        AND hca.party_id = hp.party_id
        and hp.party_id=hp.party_id+0
        AND oohl.header_id (+) = ooh.header_id
        AND hps.party_id = hp.party_id
        AND hcasa.party_site_id = hps.party_site_id
        AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
        AND ooh.ship_to_org_id = hcsua.site_use_id
        AND hps.location_id = hl.location_id (+)
        AND ooh.header_id = wdd.source_header_id (+)
        AND wdd.delivery_detail_id = wda.delivery_detail_id (+)
        AND ool.line_id = wdd.source_line_id (+)
        AND wdd.released_status NOT IN ( 'B', 'D' )
        AND wda.delivery_id = wnd.delivery_id (+)
        AND ooh.header_id = ool.header_id
        AND hps.location_id = hps.location_id
        AND hps.party_site_id = hps.party_site_id
        AND hps.party_site_number = hps.party_site_number
        AND ooh.header_id = ooh.header_id
    GROUP BY
        ooh.creation_date,
        wdd.source_header_type_name,
        ooh.order_number,
        ooh.cust_po_number,
        hp.party_name,
        hl.address1
        || '-'
        || hl.address2
        || '-'
        || hl.address3,
        ooh.booked_date,
        ooh.flow_status_code,
        released_flag,
        wnd.name,
        oohl.last_update_date,
        wdd.released_status,
        ool.ship_from_org_id,
        ooh.header_id,
        ooh.sold_to_org_id,
        hp.party_id
        order by ooh.header_id desc;

After creating Index and forcing Index as attached in the above query now while running plan gives the below result.

SELECT * FROM TABLE(dbms_xplan.display);

We observe a drastic change in costing. But this way the query can be optmized