Oracle query optimization

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 request takes 153 seconds. to VERIFICATION_TABLEhave millions of rows.

I think the request takes a lot of time due to functions in where where. However, I need to do ltrim rtrim for the columns as well as the date in format MM/DD/YYYY. How can I optimize this query?

Explain the 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 

Indices:

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

Although in terms of explanation, I do not see indexes / primary key. what a problem?

Answer 1

Try the following:

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

TRUNCATE(), dob , ( ?) . , . , mbr head 2 , , , .

Answer 2

, , N_VRFTN_IDX2, , WHERE: HEAD, MBR, DOB LNAME.

, DOB, LNAME, . , , HEAD + MBR . TO_CHAR() DOB, N_VRFTN_IDX2, . , TRIM() LNAME?

, SYSTEM_CODE , ( ). N_VRFTN_IDX2 , FULL TABLE SCAN, . SYSTEM_CODE , INDEX RANGE SCAN, .

, ? , , . , .

Answer 3

DATE, VARCHAR2, :

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

ANSI:

AND  dob = DATE '2004-08-10'

dob ( , !), :

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

Answer 4

HEAD MBR. "687422 23102" "", . , , , HEAD . [ .]

, , HEAD / MBR VARCHAR2 CHAR, NUMBER. , . ( dob , ).

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

Answer 5

EXPLAIN- , , . :

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

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

and use index based function on

RTRIM(LTRIM(lname)) 

Answer 6

Try the following:

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

Thus, a possible dob index will be used.