PLSQL块需要很长时间才能声明光标

Question

我正在执行一个PL/SQL块，该块从显式光标更新一些行。声明光标的代码如下：

cursor DUP_SUBJECTS is  
  select * 
    from ODS_SUBJECT_D  
   WHERE SUBJECT_COD = ANY (SELECT SUBJECT_COD  
                              FROM ODS_SUBJECT_D  
                             WHERE END_DATE = TO_DATE ('31-12-9999','DD-MM-YYYY')   
                             GROUP BY SUBJECT_COD, ROW_TYPE_DE  
                            HAVING COUNT(*) > 1)  
   ORDER BY SUBJECT_COD, START_DATE; 

身体中的第一个语句是DBMS_OUTPUT.PUT_LINE，以便在开始执行块时通知它。上面脚本中的查询返回表中2900000行中的20000行。在脚本的声明块中似乎需要很长时间（30分钟后它尚未打印消息）。
有任何建议以优化脚本的性能吗？

谢谢，
安东尼奥

Answer 1

您误解了DBMS_OUTPUT的工作原理。客户无法接收发送到DBMS_OUTPUT的消息，直到您的代码块完成整体运行。您可以通过在DBMS_OUTPUT消息中添加时间戳来验证这一点。我怀疑您会看到第一个消息几乎立即写成，并且实际的瓶颈在其他地方。

Answer 2

您可以尝试另一种编写查询的方式：

cursor dup_subjects is  
  select
    * 
  from (
    select
      o.*,
      count(*) over (partition by subject_cod, row_type_de) count_per
    from
      ods_subject_d
    where
      end_date = date '9999-12-31')
  where
    count_per > 1
  order by
    subject_cod,
    start_date;

如果不是很常见的值，请确保索引END_DATE - 如果您不经常在end_date上查询以外的值，则该值除外，然后考虑索引：

create index ...
on     ods_subject_d (
         case end_date
           when date '9999-12-31' then 1
         end)

...然后将该谓词更改为...

where case end_date when date '9999-12-31' then 1 end = 1

有可能进一步完善索引和查询，并有更多了解值的分布以及满足" count（*）> 1"条件的可能性。

Answer 3

如果您的PL/SQL块需要更长的时间运行，并且不会退出程序，然后，您需要在适当的列上创建索引。