Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    8

    Unanswered: How to remove Table Access Full

    Can any one explain how to remove Table Access Full, query is taking too long to give the results.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can any one explain how to remove Table Access Full, query is taking too long to give the results.

    It is a MAJOR challenge to fix code that is hidden from view.

    please use COPY & PASTE so we can see what you do & how Oracle responds

    ALWAYS
    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version
    or post Oracle version to 4 decimal places
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by ashishbarot View Post
    Can any one explain how to remove Table Access Full, query is taking too long to give the results.
    Create an index

  4. #4
    Join Date
    Feb 2010
    Posts
    8

    Thanks for your reply

    Thanks for your prompt reply....!!!

    DBA has already created NON-unique index.

    Oracle8 Enterprise Edition Release 8.0.5.0.0

    and i have a query which is mentioned below with explain plan.

    SELECT M_RO_HDR.MEDIA_CODE
    || ','
    || CLIENT_NAME
    || ','
    || G_VARIANT_MSTR.VARIANT_CODE
    || ','
    || G_VARIANT_MSTR.VARIANT_NAME
    || ','
    || CAMPAIGN_NAME
    || ','
    || M_RD_RO_DTL.MEDIA_ESTIMATE_NO
    || ','
    || M_RO_HDR.RO_NO
    || ','
    || K.SUPPLIER_CODE
    || ','
    || K.SUPPLIER_NAME
    || ','
    || M_RO_HDR.SUPPLIER_CURRENCY_CODE
    || ','
    || STATION_NAME
    || ','
    || SCHD_SPOT_DURATION
    || ','
    || M_RD_ESTIMATE_SCHEDULE_DTL.SCHEDULE_FROM
    || ','
    || DECODE(M_RD_RO_DTL.BONUS_FLAG,
    'Y', 0,
    DECODE(A.SCHEDULERS_STATUS, '4', 0, NETT_COST)
    )
    || ','
    || M_RD_RO_DTL.BONUS_FLAG
    || ','
    ||
    --DECODE(M_RD_RO_DTL.RO_STATUS,'0','No','1','Yes','2 ','Missing','3','Disputed','4','MakeGood','6','Can celled','7','Yes')||','||
    --DECODE(M_RD_RO_DTL.RO_STATUS,'4','Makegood',DECODE (M_RD_RO_DTL.DN_GENERATED_PERCENTAGE,NULL,'NOT GENERATED','GENERATED'))||','||
    DECODE(NVL(M_RD_RO_DTL.RO_STATUS, 0),
    '6', 'Cancelled',
    '4', 'No',
    DECODE(NVL(A.SCHEDULERS_STATUS, 0),
    '0', 'No',
    '1', 'Yes',
    '2', 'Missing',
    '3', 'Disputed',
    '4', 'MakeGood',
    '6', 'Cancelled',
    '7', 'Yes'
    )
    )
    || ','
    || DECODE(NVL(A.SCHEDULERS_STATUS, 0),
    '4', 'Makegood',
    DECODE(M_RD_RO_DTL.DN_GENERATED_PERCENTAGE,
    NULL, 'NOT GENERATED',
    'GENERATED'
    )
    )
    || ','
    || DECODE(M_RO_HDR.RO_PRINTED_FLAG, 'N', 'No', 'Yes')
    || ','
    || 'NO'
    || ','
    || 'NO'
    || ','
    || 'NO'
    || ','
    || B.DN_NO
    || ','
    || C.DN_DATE
    || ','
    || D.COMPANY_CODE
    || ','
    || D.COMPANY_NAME
    || ','
    || E.BILL_NO
    || ','
    || F.SUPPLIER_BILL_NO
    || ','
    || F.BILL_DATE
    || ','
    || M_RO_HDR.RO_REF_NO
    || ','
    || (NVL(K.AGENCY_DISCOUNT, H.FEE_COMM_VALUE)
    - DECODE(J.DISCOUNT_RATE,
    0, 0,
    DECODE(SIGN( NVL(K.AGENCY_DISCOUNT, H.FEE_COMM_VALUE)
    - NVL(L.COMMISSION_PERCENTAGE, 0)
    ),
    0, 0,
    -1, 0,
    NVL(K.AGENCY_DISCOUNT, H.FEE_COMM_VALUE)
    - NVL(L.COMMISSION_PERCENTAGE, 0)
    )
    ))
    || ','
    || ROUND( DECODE(M_RD_RO_DTL.BONUS_FLAG,
    'Y', 0,
    DECODE(A.SCHEDULERS_STATUS, '4', 0, NETT_COST)
    )
    * ( NVL(K.AGENCY_DISCOUNT, H.FEE_COMM_VALUE)
    - DECODE(J.DISCOUNT_RATE,
    0, 0,
    DECODE(SIGN( NVL(K.AGENCY_DISCOUNT,
    H.FEE_COMM_VALUE
    )
    - NVL(L.COMMISSION_PERCENTAGE, 0)
    ),
    0, 0,
    -1, 0,
    NVL(K.AGENCY_DISCOUNT, H.FEE_COMM_VALUE)
    - NVL(L.COMMISSION_PERCENTAGE, 0)
    )
    )
    )
    / 100,
    2
    )
    || ','
    || NVL(M.VOLUME_DISCOUNT_RATE, 0)
    || ','
    || ROUND( DECODE(M_RD_RO_DTL.BONUS_FLAG,
    'Y', 0,
    DECODE(A.SCHEDULERS_STATUS, '4', 0, NETT_COST)
    )
    * NVL(M.VOLUME_DISCOUNT_RATE, 0)
    / 100,
    2
    )
    || ','
    || M_FLOW_PLAN_MSTR.CLIENT_PO_NO
    || ','
    || M_FLOW_PLAN_DETAILS.COMBINATION_DESCRIPTION
    FROM G_CLIENT_MSTR,
    G_VARIANT_MSTR,
    M_RD_RO_DTL,
    M_RO_HDR,
    M_FLOW_PLAN_DETAILS,
    G_CAMPAIGN_MSTR,
    M_ESTIMATE_HDR,
    M_STATION_MSTR,
    M_RD_ESTIMATE_SCHEDULE_DTL,
    M_RD_RO_MONITORING A,
    M_FLOW_PLAN_MSTR,
    M_RD_DN_DTL B,
    M_DN_HDR C,
    G_COMPANY_MSTR D,
    M_RD_BILL_DTL E,
    M_BILL_HDR F,
    G_VARIANT_MEDIA_PROFILE_MSTR H,
    G_CLIENT_MONITORING_COST J,
    G_SUPPLIER_MSTR K,
    M_VARIANT_DN_PROFILE_MSTR L,
    M_SUPP_SRVTX_MSTR M
    WHERE G_CLIENT_MSTR.CLIENT_CODE = M_RO_HDR.CLIENT_CODE
    AND G_VARIANT_MSTR.VARIANT_CODE = M_RO_HDR.VARIANT_CODE
    AND G_CAMPAIGN_MSTR.CAMPAIGN_CODE = M_ESTIMATE_HDR.CAMPAIGN_CODE
    AND M_ESTIMATE_HDR.MEDIA_ESTIMATE_NO = M_RO_HDR.MEDIA_ESTIMATE_NO
    AND M_RO_HDR.RO_NO = M_RD_RO_DTL.RO_NO
    AND M_STATION_MSTR.STATION_CODE = M_RD_RO_DTL.STATION_CODE
    AND M_FLOW_PLAN_DETAILS.FLOW_PLAN_NO || '/RD' =M_RD_RO_DTL.MEDIA_ESTIMATE_NO
    AND M_FLOW_PLAN_DETAILS.PUBLICATION_CHANNEL_CODE = M_RD_RO_DTL.STATION_CODE
    AND M_FLOW_PLAN_DETAILS.COMBINATION_CODE = M_RD_RO_DTL.DEAL_COMBO_CODE
    AND M_FLOW_PLAN_DETAILS.CATEGORY_CODE = M_RD_RO_DTL.CATEGORY_CODE
    AND M_RD_ESTIMATE_SCHEDULE_DTL.SCHEDULE_FROM BETWEEN to_date(:B1START_DATE,'MM/DD/YYYY') AND to_date(:B1END_DATE,'MM/DD/YYYY')
    AND UPPER(G_CLIENT_MSTR.CLIENT_CODE) =
    UPPER(DECODE(:P_CLIENT_CODE,
    'ALL', G_CLIENT_MSTR.CLIENT_CODE,
    :P_CLIENT_CODE
    )
    )
    AND UPPER(G_VARIANT_MSTR.VARIANT_CODE) =
    UPPER(DECODE(:P_VARIANT_CODE,
    'ALL', G_VARIANT_MSTR.VARIANT_CODE,
    :P_VARIANT_CODE
    )
    )
    AND M_RD_ESTIMATE_SCHEDULE_DTL.MEDIA_ESTIMATE_NO =
    M_RD_RO_DTL.MEDIA_ESTIMATE_NO

    AND M_RD_ESTIMATE_SCHEDULE_DTL.SERIAL_NO = M_RD_RO_DTL.SERIAL_NO
    AND M_RD_ESTIMATE_SCHEDULE_DTL.SCHEDULE_SERIAL_NO =
    M_RD_RO_DTL.SCHEDULE_SERIAL_NO
    AND M_RD_RO_DTL.BILL_LOG_TOTAL_SPOT IS NULL
    AND M_RD_RO_DTL.MEDIA_ESTIMATE_NO = A.MEDIA_ESTIMATE_NO(+)
    AND M_RD_RO_DTL.RO_NO = A.RO_NO(+)
    AND M_RD_RO_DTL.SERIAL_NO = A.SERIAL_NO(+)
    AND M_RD_RO_DTL.SCHEDULE_SERIAL_NO = A.SCHEDULE_SERIAL_NO(+)
    AND M_RD_RO_DTL.COMBINATION_SERIAL_NO = A.COMBINATION_SERIAL_NO(+)
    AND M_RD_RO_DTL.RO_NO = B.RO_NO(+)
    AND M_RD_RO_DTL.SERIAL_NO = B.SERIAL_NO(+)
    AND M_RD_RO_DTL.SCHEDULE_SERIAL_NO = B.SCHEDULE_SERIAL_NO(+)
    AND M_FLOW_PLAN_MSTR.FLOW_PLAN_NO = M_FLOW_PLAN_DETAILS.FLOW_PLAN_NO
    AND B.DN_NO = C.DN_NO(+)
    AND M_RO_HDR.COMPANY_CODE = D.COMPANY_CODE
    AND B.RO_NO = E.RO_NO(+)
    AND B.SERIAL_NO = E.SERIAL_NO(+)
    AND B.SCHEDULE_SERIAL_NO = E.SCHEDULE_SERIAL_NO(+)
    AND E.BILL_NO = F.BILL_NO(+)
    AND M_RO_HDR.RO_TYPE <> 'M'
    AND NVL(A.SCHEDULERS_STATUS,0) <> '4'
    AND M_RO_HDR.MEDIA_CODE = H.MEDIA_CODE
    AND M_RO_HDR.VARIANT_CODE = H.VARIANT_CODE
    AND M_RO_HDR.CLIENT_CODE = J.CLIENT_CODE
    AND M_RO_HDR.MEDIA_CODE = J.MEDIA_CODE
    AND M_RO_HDR.SUPPLIER_CODE = K.SUPPLIER_CODE
    AND H.MEDIA_CODE = L.MEDIA_CODE
    AND H.VARIANT_CODE = L.VARIANT_CODE
    AND M_RO_HDR.MEDIA_CODE = M.MEDIA_CODE(+)
    AND M_RO_HDR.SUPPLIER_CODE = M.SUPPLIER_CODE(+)

  5. #5
    Join Date
    Feb 2010
    Posts
    8
    Plan
    SELECT STATEMENT CHOOSECost: 220 Bytes: 28,548,299 Cardinality: 20,147
    51 FILTER
    50 NESTED LOOPS OUTER
    47 NESTED LOOPS Cost: 136 Bytes: 37,016 Cardinality: 28
    44 HASH JOIN OUTER Cost: 132 Bytes: 5,184 Cardinality: 4
    42 NESTED LOOPS OUTER Cost: 119 Bytes: 39,776 Cardinality: 32
    39 HASH JOIN OUTER Cost: 118 Bytes: 1,212 Cardinality: 1
    37 NESTED LOOPS Cost: 89 Bytes: 48,160 Cardinality: 43
    34 HASH JOIN Cost: 88 Bytes: 1,094 Cardinality: 1
    32 NESTED LOOPS Cost: 74 Bytes: 8,280 Cardinality: 8
    29 NESTED LOOPS Cost: 73 Bytes: 935 Cardinality: 1
    26 NESTED LOOPS OUTER Cost: 72 Bytes: 843 Cardinality: 1
    23 NESTED LOOPS Cost: 70 Bytes: 1,618 Cardinality: 2
    21 NESTED LOOPS Cost: 68 Bytes: 770 Cardinality: 1
    19 NESTED LOOPS Cost: 64 Bytes: 670 Cardinality: 1
    16 HASH JOIN Cost: 61 Bytes: 591 Cardinality: 1
    14 NESTED LOOPS Cost: 58 Bytes: 1,116 Cardinality: 2
    11 NESTED LOOPS OUTER Cost: 57 Bytes: 532 Cardinality: 1
    9 HASH JOIN Cost: 56 Bytes: 432 Cardinality: 1
    7 HASH JOIN Cost: 52 Bytes: 1,235 Cardinality: 5
    1 TABLE ACCESS FULL INTERNATIONAL.G_COMPANY_MSTR Cost: 1 Bytes: 1,230 Cardinality: 41
    6 HASH JOIN Cost: 50 Bytes: 2,604 Cardinality: 12
    2 TABLE ACCESS FULL INTERNATIONAL.G_VARIANT_MSTR Cost: 12 Bytes: 608 Cardinality: 16
    5 HASH JOIN Cost: 37 Bytes: 13,783 Cardinality: 77
    3 TABLE ACCESS FULL INTERNATIONAL.G_CLIENT_MSTR Cost: 8 Bytes: 484 Cardinality: 11
    4 TABLE ACCESS FULL INTERNATIONAL.M_RO_HDR Cost: 28 Bytes: 94,365 Cardinality: 699
    8 TABLE ACCESS FULL INTERNATIONAL.M_RD_RO_DTL Cost: 3 Bytes: 16,835 Cardinality: 91
    10 TABLE ACCESS FULL INTERNATIONAL.M_RD_DN_DTL Cost: 1 Bytes: 10,200 Cardinality: 102
    13 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_STATION_MSTR Cost: 1 Bytes: 5,278 Cardinality: 203
    12 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.PK_STN_M Cardinality: 203
    15 TABLE ACCESS FULL INTERNATIONAL.G_CLIENT_MONITORING_COST Cost: 2 Bytes: 6,699 Cardinality: 203
    18 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_RD_ESTIMATE_SCHEDULE_DTL Cost: 3 Bytes: 24,806 Cardinality: 314
    17 INDEX RANGE SCAN UNIQUE INTERNATIONAL.PK_RD_EST_SCH Cost: 2 Cardinality: 314
    20 TABLE ACCESS FULL INTERNATIONAL.M_FLOW_PLAN_DETAILS Cost: 4 Bytes: 40,500 Cardinality: 405
    22 TABLE ACCESS FULL INTERNATIONAL.M_FLOW_PLAN_MSTR Cost: 2 Bytes: 7,917 Cardinality: 203
    25 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_SUPP_SRVTX_MSTR Cost: 1 Bytes: 13,770 Cardinality: 405
    24 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.SYS_C00294338 Cardinality: 405
    28 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.G_VARIANT_MEDIA_PROFILE_MSTR Cost: 1 Bytes: 74,520 Cardinality: 810
    27 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.CPK_MEDIA_PROFILE Cardinality: 810
    31 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.G_SUPPLIER_MSTR Cost: 1 Bytes: 81,000 Cardinality: 810
    30 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.SYS_C00292214 Cardinality: 810
    33 TABLE ACCESS FULL INTERNATIONAL.M_VARIANT_DN_PROFILE_MSTR Cost: 10 Bytes: 70,505 Cardinality: 1,195
    36 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_ESTIMATE_HDR Cost: 1 Bytes: 112,632 Cardinality: 4,332
    35 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.CPK_M_ESTIMATE_HDR Cardinality: 4,332
    38 TABLE ACCESS FULL INTERNATIONAL.M_RD_BILL_DTL Cost: 11 Bytes: 128,524 Cardinality: 1,397
    41 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_DN_HDR Cost: 1 Bytes: 99,789 Cardinality: 3,219
    40 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.CPK_DN_H Cardinality: 3,219
    43 TABLE ACCESS FULL INTERNATIONAL.M_BILL_HDR Cost: 1 Bytes: 689 Cardinality: 13
    46 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.G_CAMPAIGN_MSTR Cost: 1 Bytes: 18,434 Cardinality: 709
    45 INDEX UNIQUE SCAN UNIQUE INTERNATIONAL.CPK_G_CAMPAIGN_MSTR Cardinality: 709
    49 TABLE ACCESS BY INDEX ROWID INTERNATIONAL.M_RD_RO_MONITORING Cost: 3 Bytes: 6,835,630 Cardinality: 71,954
    48 INDEX RANGE SCAN NON-UNIQUE INTERNATIONAL.M_RD_RO_MONITORING_INDX Cost: 2 Cardinality: 71,954

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    take a look at what indexes exist on the tables where you have FULL scans, can you make use of them or do you need to create new indexes?
    Dave

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT m_ro_hdr.media_code
           || ','
           || client_name
           || ','
           || g_variant_mstr.variant_code
           || ','
           || g_variant_mstr.variant_name
           || ','
           || campaign_name
           || ','
           || m_rd_ro_dtl.media_estimate_no
           || ','
           || m_ro_hdr.ro_no
           || ','
           || k.supplier_code
           || ','
           || k.supplier_name
           || ','
           || m_ro_hdr.supplier_currency_code
           || ','
           || station_name
           || ','
           || schd_spot_duration
           || ','
           || m_rd_estimate_schedule_dtl.schedule_from
           || ','
           || Decode(m_rd_ro_dtl.bonus_flag, 'Y', 0,
                                             Decode(a.schedulers_status, '4', 0,
                                                                         nett_cost))
           || ','
           || m_rd_ro_dtl.bonus_flag
           || ','
           ||
           --DECODE(M_RD_RO_DTL.RO_STATUS,'0','No','1','Yes','2 ','Missing','3','Disputed','4','MakeGood','6','Can celled','7','Yes')||','||
           --DECODE(M_RD_RO_DTL.RO_STATUS,'4','Makegood',DECODE (M_RD_RO_DTL.DN_GENERATED_PERCENTAGE,NULL,'NOT GENERATED','GENERATED'))||','||
           Decode(Nvl(m_rd_ro_dtl.ro_status, 0), '6', 'Cancelled',
                                                 '4', 'No',
           Decode(Nvl(a.schedulers_status, 0), '0',
           'No',
                                               '1',
           'Yes',
                                               '2',
           'Missing',
                                               '3',
           'Disputed',
                                               '4',
           'MakeGood',
                                               '6',
           'Cancelled',
                                               '7',
           'Yes'))
           || ','
           || Decode(Nvl(a.schedulers_status, 0), '4', 'Makegood',
                                                  Decode(
              m_rd_ro_dtl.dn_generated_percentage,
                                                  NULL, 'NOT GENERATED',
                                                  'GENERATED'))
           || ','
           || Decode(m_ro_hdr.ro_printed_flag, 'N', 'No',
                                               'Yes')
           || ','
           || 'NO'
           || ','
           || 'NO'
           || ','
           || 'NO'
           || ','
           || b.dn_no
           || ','
           || c.dn_date
           || ','
           || d.company_code
           || ','
           || d.company_name
           || ','
           || e.bill_no
           || ','
           || f.supplier_bill_no
           || ','
           || f.bill_date
           || ','
           || m_ro_hdr.ro_ref_no
           || ','
           || ( Nvl(k.agency_discount, h.fee_comm_value) -
                Decode(j.discount_rate, 0, 0,
                     Decode(
                     Sign(Nvl(k.agency_discount, h.fee_comm_value) -
                          Nvl(l.commission_percentage, 0)),
                     0, 0,
                                                                -1, 0,
                     Nvl(k.agency_discount, h.fee_comm_value) -
                     Nvl(l.commission_percentage, 0))) )
           || ','
           || Round(Decode(m_rd_ro_dtl.bonus_flag, 'Y', 0,
                    Decode(a.schedulers_status, '4',
                    0,
                                                nett_cost))
                    * (
                             Nvl(k.agency_discount, h.fee_comm_value) -
                             Decode(j.discount_rate, 0, 0,
                                                     Decode(
                             Sign(Nvl(k.agency_discount,
                                  h.fee_comm_value) -
                                                     Nvl(l.commission_percentage, 0)
                             ), 0, 0
                                                     ,
                                                     -1, 0,
                             Nvl(k.agency_discount, h.fee_comm_value)
                             -
                             Nvl(l.commission_percentage, 0))) ) / 100
              , 2)
           || ','
           || Nvl(m.volume_discount_rate, 0)
           || ','
           || Round(Decode(m_rd_ro_dtl.bonus_flag, 'Y', 0,
                    Decode(a.schedulers_status, '4',
                    0,
                                                nett_cost))
                    *
                             Nvl(m.volume_discount_rate, 0) / 100, 2)
           || ','
           || m_flow_plan_mstr.client_po_no
           || ','
           || m_flow_plan_details.combination_description
    FROM   g_client_mstr,
           g_variant_mstr,
           m_rd_ro_dtl,
           m_ro_hdr,
           m_flow_plan_details,
           g_campaign_mstr,
           m_estimate_hdr,
           m_station_mstr,
           m_rd_estimate_schedule_dtl,
           m_rd_ro_monitoring a,
           m_flow_plan_mstr,
           m_rd_dn_dtl b,
           m_dn_hdr c,
           g_company_mstr d,
           m_rd_bill_dtl e,
           m_bill_hdr f,
           g_variant_media_profile_mstr h,
           g_client_monitoring_cost j,
           g_supplier_mstr k,
           m_variant_dn_profile_mstr l,
           m_supp_srvtx_mstr m
    WHERE  g_client_mstr.client_code = m_ro_hdr.client_code
           AND g_variant_mstr.variant_code = m_ro_hdr.variant_code
           AND g_campaign_mstr.campaign_code = m_estimate_hdr.campaign_code
           AND m_estimate_hdr.media_estimate_no = m_ro_hdr.media_estimate_no
           AND m_ro_hdr.ro_no = m_rd_ro_dtl.ro_no
           AND m_station_mstr.station_code = m_rd_ro_dtl.station_code
           AND m_flow_plan_details.flow_plan_no
               || '/RD' = m_rd_ro_dtl.media_estimate_no
           AND m_flow_plan_details.publication_channel_code =
               m_rd_ro_dtl.station_code
           AND m_flow_plan_details.combination_code = m_rd_ro_dtl.deal_combo_code
           AND m_flow_plan_details.category_code = m_rd_ro_dtl.category_code
           AND m_rd_estimate_schedule_dtl.schedule_from BETWEEN
               To_date(:B1START_DATE, 'MM/DD/YYYY') AND
               To_date(:B1END_DATE, 'MM/DD/YYYY')
           AND Upper(g_client_mstr.client_code) = Upper(
               Decode(:P_CLIENT_CODE, 'ALL', g_client_mstr.client_code,
                                      :P_CLIENT_CODE))
           AND Upper(g_variant_mstr.variant_code) = Upper(
                   Decode(:P_VARIANT_CODE, 'ALL', g_variant_mstr.variant_code,
                                       :P_VARIANT_CODE))
           AND m_rd_estimate_schedule_dtl.media_estimate_no =
               m_rd_ro_dtl.media_estimate_no
           AND m_rd_estimate_schedule_dtl.serial_no = m_rd_ro_dtl.serial_no
           AND m_rd_estimate_schedule_dtl.schedule_serial_no =
               m_rd_ro_dtl.schedule_serial_no
           AND m_rd_ro_dtl.bill_log_total_spot IS NULL
           AND m_rd_ro_dtl.media_estimate_no = a.media_estimate_no(+)
           AND m_rd_ro_dtl.ro_no = a.ro_no(+)
           AND m_rd_ro_dtl.serial_no = a.serial_no(+)
           AND m_rd_ro_dtl.schedule_serial_no = a.schedule_serial_no(+)
           AND m_rd_ro_dtl.combination_serial_no = a.combination_serial_no(+)
           AND m_rd_ro_dtl.ro_no = b.ro_no(+)
           AND m_rd_ro_dtl.serial_no = b.serial_no(+)
           AND m_rd_ro_dtl.schedule_serial_no = b.schedule_serial_no(+)
           AND m_flow_plan_mstr.flow_plan_no = m_flow_plan_details.flow_plan_no
           AND b.dn_no = c.dn_no(+)
           AND m_ro_hdr.company_code = d.company_code
           AND b.ro_no = e.ro_no(+)
           AND b.serial_no = e.serial_no(+)
           AND b.schedule_serial_no = e.schedule_serial_no(+)
           AND e.bill_no = f.bill_no(+)
           AND m_ro_hdr.ro_type <> 'M'
           AND Nvl(a.schedulers_status, 0) <> '4'
           AND m_ro_hdr.media_code = h.media_code
           AND m_ro_hdr.variant_code = h.variant_code
           AND m_ro_hdr.client_code = j.client_code
           AND m_ro_hdr.media_code = j.media_code
           AND m_ro_hdr.supplier_code = k.supplier_code
           AND h.media_code = l.media_code
           AND h.variant_code = l.variant_code
           AND m_ro_hdr.media_code = m.media_code(+)
           AND m_ro_hdr.supplier_code = m.supplier_code(+)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I am a bit suspicious of all the concatenating of strings. Looks like you are trying to make a CSV file. Why can't the front end add all the commas for you?

  9. #9
    Join Date
    Feb 2010
    Posts
    8
    Yes you are correct i am trying to create the CSV file, and if one can see i have posted the explain plan, in which it is mentioned that which indexes are been used for that query, but there is a doubt that i have created non-unique indexes on almost all the tables but oracle is still using those primary key (unique indexes).

    Waiting for your views and replies.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •