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!

How to query the Supplier name by default by ITEM_ID based on sourcing rules?

Discussion in 'SQL PL/SQL' started by CASTELAZO, Feb 15, 2013.

  1. CASTELAZO

    CASTELAZO Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guys,

    Before anything thanks for your help.

    I need a query to get the supplier name of an item based on sourcing rules. I wrote a query months ago but I found that it's doing FULL TABLE ACCESS which is undesirable. I went digging into the columns to add more filters but can't make it slimmer. Check it out

    Code (SQL):
    SELECT  DISTINCT aps.vendor_name,
      msso.vendor_name,
     -- msso.vendor_site_code,
      msso.allocation_percent,
      msso.rank,
      msso.vendor_id,
      msso.vendor_site_id
    FROM po_approved_supplier_list pasl,
          ap_suppliers aps,
          AP_SUPPLIER_SITES_ALL POS,
          mrp_sr_source_org_v msso
    WHERE aps.vendor_id = pasl.vendor_id
      AND aps.VENDOR_ID=POS.VENDOR_ID
      AND MSSO.vendor_id=aps.VENDOR_ID
      AND aps.enabled_flag = 'Y'
      AND NVL (pasl.disable_flag, 'N') = 'N'
      AND pasl.owning_organization_id =:p_org
      AND pasl.item_id = :item_id
      AND MSSO.rank=1;
    This code is not too slow, but when you are running it to get the vendor name for some hundreds of items, it gets slow. The Explain plan shows some TABLE ACCESS(FULL) and I need to tune it up. :confused:

    I appreciate your help, thanks!

    Castelazo
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I see one TABLE ACCESS (FULL) in this entire plan (unless there's more you failed to post) and that is for MRP_SR_SOURCE_ORG. What indexes are created against this table, if any?
     
  3. CASTELAZO

    CASTELAZO Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David,

    Good question. The indexes are:


    INDEX_NAME TABLE_NAME
    ------------------------------ ------------------------------
    MRP_SR_SOURCE_ORG_U2 MRP_SR_SOURCE_ORG
    MRP_SR_SOURCE_ORG_U1 MRP_SR_SOURCE_ORG
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That doesn't help much, actually, as it doesn't report which columns of the table are indexed. Please provide that information, as well as the number of rows in that table.
     
  5. CASTELAZO

    CASTELAZO Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Code (Text):
    Index_name                     table_name                     column_name
    ------------------------------ ------------------------------ -------------------------
    mrp_sr_source_org_u1           mrp_sr_source_org              sr_source_id
    mrp_sr_source_org_u2           mrp_sr_source_org              sr_receipt_id
    mrp_sr_source_org_u2           mrp_sr_source_org              source_organization_id
    mrp_sr_source_org_u2           mrp_sr_source_org              vendor_id
    mrp_sr_source_org_u2           mrp_sr_source_org              vendor_site_id
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle? If the leading columns of index mrp_sr_source_org_u2 are not exceptionally selective then an INDEX SKIP SCAN could occur to pick up the VENDOR_ID column. Obviously that's not the case here as no skip scan is being executed. You may want to add a third index, with VENDOR_ID as the leading column, to possibly speed up this query.