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 question takes 153 seconds to operate. you will find countless rows in
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?
SELECT STATEMENT, GOAL = ALL_ROWS 80604 1 59 SORT AGGREGATE 1 59 TABLE ACCESS FULL P181 VERIFICATION_TABLE 80604 1 59
VRFTN_PK Primary VERIFICATION_ID
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?
SELECT MAX(verification_id) FROM VERIFICATION_TABLE WHERE head = 687422 AND mbr = 23102 AND TRIM(lname) = '.iq bzw' AND TRUNCATE(dob) = TO_DATE('08/10/2004') AND system_code = 'M';
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
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) FROM VERIFICATION_TABLE 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';