gpt4 book ai didi

sql - 在子查询中按级别连接慢

转载 作者:行者123 更新时间:2023-12-05 01:13:26 24 4
gpt4 key购买 nike

我在 Oracle 数据库中使用 connect_by_level 子查询来提供与另一个表连接的日期。按级别连接似乎导致查询运行非常缓慢。这是我的慢查询:

select t.code, d.month_end, count(*)
from device t,
(select add_months(trunc(sysdate, 'MM'), - (level - 1)) - 1 MONTH_END
from dual
connect by level <= 1) d
where (d.month_end between t.date and t.exp_date)
group by t.code, d.month_end

前几天,上面的慢速查询运行了大约 2 个小时。下面的查询应该是等效的,运行时间不到 30 秒:

select t.code, trunc(sysdate, 'MM') - 1 month_end, count(*)
from device t
where ((trunc(sysdate, 'MM') - 1) between t.date and t.exp_date)
group by t.code

最终,我希望第一个查询返回过去 24 个月的数据,但我需要弄清楚为什么仅以 1 个月为标准它运行得如此缓慢。关于按级别查询连接导致完成时间缓慢的原因有什么建议吗?

编辑以添加慢速查询的解释计划输出:

| Id  | Operation                                      | Name                     | Rows  | Bytes |         Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 520 | 65520 | 134K (1)| 00:00:03 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10006 | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,06 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,06 | PCWP | |
| 5 | PX SEND HASH | :TQ10005 | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,05 | P->P | HASH |
| 6 | HASH GROUP BY | | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,05 | PCWP | |
|* 7 | HASH JOIN OUTER | | 520 | 65520 | 134K (1)| 00:00:03 | | | Q1,05 | PCWP | |
| 8 | PX RECEIVE | | 520 | 48880 | 134K (1)| 00:00:03 | | | Q1,05 | PCWP | |
| 9 | PX SEND HASH | :TQ10003 | 520 | 48880 | 134K (1)| 00:00:03 | | | Q1,03 | P->P | HASH |
|* 10 | HASH JOIN OUTER BUFFERED | | 520 | 48880 | 134K (1)| 00:00:03 | | | Q1,03 | PCWP | |
| 11 | PX RECEIVE | | | | | | | | Q1,03 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | | | | | | | Q1,01 | P->P | HASH |
| 13 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 14 | NESTED LOOPS | | 276 | 13800 | 50303 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 15 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 16 | PX RECEIVE | | | | | | | | Q1,01 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10000 | | | | | | | | S->P | BROADCAST |
| 18 | VIEW | | 1 | 6 | 3 (0)| 00:00:01 | | | | | |
|* 19 | CONNECT BY WITHOUT FILTERING| | | | | | | | | | |
| 20 | FAST DUAL | | 1 | | 3 (0)| 00:00:01 | | | | | |
| 21 | PX PARTITION RANGE ALL | | 3898K| | 1656 (0)| 00:00:01 | 1 | 75 | Q1,01 | PCWC | |
|* 22 | INDEX RANGE SCAN | FTR_DT_IX1 | 3898K| | 1656 (0)| 00:00:01 | 1 | 75 | Q1,01 | PCWP | |
|* 23 | TABLE ACCESS BY LOCAL INDEX ROWID| FTR_DT | 276 | 12144 | 50300 (0)| 00:00:01 | 1 | 1 | Q1,01 | PCWP | |
| 24 | PX RECEIVE | | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,03 | PCWP | |
| 25 | PX SEND HASH | :TQ10002 | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,02 | P->P | HASH |
| 26 | PX BLOCK ITERATOR | | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,02 | PCWC | |
|* 27 | TABLE ACCESS STORAGE FULL | DTL_HIST_DT | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,02 | PCWP | |
| 28 | PX RECEIVE | | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,05 | PCWP | |
| 29 | PX SEND HASH | :TQ10004 | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,04 | P->P | HASH |
| 30 | PX BLOCK ITERATOR | | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,04 | PCWC | |
|* 31 | TABLE ACCESS STORAGE FULL | POINT_T | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,04 | PCWP | |

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("C"."CODE"="AP"."CODE"(+) AND "C"."POINT_ID"="AP"."POINT_ID"(+))
10 - access("CF"."P_ID"="C"."WTN"(+))
19 - filter(LEVEL<=1)
22 - access("CF"."DATE"<=INTERNAL_FUNCTION("D"."MONTH_END"))
23 - filter(UPPER("CF"."PLAN") LIKE '%DV%' AND ("CF"."FT_CODE"='7370' OR "CF"."FT_CODE"='7371' OR "CF"."FT_CODE"='7372' OR
"CF"."FT_CODE"='7373' OR "CF"."FT_CODE"='7374' OR "CF"."FT_CODE"='7380' OR "CF"."FT_CODE"='7380C' OR "CF"."FT_CODE"='7381' OR
"CF"."FT_CODE"='7381C' OR "CF"."FT_CODE"='7382' OR "CF"."FT_CODE"='7382C' OR "CF"."FT_CODE"='7383' OR "CF"."FT_CODE"='7384' OR
"CF"."FT_CODE"='7409' OR "CF"."FT_CODE"='7409C' OR "CF"."FT_CODE"='7410' OR "CF"."FT_CODE"='7410C' OR "CF"."FT_CODE"='TRKDV') AND
"CF"."EXP_DATE">=INTERNAL_FUNCTION("D"."MONTH_END"))
27 - storage("C"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "C"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
filter("C"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "C"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
31 - storage("AP"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "AP"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
filter("AP"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "AP"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)

Note
-----
- dynamic sampling used for this statement (level=6)
- automatic DOP: Computed Degree of Parallelism is 8

编辑以添加解释计划以加快查询速度:

| Id  | Operation                             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 33360 | 622K (1)| 00:00:11 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 278 | 33360 | 622K (1)| 00:00:11 | | | Q1,05 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 278 | 33360 | 622K (1)| 00:00:11 | | | Q1,05 | PCWP | |
| 4 | PX RECEIVE | | 4034 | 472K| 622K (1)| 00:00:11 | | | Q1,05 | PCWP | |
| 5 | PX SEND HASH | :TQ10004 | 4034 | 472K| 622K (1)| 00:00:11 | | | Q1,04 | P->P | HASH |
|* 6 | HASH JOIN OUTER BUFFERED | | 4034 | 472K| 622K (1)| 00:00:11 | | | Q1,04 | PCWP | |
| 7 | PX RECEIVE | | 4034 | 346K| 621K (1)| 00:00:11 | | | Q1,04 | PCWP | |
| 8 | PX SEND HASH | :TQ10002 | 4034 | 346K| 621K (1)| 00:00:11 | | | Q1,02 | P->P | HASH |
|* 9 | HASH JOIN OUTER BUFFERED | | 4034 | 346K| 621K (1)| 00:00:11 | | | Q1,02 | PCWP | |
| 10 | PX RECEIVE | | 2140 | 94160 | 538K (1)| 00:00:09 | | | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 2140 | 94160 | 538K (1)| 00:00:09 | | | Q1,00 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 2140 | 94160 | 538K (1)| 00:00:09 | 1 | 75 | Q1,00 | PCWC | |
|* 13 | TABLE ACCESS STORAGE FULL| FTR_DT | 2140 | 94160 | 538K (1)| 00:00:09 | 1 | 75 | Q1,00 | PCWP | |
| 14 | PX RECEIVE | | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,02 | PCWP | |
| 15 | PX SEND HASH | :TQ10001 | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,01 | P->P | HASH |
| 16 | PX BLOCK ITERATOR | | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,01 | PCWC | |
|* 17 | TABLE ACCESS STORAGE FULL| DTL_HIST_DT | 7344K| 308M| 83875 (1)| 00:00:02 | | | Q1,01 | PCWP | |
| 18 | PX RECEIVE | | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 19 | PX SEND HASH | :TQ10003 | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,03 | P->P | HASH |
| 20 | PX BLOCK ITERATOR | | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,03 | PCWC | |
|* 21 | TABLE ACCESS STORAGE FULL | POINT_T | 108K| 3376K| 275 (0)| 00:00:01 | | | Q1,03 | PCWP | |

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("C"."CODE"="AP"."CODE"(+) AND "C"."POINT_ID"="AP"."POINT_ID"(+))
9 - access("CF"."P_ID"="C"."WTN"(+))
13 - storage(UPPER("CF"."PLAN") LIKE '%DV%' AND ("CF"."FT_CODE"='7370' OR "CF"."FT_CODE"='7371' OR "CF"."FT_CODE"='7372' OR
"CF"."FT_CODE"='7373' OR "CF"."FT_CODE"='7374' OR "CF"."FT_CODE"='7380' OR "CF"."FT_CODE"='7380C' OR "CF"."FT_CODE"='7381' OR
"CF"."FT_CODE"='7381C' OR "CF"."FT_CODE"='7382' OR "CF"."FT_CODE"='7382C' OR "CF"."FT_CODE"='7383' OR "CF"."FT_CODE"='7384'
OR "CF"."FT_CODE"='7409' OR "CF"."FT_CODE"='7409C' OR "CF"."FT_CODE"='7410' OR "CF"."FT_CODE"='7410C' OR
"CF"."FT_CODE"='TRKDV') AND "CF"."EXP_DATE">=TRUNC(SYSDATE@!,'fmmm')-1 AND "CF"."DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
filter(UPPER("CF"."PLAN") LIKE '%DV%' AND ("CF"."FT_CODE"='7370' OR "CF"."FT_CODE"='7371' OR "CF"."FT_CODE"='7372' OR
"CF"."FT_CODE"='7373' OR "CF"."FT_CODE"='7374' OR "CF"."FT_CODE"='7380' OR "CF"."FT_CODE"='7380C' OR "CF"."FT_CODE"='7381' OR
"CF"."FT_CODE"='7381C' OR "CF"."FT_CODE"='7382' OR "CF"."FT_CODE"='7382C' OR "CF"."FT_CODE"='7383' OR "CF"."FT_CODE"='7384'
OR "CF"."FT_CODE"='7409' OR "CF"."FT_CODE"='7409C' OR "CF"."FT_CODE"='7410' OR "CF"."FT_CODE"='7410C' OR
"CF"."FT_CODE"='TRKDV') AND "CF"."EXP_DATE">=TRUNC(SYSDATE@!,'fmmm')-1 AND "CF"."DATE"<=TRUNC(SYSDATE@!,'fmmm')-1)
17 - storage("C"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "C"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
filter("C"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "C"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
21 - storage("AP"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "AP"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)
filter("AP"."EXP_DATE"(+)>=TRUNC(SYSDATE@!,'fmmm')-1 AND "AP"."DATE"(+)<=TRUNC(SYSDATE@!,'fmmm')-1)

Note
-----
- dynamic sampling used for this statement (level=6)
- automatic DOP: Computed Degree of Parallelism is 8

最佳答案

在第一个查询中,Oracle 在一个大表上进行索引范围扫描,并在第一遍中检索大量数据 access("CF"."DATE"<=INTERNAL_FUNCTION("D"."MONTH_END")).您可以通过重写您的选择来解决它,使其更像您的快速选择。它会是这样的 -

select t.code, trunc(t.exp_date, 'MM') - 1 month_end, count(*)
from device t
where t.date < :report_period_end
and t.exp_date > :report_period_start
group by t.code, trunc(t.exp_date,'MM') - 1;

另请注意,您的原始慢速查询存在几个潜在问题:

  1. t.date 和 t.exp_date 相隔超过 2 个月的行将被计​​算多次。
  2. t.date 和 t.exp_date 具有相同月份的行将不被计算在内
  3. 您正在比较您的日期,例如2013.10.30 00:00:00。如果您的日期包含时间数据,结果可能不正确。

关于sql - 在子查询中按级别连接慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17706560/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com