sql - 优化 Oracle 查询

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

此查询运行需要 153 秒。 VERIFICATION_TABLE 中有数百万行。

我认为查询花费很长时间是因为 where 子句中的函数。但是，我需要对列执行 ltrim rtrim 并且日期必须以 MM/DD/YYYY 格式匹配。如何优化此查询？

解释计划:

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

不过，在解释计划中我没有看到索引/主键被使用。是这个问题吗？

Answer 1

试试这个:

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 没有时间，从它的外观来看(出生日期？)它可能没有。除此之外，您还需要一些索引工作。如果您以这种方式查询那么多，我会在 2 列索引中索引 mbr 和 head，如果您说出这些列的含义，它会帮助确定这里最好的索引。