Home » RDBMS Server » Performance Tuning » Query performance
Query performance [message #203851] Thu, 16 November 2006 10:29 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I have a table called 'AP_CHECKS' which has nearly 4 lakhs record. I had written a simple query to find the vendor_id's who haven't performed any transaction for the past 13 months.

SELECT '-777',
NVL(Vendor_id,- 666) "Vendor_ID",
TO_CHAR(MAX(Check_date),'DD-MON-YY') "Check_Date"
FROM Ap_checks
GROUP BY Vendor_id
HAVING MONTHS_BETWEEN(SYSDATE,MAX(Check_date)) >= 13
ORDER BY Vendor_id DESC

The query is taking bit time to execute..can any one please help me on this..?
Re: Query performance [message #203899 is a reply to message #203851] Thu, 16 November 2006 21:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You will need either a Full Table Scan or Full Index Scan for that type of query. If you have an index that contains VENDOR_ID and CHECK_DATE, then the full index scan will be a bit faster than a full table scan.

If you use PL/SQL rather than SQL, there may be a faster way. How many different VENDOR_IDs are there in the table, and how much is a lakh (100,000?).

Ross Leishman
Re: Query performance [message #203914 is a reply to message #203899] Thu, 16 November 2006 23:13 Go to previous messageGo to next message
selvakumar_82
Messages: 138
Registered: October 2005
Location: chennai
Senior Member
s rleishman,

In Internaltional Forum please use million and billion,

Ten Lakhs is Equal to One Million .

One Lakhs = or Hundred Thousand
Re: Query performance [message #204015 is a reply to message #203914] Fri, 17 November 2006 05:57 Go to previous messageGo to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

There are morethan 20,000 different vendors are available in the base table.
Re: Query performance [message #204025 is a reply to message #204015] Fri, 17 November 2006 06:26 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Then there is no faster way to do it. Go with the full index scan.

Ross Leishman
Previous Topic: Small Doubt Regarding CBO
Next Topic: oracle reads from buttom to top
Goto Forum:
  


Current Time: Wed Dec 07 22:06:17 CST 2016

Total time taken to generate the page: 0.09296 seconds