Home » SQL & PL/SQL » SQL & PL/SQL » Help with Query
Help with Query [message #279692] Fri, 09 November 2007 11:47 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
This query is taking a long time to run. Any suggestions on how to make it faster or fix anything in the statement itself?
I'm still new to sql so be easy on me.

SELECT   UPPER (v.vendor_name) "Vendor Name", v.segment1 "Vendor Number",
         c.bank_account_name "Bank Account Name",
         c.check_number "Check Number", c.check_date "Check Date",
         c.amount "Check Amount", 
         --c.currency_code "Currency",
         c.void_date "Void Date", c.check_id "Check Id",
         i.invoice_num "Invoice Number", i.invoice_date "Invoice Date",
         i.invoice_amount "Invoice Amount", ip.amount "Payment Amount",
         i.creation_date "Creation Date",
         i.payment_method_lookup_code "Payment Method Type",
         po.segment1 "PO Number",
         i.description "Description", 
         v.vendor_type_lookup_code "Employee Type"
   FROM apps.ap_checks_all c,
         apps.po_vendors v,
         apps.ap_invoices_all i,
         apps.ap_invoice_payments_all ip,
         apps.ap_invoice_distributions_all ap,
         apps.po_headers_all po
  WHERE po.vendor_id (+) = i.vendor_id 
     AND ip.invoice_id = i.invoice_id
     AND i.invoice_id = ap.invoice_id 
--     AND i.payment_method_lookup_code = 'CHECK'
     AND c.vendor_id = v.vendor_id
     AND c.check_id = ip.check_id
     AND v.vendor_id = i.vendor_id (+)
     AND c.check_date BETWEEN TO_DATE ('01/01/2007', 'dd/mm/yyyy')
                          AND TO_DATE ('31/03/2007', 'dd/mm/yyyy')
ORDER BY v.vendor_name


Re: Help with Query [message #279695 is a reply to message #279692] Fri, 09 November 2007 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
1 or more of the suggestions found in thread above may help
Re: Help with Query [message #279706 is a reply to message #279695] Fri, 09 November 2007 12:41 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Well my query starting acting bad after I added the column from the apps.po_headers_all po table. Maybe my joins are not correct, what do you think?

All these tables have the following columns in common:

SELECT * FROM APPS.PO_VENDORS v (VENDOR_ID)

SELECT * FROM apps.ap_checks_all c (check_id,vendor_id)

select * from apps.ap_invoices_all i (invoice_id, vendor_id)

select * from apps.ap_invoice_payments_all ip (check_id,invoice_id)

select * from apps.ap_invoice_distributions_all ap (invoice_id)
select * from apps.po_headers_all po (vendor_id)

Anne
Re: Help with Query [message #279711 is a reply to message #279692] Fri, 09 November 2007 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>apps.ap_invoice_distributions_all ap,
Above line appears in the FROM clause
AFAIK, it does NOT contribute any data to the SELECT clause.
If so, it should be removed out of the FROM clause & if it is required for filtering, it should be subordinated into WHERE clause.
Re: Help with Query [message #279715 is a reply to message #279711] Fri, 09 November 2007 12:55 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
I took that line out and noticed it after I posted it, but it still runs slow.

Re: Help with Query [message #279716 is a reply to message #279692] Fri, 09 November 2007 13:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, Aim!

I, for one, can not tune a "complex" SQL statement simply by looking at it.
If tuning was that easy, then the optimizer would be able to any & every statement return data quickly.

Do you have current statistics on all tables & indexes?
Do you have indexes on all columns in the WHERE clause?

Unless & until you actually read & FOLLOW the advice in the previously provided URL, Your On Your Own (YOYO)!

[Updated on: Fri, 09 November 2007 13:06] by Moderator

Report message to a moderator

Re: Help with Query [message #279797 is a reply to message #279692] Sat, 10 November 2007 07:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Post EXPLAIN (TKPROF even better).

2. Do you have an index on check_date column of ap_checks_all table?

Re: Help with Query [message #280023 is a reply to message #279797] Mon, 12 November 2007 05:59 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Furthermore, you might want to check the datamodel on Metalink.

I don't know the PO/AP schema by heart, but I'm pretty sure that invoices can (don't have to, depends on your setup) be matched on PO's. And in your statement there is no join between po_headers and ap_invoices.

Like I said, in general, check the datamodel.

PS as for statistics: most apps dba's I know are pretty neurotic about that Wink so it's good to check but chances are that the statistics are up to date.

[Updated on: Mon, 12 November 2007 06:00]

Report message to a moderator

Previous Topic: how to find out name of table if we dont know it
Next Topic: doubt regarding exception handling
Goto Forum:
  


Current Time: Sat Dec 03 08:15:09 CST 2016

Total time taken to generate the page: 0.08294 seconds