Eliminate MAX Clause for fine tuning SQL

Shamavaidy · New User Joined: 16 Dec 2006 Posts: 1 Location: Chennai

Can any one help me to eliminate MAX Clause for SQL fine tuning
This table has some 5 million records. Job is running for a long time.

For Eg:
SELECT MAX(XXX_NBR)
FROM XXX_YYY
WHERE XXX_NBR > :WS-MIN-NBR
AND YYY = :WS-YYY
WITH UR

die7nadal · Active User Joined: 23 Mar 2005 Posts: 156

What do u want to acomplish thru this query. If u need the Max value, then then u shud use the MAX.
But here are a few things to consider.
1) Are XXX_NBR and YYY Indexed, if not try doing it.
2) If both the columns are indexed, is the predicate that brings back less rows executed first. If not try forcing the predicate that brings back more rows to execute last by concatenating Null to the corresponding host variable as here YYY = :WS-YYY || ''.

priyesh.agrawal · Posted: Mon Dec 18, 2006 11:49 am

Try GROUP BY ...