Optimizing Oracle query

Question

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?

Explain plan:

SELECT STATEMENT, GOAL = ALL_ROWS           80604   1   59
 SORT AGGREGATE                                   1   59
  TABLE ACCESS FULL P181    VERIFICATION_TABLE  80604   1   59

Primary key:

VRFTN_PK    Primary VERIFICATION_ID 

Indexes:

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, in the explain plan I dont see indexes/primary key being used. is that the problem?

Answer 1

Please provide an EXPLAIN output on this query so we know where the slow-down occurs. Two thoughts:

change

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

to

AND  dob = <date here, not sure which oracle str2date function you need>

and use a function based index on

RTRIM(LTRIM(lname)) 

Answer 2

Try this:

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';

Remove that TRUNCATE() if dob doesn't have time on it already, from the looks of it (Date of Birth?) it may not. Past that, you need some indexing work. If you're querying that much in this style, I'd index mbr and head in a 2 column index, if you said what the columns mean it'd help determine the best indexing here.

Answer 3

You should turn the literal into a DATE and not the column into a VARCHAR2 like this:

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

Or use the preferable ANSI date literal syntax:

AND  dob = DATE '2004-08-10'

If the dob column contains time (a date of birth doesn't usually, except presumably in a hospital!) then you can do:

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

Answer 4

Try this:

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

This way a possible index on dob will be used.

Answer 5

The only index that is a possible candidate for use in your query is N_VRFTN_IDX2, because it indexes four of the columns you use in your WHERE clause: HEAD, MBR, DOB and LNAME.

However, because you apply functions to both DOB and LNAME they are ineligible for consideration. The optimizer may then decide not to use that index because it thinks HEAD+MBR on their own are an insufficiently selective combination. If you removed the TO_CHAR() call from DOB then you have three leading columns on N_VRFTN_IDX2 which might make it more attractive to the optimizer. Likewise, is it necessary to TRIM() LNAME?

The other thing is, the need to look up SYSTEM_CODE means the query has to read from the table (because that column is not indexed). If N_VRFTN_IDX2 has a poor clustering factoring the optimizer may decide to go for a FULL TABLE SCAN because the indexed reads are an overhead. Whereas if you added SYSTEM_CODE to the index the entire query could be satisfied by an INDEX RANGE SCAN, which would be a lot faster.

Finally, how fresh are your statistics? If your statistics are stale, that might lead the optimizer to make a duff decision. For instance, more accurate statistics might lead the optimizer to use the compound index even with just the two leading columns.

Answer 6

Check the datatypes for HEAD and MBR. The values "687422 and 23102" have the 'feel' of being quite selective. That is, if you have hundreds of thousands of values for head and millions of records in the table, it would seem that HEAD is quite selective. [That could be totally misleading though.]

Anyway, you may find that HEAD and/or MBR are actually stored as VARCHAR2 or CHAR fields rather than NUMBER. If so, comparing the character to a number would prevent the use of the index. Try the following (and I've included the conversion of the dob predicate with 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';