优化Oracle查询 [英] Optimizing Oracle query

问题描述

  SELECT MAX（verification_id）
 FROM VERIFICATION_TABLE 
 WHERE head = 687422 
 AND mbr = 23102 
 AND RTRIM（LTRIM ）='.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 
表访问已满P181 VERIFICATION_TABLE 80604 1 59 
  

主键：

  VRFTN_PK主要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 
  

但是，在解释计划中，我不看看索引/主键被使用。

$

 >  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（） if dob 没有时间了，从它的外观（出生日期？）它可能不是。过去，你需要一些索引工作。如果你在这种风格中查询太多，我会在2列索引中索引 mbr 和 head 如果你说列是什么意思，这将有助于确定这里的最佳索引。

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?

解决方案

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.

这篇关于优化Oracle查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！