oracle中快照空间不足,sql - 如何使此Oracle SQL查询更高效，更少冗余，以避免在大型数据集上出现“快照”错误？ - 堆栈内存溢出...

我正在尝试提高查询大型数据集的技能。

我想要在特定日期范围内使用“ xyz”支付码付款，其中“ id1”不为0且不为null，并且如果对特定“ id1”的付款超过13次，这应该是我查询的结果。

我感觉好像在重复查询两次付款表，但是我不确定如何在一次查询中获取ID1计数和具有特定支付码的付款。

如果我在大量数据上运行它，它将非常慢，并且无法完成。 我收到“快照”错误。

在下面的联接/查询中正确指示了所有表/数据关系，但是如何使它更优雅，更高效？

SELECT

emp.emp_fname first_name,

emp.emp_lname last_name,

pmt.id1,

pmt.id2,

pmt.pay_date,

pmt.pay_key,

pmt.check_num,

case.file_num

FROM

/*query to collect all valid id1s that occur more than 13 times */

(

SELECT

id1

FROM

(

SELECT

id1,

COUNT(*) AS cnt

FROM

payment pmt

WHERE

pay_key IN (

SELECT DISTINCT

pay_key

FROM

allotment

WHERE

pay_code = 'xyz'

)

AND

id1 <> 0

AND

id1 IS NOT NULL

AND

trunc(pmt.pay_date) BETWEEN TO_DATE('01-JUL-17') AND TO_DATE('01-OCT-17')

AND

upper(TRIM(deleted) ) = 'N'

GROUP BY

id1

)

WHERE

cnt > 13

) ptxids

/*join these to full-payment records with matchinig id1s during this time*/

INNER JOIN (

SELECT

*

FROM

payment

WHERE

upper(TRIM(deleted) ) = 'N'

AND

id1 <> 0

AND

id1 IS NOT NULL

AND

trunc(payment.pay_date) BETWEEN TO_DATE('01-JUL-17') AND TO_DATE('01-OCT-17')

) pmt ON pmt.id1 = ptxids.id1

/*join on allotments where pay_key matches and allotment pay_code is xyz*/

INNER JOIN allotment alt ON

upper(TRIM(alt.deleted) ) = 'N'

AND

alt.pay_code = 'xyz'

AND

alt.pay_key = pmt.pay_key

/*join on case to return the requested file-num*/

INNER JOIN case ON case.event_num = alt.event_num

/*join on emp to return payee / employee name */

INNER JOIN emp ON emp.event_num = case.event_num;