在Oracle中通过连接多个表来编写高效的update查询

Question

我有一个复杂的需求，需要通过连接多个表来更新一个表中的一些记录，以找到正确的记录。我已经编写了以下查询，该查询正在工作，但执行它需要2.5分钟，这导致我的数据库连接超时。有没有办法通过重写来提高这个查询的效率？我也尝试过合并方法，这也太耗时了。我在EVENT_DYNAMIC_ATTRIBUTE表中有200多万条记录，在EVENT表中有100万条记录，在CATEGORY表中有10万条记录。

UPDATE EVENT_DYNAMIC_ATTRIBUTE eda
SET eda.ATTRIBUTE_VALUE = 'claim',
    eda.LAST_UPDATED_DATE = SYSDATE,
    eda.LAST_UPDATED_BY = 'superUsers'
WHERE eda.DYNAMIC_ATTRIBUTE_NAME_ID=4002
  AND eda.EVENT_ID IN
    (SELECT e.EVENT_ID
     FROM EVENT e
     WHERE e.PRIMARY_CATEGORY_ID IN
         (SELECT CATEGORY_ID
          FROM CATEGORY START WITH CATEGORY_ID = 495984 CONNECT BY PARENT_ID =
          PRIOR CATEGORY_ID));

这是合并查询：

 MERGE INTO EVENT_DYNAMIC_ATTRIBUTE eda
    USING (SELECT DISTINCT e.EVENT_ID FROM (
         SELECT CATEGORY_ID
         FROM CATEGORY 
         START WITH CATEGORY_ID=495984 
         CONNECT BY PARENT_ID =
         PRIOR CATEGORY_ID) CATEGORIES
    INNER JOIN  EVENT E ON e.PRIMARY_CATEGORY_ID = CATEGORY_ID 
    INNER JOIN  EVENT_DYNAMIC_ATTRIBUTE ed on ed.EVENT_ID = E.EVENT_ID) temp 
    ON (eda.EVENT_ID = temp.EVENT_ID ) 
    WHEN MATCHED THEN 
       UPDATE SET eda.ATTRIBUTE_VALUE = 'claim',
                  eda.LAST_UPDATED_DATE = SYSDATE,
                  eda.LAST_UPDATED_BY = 'superUser'
    WHERE eda.DYNAMIC_ATTRIBUTE_NAME_ID=4002

Answer 1

你可以尝试像这样的东西

update 
  (
    select
      eda.*
    from
      (select * from event_dynamic_attribute where dynamic_attribute_name_id = 4002) eda,
      (select category_id from category start with category_id = 495984 connect by parent_id = prior category_id) c,
      event e
    where
      e.primary_category_id = c.category_id and
      e.event_id = eda.event_id
  )
set
  attribute_value = 'claim', 
  last_updated_date = sysdate,
  last_updated_by = 'superUsers'

但我怀疑这是否有帮助。一般来说，这是非常非常低的速度，即使在旧的硬件上也是如此。您应该跟踪这些更新，并检查它们的执行情况、受影响的块等。

首先，我想请你执行

explain plan for <<your update statement text>>;
select * from table(dbms_xplan.display());

并将其结果写入问题中。又一个..。event_dynamic_attribute表上是否有任何触发器？