Optimizing Oracle query
Posted
by Omnipresent
on Stack Overflow
See other posts from Stack Overflow
or by Omnipresent
Published on 2010-03-21T11:55:39Z
Indexed on
2010/03/21
12:01 UTC
Read the original article
Hit count: 435
SELECT MAX(verification_id)
FROM VERIFICATION_TABLE
WHERE head = 687422
AND mbr = 23102
AND RTRIM(LTRIM(lname)) = '.iq bzw'
AND TO_CHAR(dob,'MM/DD/YYYY')= '08/10/2004'
AND system_code = 'M';
This query is taking 153 seconds to run. there are millions of rows in VERIFICATION_TABLE.
I think query is taking long because of the functions in where clause. However, I need to do ltrim rtrim on the columns and also date has to be matched in MM/DD/YYYY format. How can I optimize this query?
© Stack Overflow or respective owner
