SELECT MAX(verification_id)
 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 question takes 153 seconds to operate. you will find countless rows in VERIFICATION_TABLE.

I believe totally taking lengthy due to the functions in where clause. However, I have to do ltrim rtrim around the posts as well as date needs to be matched up in MM/DD/YYYY format. How do i optimize this question?

Explain plan:

SELECT STATEMENT, GOAL = ALL_ROWS           80604   1   59
 SORT AGGREGATE                                   1   59

Primary key:



N_VRFTN_IDX2    head, mbr, dob, lname, verification_id
N_VRFTN_IDX3    last_update_date
N_VRFTN_IDX4    mbr, lname, dob, verification_id
N_VRFTN_IDX4    verification_id

Though, within the explain plan I dont see indexes/primary key getting used. would be that the problem?

Do this:

SELECT MAX(verification_id)
 WHERE head = 687422
   AND mbr = 23102
   AND TRIM(lname) = '.iq bzw'
   AND TRUNCATE(dob) = TO_DATE('08/10/2004')
   AND system_code = 'M';

Remove that TRUNCATE() if dob does not have enough time onto it already, in the looks from it (Birth date?) it might not. Past that, you'll need some indexing work. If you are querying much in this fashion, I'd index mbr and head inside a 2 column index, should you stated exactly what the posts mean it'd help determine the very best indexing here.

The only real index that's a potential candidate to be used inside your totally N_VRFTN_IDX2, since it indexes four from the posts you utilize inside your WHERE clause: Mind, MBR, DOB and LNAME.

However, since you apply functions to both DOB and LNAME they're ineligible for consideration. The optimizer will then decide to not use that index since it thinks Mind+MBR by themselves are an inadequately selective combination. Should you removed the TO_CHAR() call from DOB then you've three leading posts on N_VRFTN_IDX2 that might allow it to be more appealing towards the optimizer. Likewise, must you TRIM() LNAME?

Another factor is, the necessity to lookup SYSTEM_CODE means the query needs to read in the table (because that column isn't indexed). If N_VRFTN_IDX2 includes a poor clustering invoice discounting the optimizer might wish to get a FULL TABLE SCAN since the indexed reads are an overhead. Whereas should you added SYSTEM_CODE towards the index the whole query might be satisfied by a catalog RANGE SCAN, which will be a lot faster.

Finally, how fresh are the statistics? In case your statistics are stale, that may lead the optimizer to create a duff decision. For example, better statistics might lead the optimizer to make use of the compound index despite only the two leading posts.

You need to turn the literal right into a DATE and never the column right into a VARCHAR2 such as this:

AND  dob = TO_DATE('08/10/2004','MM/DD/YYYY')

Or make use of the more suitable ANSI date literal syntax:

AND  dob = DATE '2004-08-10'

When the dob column consists of time (a birth date does not usually, except most probably inside a hospital!) you'll be able to do:

AND  dob >= DATE '2004-08-10' 
AND  dob <  DATE '2004-08-11' 

Look into the datatypes for Mind and MBR. The values "687422 and 23102" possess the 'feel' to be quite selective. That's, for those who have 100s of 1000's of values for mind and countless records within the table, it might appear that Mind is very selective. [That may be totally misleading though.]

Anyway, you might find that Mind and/or MBR are really saved as VARCHAR2 or CHAR fields instead of NUMBER. If that's the case, evaluating the smoothness to some number would prevent using the index. Try the next (and I have incorporated the conversion from the dob predicate having a date but added the explicit format mask).

SELECT MAX(verification_id)
 WHERE head = '687422'
   AND mbr = '23102'
   AND RTRIM(LTRIM(lname)) = '.iq bzw'
   AND TRUNCATE(dob) = TO_DATE('08/10/2004','MM/DD/YYYY')
   AND system_code = 'M';