需要帮助优化oracle查询 [英] need help optimizing oracle query

问题描述

我需要帮助来优化以下查询.需要很长时间才能完成.它花费了将近213秒.由于某些限制，我无法添加索引，而只能使用现有索引.

I need help in optimizing the following query. It is taking a long time to finish. It takes almost 213 seconds . because of some constraints, I can not add an index and have to live with existing ones.

INSERT INTO temp_table_1
( USER_ID, role_id, participant_code, status_id )
WITH A AS
 (SELECT USER_ID user_id,ROLE_ID, STATUS_ID,participant_code
  FROM    USER_ROLE WHERE   participant_code IS NOT NULL), --1
B AS
 (SELECT ROLE_ID
  FROM    CMP_ROLE
  WHERE   GROUP_ID = 3),
C AS (SELECT USER_ID FROM USER) --2

SELECT USER_ID,ROLE_ID,PARTICIPANT_CODE,MAX(STATUS_ID)
FROM A INNER JOIN B USING (ROLE_ID)
       INNER JOIN C USING (USER_ID)
GROUP BY USER_ID,role_id,participant_code ;

--1 = query when ran alone takes 100+ seconds

--2 = query when ran alone takes 19 seconds

DELETE temp_table_1
WHERE ROWID NOT IN
( SELECT a.ROWID
  FROM temp_table_1 a,
  USER_ROLE b
  WHERE a.status_id = b.status_id
  AND   ( b.ACTIVE IN ( 1 ) OR ( b.ACTIVE IN ( 0,3 ) 
  AND SYSDATE BETWEEN b.effective_from_date AND b.effective_to_date ))
);

似乎写查询的人试图将所有内容首先放入临时表，然后从临时表中删除记录.剩下的就是实际结果.

It seems like the person who wrote the query is trying to get everything into a temp table first and then deleting records from the temp table. whatever is left is the actual results.

难道不需要删除吗?我们只是得到所需的结果，因为这样可以节省时间?

Can't it be done such a way that there is no need for the delete? We just get the results needed since that will save time?

推荐答案

这里是一个天真的组合了上面两个查询的查询，因此请确保您检查并比较了这两个方法的输出.

Here is a query which naively combines the two queries above, so make sure that you check and compare the outputs from the two methods.

select 
  r.user_id, r.role_id, r.participant_code, max(status_id)
from 
  user_role r, 
  cmp_role c
where 
      r.role_id = c.role_id
  and r.active in (0,1,3)
  and r.participant_code is not null
  and sysdate between r.effective_from_date and r.effective_to_date
  and c.group_id = 3
group by 
  r.user_id, r.role_id, r.participant_code;

不必使用临时表，然后再删除记录以获得所需的结果.尽管可能有使用它的原因，但也许是性能?

It is not necessary to use a temporary table and then delete records afterwards to get the required results. Although, there may have been a reason for its use, maybe performance?

此外，看起来像查询并不需要连接到USER表，因为USER_ID可从USER_ROLES获得.我从上面的查询中省略了它.希望这可以为您改善它提供一个良好的开端.

Also, it looks like the query and join to the USER table is unnecessary as the USER_ID is available from USER_ROLES. I have omitted it from the query above. Hopefully that gives you a good start to improving it.

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