Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unhappy Unanswered: Query Tuning--Help

    Hi,
    Need to tune the following query.
    Explain Plan and other details attached.
    select count(1) from tcs_mis_expense_cost a

    where task_id=0

    AND
    not exists

    (select 1
    FROM

    ap_invoices_all AIA,
    po_vendors PO,
    PO_Vendor_Sites_all POS,
    ap_invoice_distributions_all AID,
    GL_Code_combinations GL

    WHERE

    aid.invoice_distribution_id=a.invoice_distribution _id
    AND
    AIA.vendor_id = PO.vendor_id
    AND
    AIA.vendor_site_id = POS.vendor_site_id
    AND
    AIA.invoice_id = AID.invoice_id
    AND PO.vendor_id = POS.vendor_id
    AND
    AID.DIST_CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID
    AND
    GL.segment7 between '50000' AND '69999'
    AND
    ( gl.segment7 not like '617%' and gl.segment7 <> '61801')
    )
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Do you need to include the tables with alias AIA, PO and POS in the subquery? Would this not give the same result? :-

    select count(1) from tcs_mis_expense_cost a
    where task_id=0
    AND
    not exists
    (select 1
    FROM
    ap_invoice_distributions_all AID,
    GL_Code_combinations GL
    WHERE
    aid.invoice_distribution_id=a.invoice_distribution _id
    AND
    AID.DIST_CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID
    AND
    GL.segment7 between '50000' AND '69999'
    AND
    ( gl.segment7 not like '617%' and gl.segment7 <> '61801')
    )

    Also, how many rows in table A have task=0? If few, an index on that might help.

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Table A has 2265143 records and there are 1728630 records with task_id=0.
    Index won't help.

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Index AP_INVOICE_DISTRIBUTIONS_U2 is not required since cardinality shown for its use is same as the number of records in the table.
    But despite the NO_INDEX hint it is getting used. How do I prevent it? Or my no_index hint is incorrect??

    select count(1) from tcs_mis_expense_cost a

    where task_id=0

    AND
    not exists

    (select /* +NO_INDEX(AID AP_INVOICE_DISTRIBUTIONS_U2)*/ 1
    FROM
    ap_invoice_distributions_all AID,
    ap_invoices_all AIA,
    GL_Code_combinations GL ,
    PO_Vendor_Sites_all POS,
    po_vendors PO

    WHERE

    aid.invoice_distribution_id=a.invoice_distribution _id
    AND
    AIA.vendor_id = PO.vendor_id
    AND
    AIA.vendor_site_id = POS.vendor_site_id
    AND
    AIA.invoice_id = AID.invoice_id
    AND PO.vendor_id = POS.vendor_id
    AND
    AID.DIST_CODE_COMBINATION_ID = GL.CODE_COMBINATION_ID
    AND
    GL.segment7 between '50000' AND '69999'
    AND
    ( gl.segment7 not like '617%' and gl.segment7 <> '61801')
    )

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your hint syntax is wrong - the + must come right next to the /* like this:

    select /*+ NO_INDEX(AID AP_INVOICE_DISTRIBUTIONS_U2) */ 1

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    By using NOT EXISTS you are pushing the optimizer towards using NESTED LOOPS and an index on aid.invoice_distribution_id like this (pseudocode):
    Code:
    FOR each tcs_mis_expense_cost where task_id=0
    LOOP
       SELECT 1 FROM ap_invoice_distributions_all aid, ...
       WHERE aid.invoice_distribution_id = <value>
       AND ...;
       IF %NOTFOUND THEN
         return this tcs_mis_expense_cost row
       END IF
    END LOOP
    You could try changing the structure, e.g. using NOT IN:

    Code:
    select count(1)
    from   tcs_mis_expense_cost a
    where  task_id=0
    and    a.invoice_distribution_id not in 
    ( select aid.invoice_distribution_id
      from   ap_invoices_all AIA,
             po_vendors PO,
             PO_Vendor_Sites_all POS,
             ap_invoice_distributions_all AID,
             GL_Code_combinations GL
      where  aia.vendor_id = po.vendor_id 
      and    aia.vendor_site_id = pos.vendor_site_id
      and    aia.invoice_id = aid.invoice_id
      and    po.vendor_id = pos.vendor_id
      and    aid.dist_code_combination_id = gl.code_combination_id 
      and    gl.segment7 between '50000' and '69999' 
      and    ( gl.segment7 not like '617%' and gl.segment7 <> '61801') 
    )
    ...or even IN:
    Code:
    select count(1)
    from   tcs_mis_expense_cost
    where  task_id=0
    and    invoice_distribution_id in 
    ( 
      select a.invoice_distribution_id
      from   tcs_mis_expense_cost a
      where  task_id=0
      MINUS
      select aid.invoice_distribution_id
      from   ap_invoices_all AIA,
             po_vendors PO,
             PO_Vendor_Sites_all POS,
             ap_invoice_distributions_all AID,
             GL_Code_combinations GL
      where  aia.vendor_id = po.vendor_id 
      and    aia.vendor_site_id = pos.vendor_site_id
      and    aia.invoice_id = aid.invoice_id
      and    po.vendor_id = pos.vendor_id
      and    aid.dist_code_combination_id = gl.code_combination_id 
      and    gl.segment7 between '50000' and '69999' 
      and    ( gl.segment7 not like '617%' and gl.segment7 <> '61801') 
    )

Posting Permissions

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