我们有一个过程，它使用一个巨大的SQL查询来检索信息，然后将其插入另一个表XX_REP_TABLE中。SQL查询如下：
SELECT
xx_det.ledger_id,
<< ... more where clauses ... >>
nvl(xx_line.tax_calculation_formula, 'STANDARD_TC') tax_line_user_attribute14,
CASE
WHEN COUNT(*) OVER(PARTITION BY xxx_rcpt_line.accounting_class_code, xxx_rcpt_dist.source_distribution_id_num_1, xxx_rcpt_dist.
event_id) = 1 THEN
NULL
WHEN ( ( xxx_rcpt_line.overridden_code_combination_id IS NULL
AND xxx_rcpt_line.override_reason IS NOT NULL )
OR ( EXISTS (
SELECT
'Reversal of original due to override exists'
FROM
xxx_ae_lines xal2,
xxx_distribution_links xdl2
WHERE
xdl2.application_id = 999
AND xal2.application_id = 999
AND xal2.ae_header_id = xdl2.ae_header_id
AND xal2.ae_line_num = xdl2.ae_line_num
AND xal2.overridden_code_combination_id IS NULL
AND xal2.override_reason IS NOT NULL
AND xdl2.source_distribution_type = 'xx_DISTRIBUTIONS_ALL'
AND xal2.ledger_id = xxx_rcpt_line.ledger_id
AND xdl2.source_distribution_id_num_1 = xxx_rcpt_dist.source_distribution_id_num_1
AND xdl2.ref_ae_header_id = xxx_rcpt_dist.ae_header_id
AND xdl2.ref_ae_line_num = xxx_rcpt_dist.ae_line_num
) ) ) THEN
'DEL'
ELSE
NULL
END tax_line_user_attribute15
<< ... more where clauses ... >>
FROM
xx_lines_det_factors xx_det,
xx_lines xx_line,
xx_exemptions xx_ex,
xx_taxes_b xx_tax,
xx_accounts xx_accounts,
xx_rates_vl xx_rate,
xxx_events xxx_event,
xxx_ae_headers xxx_head,
xxx_ae_lines xxx_line,
xxx_acct_class_assgns acs,
xxx_assignment_defns_b asd,
xxx_distribution_links xxx_dist,
xx_receivable_applications_all ara,
xxx_events xxx_rcpt_event,
xxx_ae_headers xxx_rcpt_head,
xxx_ae_lines xxx_rcpt_line,
xxx_distribution_links xxx_rcpt_dist,
xx_distributions_all ardist,
xx_cust_trx_line_gl_dist_all trx_dist,
xx_customer_trx_lines_all lines
WHERE
xx_det.tax_reporting_flag = 'Y'
<< ... more where clauses ... >>
AND 1 = 1;
解释计划如下：
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | exeOrd| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 66 | SELECT STATEMENT | | | | | 283K(100)| |
| 1 | 65 | NESTED LOOPS SEMI | | 1 | 319 | | 7 (0)| 00:00:01 |
| 2 | 62 | TABLE ACCESS BY INDEX ROWID BATCHED | xxx_distribution_links | 1 | 50 | | 5 (0)| 00:00:01 |
| 3 | 61 | INDEX RANGE SCAN | xxx_distribution_links_N1 | 2 | | | 4 (0)| 00:00:01 |
| 4 | 64 | TABLE ACCESS BY INDEX ROWID | xxx_ae_lines | 1 | 269 | | 2 (0)| 00:00:01 |
| 5 | 63 | INDEX UNIQUE SCAN | xxx_ae_lines_U1 | 1 | | | 1 (0)| 00:00:01 |
| 6 | 60 | WINDOW SORT | | 1 | 2792 | | 283K (1)| 00:00:12 |
| 7 | 59 | FILTER | | | | | | |
| 8 | 58 | NESTED LOOPS OUTER | | 1 | 2792 | | 283K (1)| 00:00:12 |
| 9 | 56 | NESTED LOOPS | | 1 | 2753 | | 283K (1)| 00:00:12 |
| 10 | 53 | NESTED LOOPS | | 1 | 2743 | | 283K (1)| 00:00:12 |
| 11 | 50 | NESTED LOOPS | | 1 | 2723 | | 283K (1)| 00:00:12 |
| 12 | 47 | NESTED LOOPS | | 1 | 2696 | | 283K (1)| 00:00:12 |
| 13 | 44 | NESTED LOOPS | | 1 | 2654 | | 283K (1)| 00:00:12 |
| 14 | 41 | HASH JOIN | | 1 | 2630 | | 283K (1)| 00:00:12 |
| 15 | 39 | NESTED LOOPS | | 1 | 2585 | | 232K (1)| 00:00:10 |
| 16 | 36 | NESTED LOOPS | | 1 | 2493 | | 232K (1)| 00:00:10 |
| 17 | 33 | NESTED LOOPS | | 15 | 36750 | | 232K (1)| 00:00:10 |
| 18 | 30 | HASH JOIN | | 2455 | 3735K| | 225K (1)| 00:00:09 |
| 19 | 1 | TABLE ACCESS STORAGE FULL | xx_rates_tl | 82 | 8938 | | 5 (0)| 00:00:01 |
| 20 | 29 | HASH JOIN | | 2455 | 3473K| | 225K (1)| 00:00:09 |
| 21 | 2 | TABLE ACCESS STORAGE FULL | xx_rates_b | 82 | 6970 | | 3 (0)| 00:00:01 |
| 22 | 28 | HASH JOIN | | 2455 | 3270K| | 225K (1)| 00:00:09 |
| 23 | 3 | TABLE ACCESS STORAGE FULL | xx_rates_b | 21 | 546 | | 5 (0)| 00:00:01 |
| 24 | 27 | NESTED LOOPS | | 2455 | 3207K| | 225K (1)| 00:00:09 |
| 25 | 25 | NESTED LOOPS | | 9516 | 3207K| | 225K (1)| 00:00:09 |
| 26 | 23 | HASH JOIN | | 2379 | 1119K| 8072K| 218K (1)| 00:00:09 |
| 27 | 20 | JOIN FILTER CREATE | :BF0000 | 2379 | 1119K| | 218K (1)| 00:00:09 |
| 28 | 19 | HASH JOIN | | 18159 | 7855K| 7680K| 190K (1)| 00:00:08 |
| 29 | 16 | JOIN FILTER CREATE | :BF0001 | 18159 | 7855K| | 190K (1)| 00:00:08 |
| 30 | 15 | HASH JOIN | | 18159 | 7465K| 7112K| 168K (1)| 00:00:07 |
| 31 | 12 | JOIN FILTER CREATE | :BF0002 | 18159 | 6898K| | 115K (1)| 00:00:05 |
| 32 | 11 | MERGE JOIN CARTESIAN | | 18159 | 6898K| | 115K (1)| 00:00:05 |
| 33 | 7 | NESTED LOOPS | | 1 | 120 | | 2 (0)| 00:00:01 |
| 34 | 5 | TABLE ACCESS BY INDEX ROWID | xxx_assignment_defns_b | 1 | 53 | | 2 (0)| 00:00:01 |
| 35 | 4 | INDEX SKIP SCAN | xxx_assignment_defns_b_U1 | 1 | | | 1 (0)| 00:00:01 |
| 36 | 6 | INDEX UNIQUE SCAN | xxx_ACCT_CLASS_ASSGNS_U1 | 1 | 67 | | 0 (0)| |
| 37 | 10 | BUFFER SORT | | 329K| 84M| | 115K (1)| 00:00:05 |
| 38 | 9 | TABLE ACCESS BY INDEX ROWID BATCHED| xxx_ae_lines | 329K| 84M| | 115K (1)| 00:00:05 |
| 39 | 8 | INDEX RANGE SCAN | xxx_ae_lines_N2 | 2084K| | | 6741 (1)| 00:00:01 |
| 40 | 14 | JOIN FILTER USE | :BF0002 | 844K| 25M| | 50709 (1)| 00:00:02 |
| 41 | 13 | TABLE ACCESS STORAGE FULL | xxx_ae_headers | 844K| 25M| | 50709 (1)| 00:00:02 |
| 42 | 18 | JOIN FILTER USE | :BF0001 | 2526K| 53M| | 18048 (1)| 00:00:01 |
| 43 | 17 | TABLE ACCESS STORAGE FULL | xxx_events | 2526K| 53M| | 18048 (1)| 00:00:01 |
| 44 | 22 | JOIN FILTER USE | :BF0000 | 319K| 11M| | 27552 (1)| 00:00:02 |
| 45 | 21 | TABLE ACCESS STORAGE FULL | xx_receivable_applications_all | 319K| 11M| | 27552 (1)| 00:00:02 |
| 46 | 24 | INDEX RANGE SCAN | xx_LINES_N4 | 4 | | | 2 (0)| 00:00:01 |
| 47 | 26 | TABLE ACCESS BY INDEX ROWID | xx_lines | 1 | 856 | | 3 (0)| 00:00:01 |
| 48 | 32 | TABLE ACCESS BY INDEX ROWID BATCHED | xx_lines_det_factors | 1 | 892 | | 3 (0)| 00:00:01 |
| 49 | 31 | INDEX RANGE SCAN | xx_LINES_DET_FACTORS_N2 | 3 | | | 2 (0)| 00:00:01 |
| 50 | 35 | TABLE ACCESS BY INDEX ROWID BATCHED | xx_distributions_all | 1 | 43 | | 3 (0)| 00:00:01 |
| 51 | 34 | INDEX RANGE SCAN | xx_DISTRIBUTIONS_N1 | 1 | | | 2 (0)| 00:00:01 |
| 52 | 38 | TABLE ACCESS BY INDEX ROWID BATCHED | xxx_distribution_links | 1 | 92 | | 3 (0)| 00:00:01 |
| 53 | 37 | INDEX RANGE SCAN | xxx_distribution_links_N3 | 1 | | | 2 (0)| 00:00:01 |
| 54 | 40 | TABLE ACCESS STORAGE FULL | xxx_ae_headers | 26278 | 1154K| | 50711 (1)| 00:00:02 |
| 55 | 43 | TABLE ACCESS BY INDEX ROWID BATCHED | xxx_ae_lines | 1 | 24 | | 3 (0)| 00:00:01 |
| 56 | 42 | INDEX RANGE SCAN | xxx_ae_lines_U1 | 1 | | | 2 (0)| 00:00:01 |
| 57 | 46 | TABLE ACCESS BY INDEX ROWID BATCHED | xxx_distribution_links | 1 | 42 | | 3 (0)| 00:00:01 |
| 58 | 45 | INDEX RANGE SCAN | xxx_distribution_links_N3 | 1 | | | 2 (0)| 00:00:01 |
| 59 | 49 | TABLE ACCESS BY INDEX ROWID | xx_cust_trx_line_gl_dist_all | 1 | 27 | | 2 (0)| 00:00:01 |
| 60 | 48 | INDEX UNIQUE SCAN | xx_CUST_TRX_LINE_GL_DIST_U1 | 1 | | | 1 (0)| 00:00:01 |
| 61 | 52 | TABLE ACCESS BY INDEX ROWID | xx_customer_trx_lines_all | 1 | 20 | | 2 (0)| 00:00:01 |
| 62 | 51 | INDEX UNIQUE SCAN | xx_CUSTOMER_TRX_LINES_U1 | 1 | | | 1 (0)| 00:00:01 |
| 63 | 55 | TABLE ACCESS BY INDEX ROWID BATCHED | xxx_events | 1 | 10 | | 3 (0)| 00:00:01 |
| 64 | 54 | INDEX RANGE SCAN | xxx_EVENTS_U1 | 1 | | | 2 (0)| 00:00:01 |
| 65 | 57 | INDEX UNIQUE SCAN | xx_ACCOUNTS_U2 | 1 | 39 | | 0 (0)| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
根据我们的一些分析，这是由SELECTed列中的COUNT(*) OVER(PARTITION BY引起的。当我删除该分析函数时，解释计划会删除全表扫描：
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | exeOrd| Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 55 | SELECT STATEMENT | | | | 504K(100)| |
| 1 | 54 | NESTED LOOPS | | 1 | 2537 | 504K (1)| 00:00:20 |
| 2 | 51 | NESTED LOOPS | | 1 | 2517 | 504K (1)| 00:00:20 |
| 3 | 48 | NESTED LOOPS | | 1 | 2490 | 504K (1)| 00:00:20 |
| 4 | 45 | NESTED LOOPS | | 1 | 2448 | 504K (1)| 00:00:20 |
| 5 | 42 | NESTED LOOPS | | 1 | 2422 | 504K (1)| 00:00:20 |
| 6 | 39 | NESTED LOOPS | | 1 | 1523 | 504K (1)| 00:00:20 |
| 7 | 36 | NESTED LOOPS | | 9 | 13383 | 504K (1)| 00:00:20 |
| 8 | 33 | NESTED LOOPS | | 9 | 13293 | 504K (1)| 00:00:20 |
|* 9 | 30 | HASH JOIN | | 392 | 546K| 504K (1)| 00:00:20 |
| 10 | 27 | NESTED LOOPS | | 1 | 1403 | 340K (1)| 00:00:14 |
| 11 | 24 | NESTED LOOPS | | 1 | 1381 | 340K (1)| 00:00:14 |
|* 12 | 21 | HASH JOIN | | 1 | 1271 | 340K (1)| 00:00:14 |
| 13 | 19 | NESTED LOOPS | | 1 | 1186 | 340K (1)| 00:00:14 |
| 14 | 16 | NESTED LOOPS | | 2752 | 857K| 333K (1)| 00:00:14 |
| 15 | 13 | NESTED LOOPS | | 12119 | 3313K| 311K (1)| 00:00:13 |
| 16 | 10 | NESTED LOOPS | | 28044 | 6490K| 254K (1)| 00:00:10 |
| 17 | 7 | NESTED LOOPS | | 22839 | 3234K| 163K (1)| 00:00:07 |
| 18 | 4 | NESTED LOOPS | | 1 | 120 | 2 (0)| 00:00:01 |
|* 19 | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| xla_assignment_defns_b | 1 | 53 | 2 (0)| 00:00:01 |
|* 20 | 1 | INDEX SKIP SCAN | XLA_ASSIGNMENT_DEFNS_B_U1 | 1 | | 1 (0)| 00:00:01 |
|* 21 | 3 | INDEX UNIQUE SCAN | XLA_ACCT_CLASS_ASSGNS_U1 | 1 | 67 | 0 (0)| |
|* 22 | 6 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_lines | 414K| 9M| 163K (1)| 00:00:07 |
|* 23 | 5 | INDEX RANGE SCAN | XLA_AE_LINES_N2 | 2824K| | 9684 (1)| 00:00:01 |
|* 24 | 9 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_distribution_links | 1 | 92 | 4 (0)| 00:00:01 |
|* 25 | 8 | INDEX RANGE SCAN | XLA_DISTRIBUTION_LINKS_N3 | 1 | | 3 (0)| 00:00:01 |
|* 26 | 12 | TABLE ACCESS BY INDEX ROWID | ar_distributions_all | 1 | 43 | 2 (0)| 00:00:01 |
|* 27 | 11 | INDEX UNIQUE SCAN | AR_DISTRIBUTIONS_U2 | 1 | | 1 (0)| 00:00:01 |
|* 28 | 15 | TABLE ACCESS BY INDEX ROWID | ar_receivable_applications_all | 1 | 39 | 2 (0)| 00:00:01 |
|* 29 | 14 | INDEX UNIQUE SCAN | AR_RECEIVABLE_APPLICATIONS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 30 | 18 | TABLE ACCESS BY INDEX ROWID BATCHED | zx_lines | 1 | 867 | 3 (0)| 00:00:01 |
|* 31 | 17 | INDEX RANGE SCAN | ZX_LINES_N4 | 4 | | 2 (0)| 00:00:01 |
| 32 | 20 | TABLE ACCESS STORAGE FULL | zx_rates_b | 1 | 85 | 1 (0)| 00:00:01 |
| 33 | 23 | TABLE ACCESS BY INDEX ROWID | zx_rates_tl | 1 | 110 | 1 (0)| 00:00:01 |
|* 34 | 22 | INDEX UNIQUE SCAN | ZX_RATES_TL_U1 | 1 | | 0 (0)| |
|* 35 | 26 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_events | 1 | 22 | 3 (0)| 00:00:01 |
|* 36 | 25 | INDEX RANGE SCAN | XLA_EVENTS_U1 | 1 | | 2 (0)| 00:00:01 |
|* 37 | 29 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_ae_lines | 414K| 9M| 163K (1)| 00:00:07 |
|* 38 | 28 | INDEX RANGE SCAN | XLA_AE_LINES_N2 | 2824K| | 9685 (1)| 00:00:01 |
|* 39 | 32 | TABLE ACCESS BY INDEX ROWID | xla_ae_headers | 1 | 49 | 2 (0)| 00:00:01 |
|* 40 | 31 | INDEX UNIQUE SCAN | XLA_AE_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 41 | 35 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_events | 1 | 10 | 3 (0)| 00:00:01 |
|* 42 | 34 | INDEX RANGE SCAN | XLA_EVENTS_U1 | 1 | | 2 (0)| 00:00:01 |
|* 43 | 38 | TABLE ACCESS BY INDEX ROWID | xla_ae_headers | 1 | 36 | 2 (0)| 00:00:01 |
|* 44 | 37 | INDEX UNIQUE SCAN | XLA_AE_HEADERS_U1 | 1 | | 1 (0)| 00:00:01 |
|* 45 | 41 | TABLE ACCESS BY INDEX ROWID BATCHED | zx_lines_det_factors | 1 | 899 | 3 (0)| 00:00:01 |
|* 46 | 40 | INDEX RANGE SCAN | ZX_LINES_DET_FACTORS_N2 | 3 | | 2 (0)| 00:00:01 |
| 47 | 44 | TABLE ACCESS BY INDEX ROWID | zx_taxes_b | 1 | 26 | 1 (0)| 00:00:01 |
|* 48 | 43 | INDEX UNIQUE SCAN | ZX_TAXES_B_U1 | 1 | | 0 (0)| |
|* 49 | 47 | TABLE ACCESS BY INDEX ROWID BATCHED | xla_distribution_links | 1 | 42 | 4 (0)| 00:00:01 |
|* 50 | 46 | INDEX RANGE SCAN | XLA_DISTRIBUTION_LINKS_N3 | 1 | | 3 (0)| 00:00:01 |
|* 51 | 50 | TABLE ACCESS BY INDEX ROWID | ra_cust_trx_line_gl_dist_all | 1 | 27 | 2 (0)| 00:00:01 |
|* 52 | 49 | INDEX UNIQUE SCAN | RA_CUST_TRX_LINE_GL_DIST_U1 | 1 | | 1 (0)| 00:00:01 |
|* 53 | 53 | TABLE ACCESS BY INDEX ROWID | ra_customer_trx_lines_all | 1 | 20 | 2 (0)| 00:00:01 |
|* 54 | 52 | INDEX UNIQUE SCAN | RA_CUSTOMER_TRX_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------------
我计划将分析函数转移到一个单独的过程中，类似于下面的内容(还不是确切的过程(：
procedure Identify_duplicates (p_request_id number)
as
cursor cur_duplicate_information (p_request_id number)
is
SELECT
REP_TBL_ID, -- UNIQUE IDENTIFIER
CASE
WHEN COUNT(*) OVER(PARTITION BY xxrtbl.accounting_class_code, xxrtbl.source_distribution_id_num_1, xxrtbl.event_id) = 1 THEN
NULL
WHEN ( ( xxx_rcpt_line.overridden_code_combination_id IS NULL
AND xxx_rcpt_line.override_reason IS NOT NULL )
OR ( EXISTS (
SELECT
'Reversal of original due to override exists'
FROM
xxx_ae_lines xal2,
xxx_distribution_links xdl2
WHERE
xdl2.application_id = 999
AND xal2.application_id = 999
AND xal2.ae_header_id = xdl2.ae_header_id
AND xal2.ae_line_num = xdl2.ae_line_num
AND xal2.overridden_code_combination_id IS NULL
AND xal2.override_reason IS NOT NULL
AND xdl2.source_distribution_type = 'xx_DISTRIBUTIONS_ALL'
AND xal2.ledger_id = xxrtbl.ledger_id
AND xdl2.source_distribution_id_num_1 = xxrtbl.source_distribution_id_num_1
AND xdl2.ref_ae_header_id = xxrtbl.ae_header_id
AND xdl2.ref_ae_line_num = xxrtbl.ae_line_num
) ) ) THEN
'DEL'
ELSE
NULL
END tax_line_user_attribute15
FROM XX_REP_TABLE xxrtbl
WHERE REQ_ID = p_request_id;
type typ_dup_info cur_duplicate_information%type;
rec_dup_info typ_dup_info;
begin
OPEN cur_duplicate_information;
FETCH cur_duplicate_information
BULK COLLECT
INTO rec_dup_info
CLOSE cur_duplicate_information;
forall rec_dup_info.first .. rec_dup_info.last
UPDATE XX_REP_TABLE
SET tax_line_user_attribute15 = rec_dup_info(i).tax_line_user_attribute15
where REP_TBL_ID = REP_TBL_ID(i).REP_TBL_ID;
exception
when others then
dbms_output.put_line('Error in :'||SQLERRM);
raise;
end;
我很想知道这是否会有什么不同。Will"添加"；这两个解释计划提供了总成本的粗略估计？
注意：我不能从我们自己的实例中运行这个，因为记录太少，无法产生实际影响。这个性能问题来自我们的客户，他们有数十亿条记录，我们无法导出或模仿。因此，我想"；理论上"；在进行代码修复之前先计算它。
此查询
SELECT a, b, COUNT(*) OVER (PARTITION BY b)
FROM table
给出与类似的结果
SELECT t.a, t.b, q.number
FROM table t
JOIN ( SELECT b, COUNT(*) number
FROM table
GROUP BY b
) q ON t.b = q.b
第二项中的子查询可以利用b上的索引。
如果没有看到整个查询，很难猜测如何重构它以使用这样的子查询。
顺便说一句，只要你正在处理这段代码，你就应该考虑通过使用显式JOIN将其带入20世纪90年代。而不是这个
SELECT ...
FROM t1, t2, t3
WHERE t1.t1_id = t2.t1_id
AND t2.t2_id = t3.t2_id(+)
使用此
SELECT ...
FROM t1
INNER JOIN t2 ON t1.t1_id = t2.t1_id
LEFT JOIN t3 ON t2.t2_id = t3.t2_id
逗号连接语法在一代以前的SQL-92中被显式连接所取代。