1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

please tell me what steps should I perform to optimize sql request

Discussion in 'SQL PL/SQL' started by ecivgamer, Nov 11, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,



    Please tell me what steps should I perform to optimize sql request (shown below with explain plan).

    Thanks ahead.



    SELECT

    /* SCR 11989 - removed ordered */

    /* selectVirtualRadarLoads */

    lsd.load_id,

    lsd.shipper_reference_number,

    loads.carrier_reference_number,

    load_status_types.load_status_order as load_status,

    lsd.org_id org_id,

    org_shippers.name org_name,

    lsd.location_id location_id,

    loads.priority,

    lsd.orig_city,

    lsd.orig_state,

    lsd.orig_country,

    lsd.orig_zip,

    lsd.dest_city,

    lsd.dest_state,

    lsd.dest_country,

    lsd.dest_zip,

    lsd.orig_scheduled_arrival last_possible_pickup,

    lsd.orig_scheduled_arrival_tz last_possible_pickup_tz,

    lsd.dest_scheduled_arrival target_delivery,

    lsd.dest_scheduled_arrival_tz target_delivery_tz,

    org_carriers.scac,

    org_carriers.name carrierName,

    lsd.orig_departure actual_pickup,

    lsd.orig_departure_tz actual_pickup_tz,

    lsd.dest_departure actual_delivery,

    lsd.dest_departure_tz actual_delivery_tz,

    log.event,

    log.eventlog event_log_id,

    events.description event_description,

    events.priority event_priority,

    loads.etd_date estimated_delivery,

    loads.etd_date_tz estimated_delivery_tz,

    lsd.carrier_person_id,

    lsd.carrier_loc_id,

    loads.after_hours_contact,

    loads.after_hours_phone,

    loads.driver_name,

    loads.tractor_id,

    loads.trailer,

    loads.pickup_num,

    destination_details.sdsa_flag dsrd,

    loads.booked,

    loads.frt_bill_amount,

    loads.frt_bill_recv_date,

    loads.frt_bill_number,

    loads.finalization_status,

    flatbed.get_scheduled_at( lsd.load_id ) scheduled_date,

    flatbed.get_scheduled_at_tz( lsd.load_id ) scheduled_date_tz,

    (

    SELECT contact

    FROM load_details

    WHERE point_type='O'

    AND load_details.load_id = lsd.load_id

    )

    orig_contact,

    destination_details.contact dest_contact,

    (

    SELECT first_name || ' ' || last_name

    FROM flatbed.users u,

    flatbed.user_customer uc

    WHERE uc.org_id = lsd.org_id

    AND uc.location_id = lsd.location_id

    AND uc.person_id = u.person_id

    AND uc.status = 'A'

    AND nvl( loads.award_date, LSD.ORIG_SCHEDULED_ARRIVAL ) >= uc.eff_date

    AND nvl( loads.award_date, lsd.orig_scheduled_arrival ) < uc.exp_date

    )

    customer_owner,

    /* Paperwork Required */

    CASE

    WHEN (

    SELECT CAR.PAPERWORK_REQ_IND

    FROM CARRIERS CAR

    WHERE CAR.ORG_ID = ORG_CARRIERS.ORG_ID

    AND CAR.NETWORK_ID = ORG_SHIPPERS.NETWORK_ID

    )

    = 'N' THEN 0

    ELSE

    CASE

    WHEN (

    SELECT COUNT(*)

    FROM flatbed.ORGANIZATION_REQ_DOC REQ_DOC

    WHERE REQ_DOC.ORG_ID = ORG_SHIPPERS.ORG_ID

    AND REQ_DOC.STATUS = 'A'

    AND (REQ_DOC.LOCATION_ID = -1

    OR REQ_DOC.LOCATION_ID = LOADS.LOCATION_ID)

    AND (REQ_DOC.EFF_DATE IS NULL

    OR REQ_DOC.EFF_DATE <= TO_DATE(TO_CHAR((

    CASE

    WHEN (

    SELECT LDE.DEPARTURE

    FROM LOAD_DETAILS LDE

    WHERE LDE.LOAD_ID = LOADS.LOAD_ID

    AND LDE.POINT_TYPE='O'

    )

    IS NOT NULL THEN

    (

    SELECT LDE.DEPARTURE

    FROM LOAD_DETAILS LDE

    WHERE LDE.LOAD_ID = LOADS.LOAD_ID

    AND LDE.POINT_TYPE='O'

    )

    ELSE SYSDATE

    END), 'mm/dd/yyyy'), 'mm/dd/yyyy'))

    AND (REQ_DOC.EXP_DATE IS NULL

    OR REQ_DOC.EXP_DATE >= TO_DATE(TO_CHAR((

    CASE

    WHEN (

    SELECT LDE.DEPARTURE

    FROM LOAD_DETAILS LDE

    WHERE LDE.LOAD_ID = LOADS.LOAD_ID

    AND LDE.POINT_TYPE='O'

    )

    IS NOT NULL THEN

    (

    SELECT LDE.DEPARTURE

    FROM LOAD_DETAILS LDE

    WHERE LDE.LOAD_ID = LOADS.LOAD_ID

    AND LDE.POINT_TYPE='O'

    )

    ELSE SYSDATE

    END), 'mm/dd/yyyy'), 'mm/dd/yyyy'))

    )

    = 0 THEN 0

    ELSE 1

    END

    END as PAPERWORK_REQUIRED,

    /* End of Paperwork Required */

    loads.premium_available premiumAvailable,

    loads.shipper_premium_available shipperPremiumAvailable,

    '' as originSRD,

    loads.dispatcher_schedule_only dispatcherScheduleOnly,

    loads.hfr_flag holdForRelease,

    loads.radio_active_flag radioactive,

    loads.hazmat_flag hazmat,

    '' doNotShipBefore,

    '' doNotShipAfter,

    0 as numTenderRules,

    /*load_has_tenderrule( loads.load_id ) numTenderRules,*/

    (

    SELECT count(*)

    FROM flatbed.load_preferred_carriers

    WHERE load_preferred_carriers.load_id = loads.load_id

    )

    preferredCarrierCount,

    (

    SELECT ldDetails.early_scheduled_arrival

    FROM load_details ldDetails

    WHERE ldDetails.load_id = destination_details.load_id

    AND ldDetails.point_type = 'O'

    ) as pnet,

    (

    SELECT ldDetails.early_scheduled_arrival_tz

    FROM load_details ldDetails

    WHERE ldDetails.load_id = destination_details.load_id

    AND ldDetails.point_type = 'O'

    ) as pnet_tz,

    (

    SELECT ldDetails.early_scheduled_arrival

    FROM load_details ldDetails

    WHERE ldDetails.load_id = destination_details.load_id

    AND ldDetails.point_type = 'D'

    ) as dnet,

    (

    SELECT ldDetails.early_scheduled_arrival_tz

    FROM load_details ldDetails

    WHERE ldDetails.load_id = destination_details.load_id

    AND ldDetails.point_type = 'D'

    ) as dnet_tz,

    nvl

    (

    (

    SELECT count(*)

    FROM flatbed.TENDERING_RESULTS TR,

    flatbed.master_tendering_plan mtplan,

    flatbed.tendering_plan tplan

    WHERE tr.plan_id = tplan.plan_id

    AND mtplan.load_id = lsd.load_id

    AND tplan.master_tender_plan_id = mtplan.master_tender_plan_id

    AND mtplan.status = 'A'

    AND tplan.status = 'P'

    AND lsd.load_status = 'T'

    AND sysdate BETWEEN tplan.start_time AND tplan.end_time

    AND tr.response_code in ('Y', 'O', 'U', 'D')

    AND ( tplan.rate_id is null

    AND tplan.dedicated_program_id is null

    AND tplan.dedicated_unit_id is null )

    )

    , 0

    ) as offerCount,

    /* Accelerated Pay Terms */

    CASE

    WHEN (

    SELECT count(*)

    FROM flatbed.AP_TERMS APT,

    flatbed.CARRIERS CAR

    WHERE CAR.ORG_ID = ORG_CARRIERS.ORG_ID

    AND CAR.NETWORK_ID = ORG_SHIPPERS.NETWORK_ID

    AND CAR.TERM_ID = APT.TERM_ID

    AND APT.NAME like '%ACCEL%'

    )

    > 0 THEN 1

    ELSE 0

    END as accel_pay_terms,

    loads.container_cd,

    /* End of Accelerated Pay Terms */

    CASE

    WHEN (

    SELECT count(cc.carrier_capacity_id)

    FROM flatbed.carrier_capacity cc

    WHERE cc.container_cd = loads.container_cd

    AND cc.status = 'A'

    AND ((destination_details.point_type = 'O'

    AND (destination_details.early_scheduled_arrival is null

    OR destination_details.early_scheduled_arrival <= cc.avail_end_date))

    OR (lsd.orig_scheduled_arrival >= cc.avail_start_date))

    AND ((cc.orig_state = lsd.orig_state

    /* Include 150 mile radius */ )

    OR (lsd.orig_state in

    (

    SELECT ze.state_code

    FROM flatbed.zone_elements ze

    WHERE ze.zone_id = cc.orig_zone_id

    )))

    )

    >= 0 THEN 1

    ELSE 0

    END as capacityMatched,

    loads.pay_terms payTerms,

    loads.bol,

    /*SCR:6283 For Shipper on Credit Hold */

    CASE

    WHEN (LOADS.LOAD_STATUS = 'PO'

    OR LOADS.LOAD_STATUS = 'T') THEN

    CASE

    WHEN (

    SELECT CREDIT_LIMIT_REQUIRED

    FROM NETWORKS NW

    WHERE NW.NETWORK_ID = ORG_SHIPPERS.NETWORK_ID

    )

    = 'Y' THEN

    CASE

    WHEN (

    SELECT CREDIT_HOLD

    FROM BILL_TO BT

    WHERE LOADS.BILL_TO = BT.BILL_TO_ID

    )

    = 'Y' THEN 'Y'

    ELSE 'N'

    END

    ELSE

    CASE

    WHEN (ORG_SHIPPERS.STATUS = 'H'

    AND ORG_SHIPPERS.STATUS_REASON = 'CRHLD') THEN 'Y'

    ELSE 'N'

    END

    END

    ELSE 'N'

    END as shipperOnCreditHold

    /*end*/

    FROM

    /* from condition for account/customer owner or branch filters */

    flatbed.user_customer uc,

    /* end of from condition for account/customer owner or branch filters */

    load_search_data lsd

    JOIN load_status_types

    ON lsd.load_status = load_status_types.load_status

    JOIN loads

    ON lsd.load_id = loads.load_id

    JOIN load_details destination_details

    ON lsd.dest_load_detail_id = destination_details.load_detail_id

    JOIN organizations org_shippers

    ON lsd.org_id = org_shippers.org_id

    JOIN organizations org_carriers

    ON lsd.carrier_org_id = org_carriers.org_id

    LEFT OUTER JOIN

    (

    SELECT load_id,

    event,

    MAX(event_log_id) AS eventLog

    FROM flatbed.virtual_radar_event_log LASTCOMPLETE

    WHERE LASTCOMPLETE.date_closed is null

    GROUP BY LASTCOMPLETE.load_id,

    event

    )

    log

    ON ( lsd.load_id = log.load_id)

    LEFT OUTER JOIN flatbed.virtual_radar_events events

    ON log.event = events.event

    WHERE

    /* where condition for account/customer owner or branch filters */

    uc.org_id = lsd.org_id

    AND uc.location_id = lsd.location_id

    AND uc.status = 'A'

    AND

    /* end of where condition for account/customer owner or branch filters */

    /* where condition for account/customer owner filters */

    nvl( loads.award_date, LSD.ORIG_SCHEDULED_ARRIVAL ) >= uc.eff_date

    AND nvl( loads.award_date, lsd.orig_scheduled_arrival ) <= uc.exp_date

    AND

    /* end of where condition for account/customer owner filters */

    exists

    (

    SELECT 'X'

    FROM organizations

    WHERE organizations.org_id = lsd.ORG_ID

    AND organizations.network_id =

    (

    SELECT network_id

    FROM organizations

    WHERE org_id = '226371'

    )

    )

    AND LOADS.RADIO_ACTIVE_FLAG = 'N'

    AND (loads.radio_active_secure_flag = 'N'

    OR loads.radio_active_secure_flag IS NULL )

    AND nvl(loads.originating_system, 'PLS') not in ('PLS2_LT')

    AND ( ( load_status_types.load_status_order = '80' )

    AND ( uc.person_id = '217758' )

    OR ( lsd.load_id = '9603873' )

    AND ( ROWNUM <= 500 ) )

    ORDER BY orig_city ,

    event_priority,

    decode(loads.priority,'R',1,0) DESC,

    decode(destination_details.sdsa_flag,'N',1,0) DESC,

    load_id ;


    --------------------------------------------------------------------------

    | Id | Operation | Name |

    --------------------------------------------------------------------------

    | 0 | SELECT STATEMENT | |

    | 1 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 2 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 3 | NESTED LOOPS | |

    | 4 | TABLE ACCESS BY INDEX ROWID | USER_CUSTOMER |

    | 5 | INDEX RANGE SCAN | UC_COMP_IDX1 |

    | 6 | INDEX RANGE SCAN | USR_NAME_IDX |

    | 7 | TABLE ACCESS BY INDEX ROWID | CARRIERS |

    | 8 | INDEX UNIQUE SCAN | CAR_UK |

    | 9 | SORT AGGREGATE | |

    | 10 | FILTER | |

    | 11 | TABLE ACCESS BY INDEX ROWID | ORGANIZATION_REQ_DOC |

    | 12 | INDEX RANGE SCAN | ORG_REQ_DOC_ORG_FK_I |

    | 13 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 14 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 15 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 16 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 17 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 18 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 19 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 20 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 21 | SORT AGGREGATE | |

    | 22 | INDEX RANGE SCAN | LPC_LOD_FK_I |

    | 23 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 24 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 25 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 26 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 27 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 28 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 29 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 30 | INDEX RANGE SCAN | LDD_COMP_I5 |

    | 31 | SORT AGGREGATE | |

    | 32 | FILTER | |

    | 33 | TABLE ACCESS BY INDEX ROWID | TENDERING_RESULTS |

    | 34 | NESTED LOOPS | |

    | 35 | NESTED LOOPS | |

    | 36 | INDEX RANGE SCAN | MTP_LOAD_ID_IDX |

    | 37 | TABLE ACCESS BY INDEX ROWID | TENDERING_PLAN |

    | 38 | INDEX RANGE SCAN | TP_RECIPEINTS_IDX |

    | 39 | INDEX RANGE SCAN | TR_RECIPEINTS_IDX1 |

    | 40 | SORT AGGREGATE | |

    | 41 | NESTED LOOPS | |

    | 42 | TABLE ACCESS BY INDEX ROWID | CARRIERS |

    | 43 | INDEX UNIQUE SCAN | CAR_UK |

    | 44 | TABLE ACCESS BY INDEX ROWID | AP_TERMS |

    | 45 | INDEX RANGE SCAN | AP_TERMS_PK |

    | 46 | SORT AGGREGATE | |

    | 47 | FILTER | |

    | 48 | TABLE ACCESS FULL | CARRIER_CAPACITY |

    | 49 | INDEX RANGE SCAN | ZNE_TR_IDX4 |

    | 50 | TABLE ACCESS BY INDEX ROWID | NETWORKS |

    | 51 | INDEX UNIQUE SCAN | NET_PK |

    | 52 | TABLE ACCESS BY INDEX ROWID | BILL_TO |

    | 53 | INDEX UNIQUE SCAN | BTO_PK |

    | 54 | TABLE ACCESS BY INDEX ROWID | NETWORKS |

    | 55 | INDEX UNIQUE SCAN | NET_PK |

    | 56 | TABLE ACCESS BY INDEX ROWID | BILL_TO |

    | 57 | INDEX UNIQUE SCAN | BTO_PK |

    | 58 | SORT ORDER BY | |

    | 59 | COUNT | |

    | 60 | FILTER | |

    | 61 | HASH JOIN OUTER | |

    | 62 | HASH JOIN OUTER | |

    | 63 | NESTED LOOPS | |

    | 64 | NESTED LOOPS | |

    | 65 | NESTED LOOPS | |

    | 66 | NESTED LOOPS | |

    | 67 | HASH JOIN | |

    | 68 | TABLE ACCESS FULL | USER_CUSTOMER |

    | 69 | TABLE ACCESS BY INDEX ROWID | LOAD_SEARCH_DATA |

    | 70 | NESTED LOOPS | |

    | 71 | INDEX FULL SCAN | LSTAT_ORDER_IDX |

    | 72 | INDEX RANGE SCAN | LSD_STAT_ORG_IDX |

    | 73 | TABLE ACCESS BY INDEX ROWID | ORGANIZATIONS |

    | 74 | INDEX UNIQUE SCAN | ORG_PK |

    | 75 | TABLE ACCESS BY INDEX ROWID| ORGANIZATIONS |

    | 76 | INDEX UNIQUE SCAN | ORG_PK |

    | 77 | TABLE ACCESS BY INDEX ROWID | LOADS |

    | 78 | INDEX UNIQUE SCAN | LOD_PK |

    | 79 | TABLE ACCESS BY INDEX ROWID | ORGANIZATIONS |

    | 80 | INDEX UNIQUE SCAN | ORG_PK |

    | 81 | TABLE ACCESS BY INDEX ROWID | ORGANIZATIONS |

    | 82 | INDEX UNIQUE SCAN | ORG_PK |

    | 83 | TABLE ACCESS BY INDEX ROWID | LOAD_DETAILS |

    | 84 | INDEX UNIQUE SCAN | LDD_PK |

    | 85 | VIEW | |

    | 86 | HASH GROUP BY | |

    | 87 | INDEX FAST FULL SCAN | VREL_TEST1 |

    | 88 | TABLE ACCESS FULL | VIRTUAL_RADAR_EVENTS |

    --------------------------------------------------------------------------
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    For a start ...
    recommendations

    1) get rid of the excess correlated subqueries,

    and rewrite request using

    WITH

    2) show the full plan of query with statistics of runtime
     
  3. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Dear Sergey,

    >>>1) get rid of the excess correlated subqueries

    do you mean those subqueries that are used like FROM TABLE

    OR

    those subqueries that are used like COLUMN ?

    Maybe both?
    Thanks for your attention.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I don't know enough about what the query is attempting to do or the structure and content of the tables to be positive, but much of the SQL seems to be trying to get the data in a very roundabout fashion. The correlated subqueries in the SELECT list are particularly ugly as has been noted. As I was scanning through the code, the following snippet jumped out at me:

    Code (Text):
    ...
    AND
    /* end of where condition for account/customer owner filters */
    exists
    (
    SELECT 'X'
    FROM   organizations
    WHERE  organizations.org_id = lsd.ORG_ID
    AND    organizations.network_id = (SELECT network_id
                                       FROM   organizations
                                       WHERE org_id = '226371'
                                      )
    )
    ...
    This doesn't make sense. Both queries in this EXISTS clause are selecting from the same table... ORGANIZATIONS. There is no reason for the subquery. Since the subquery is compared using '=', only a single network ID will be returned (or the result would generate an exception). Given that, the subquery could be removed and the query rewritten as:

    Code (Text):
    ...
    AND
    /* end of where condition for account/customer owner filters */
    exists
    (
    SELECT 'X'
    FROM   organizations
    WHERE  organizations.org_id = lsd.ORG_ID
    AND    organizations.org_id = '226371'
    )
    ...
    As previously noted, org_id seems to be a unique value (possibly the PK) in the ORGANIZATIONS table. As such, the above query equates to lsd.ORG_ID = '226371'. I don't understand the reason for the EXISTS at all. Why is this not simply:

    Code (Text):
    ...
    AND
    /* end of where condition for account/customer owner filters */
    lsd.ORG_ID = '226371'
    ...
    Without access to the data, I can't be positive this is equivalent to the original SQL, but it sure seems that way based on how I read the logic. The rest of the query just has the same roundabout feel to it.