| Query performance [message #203851] |
Thu, 16 November 2006 10:29  |
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  |
rleishman
Messages: 3728
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
