低成本查询的性能
低成本查询的性能
提问于 2020-01-09 15:47:50
我试图优化select语句，但是它似乎成本降低得越多，运行的时间就越长。这里发生什么事情？
我可以在partition_type上添加一个过滤器，这大大降低了查询的成本，我认为这是因为对完整表扫描的需求从解释计划中消失了。还有一个嵌套查询，它返回包含当前活动的partition_key的一行。保留嵌套查询会使成本增加到20 in (全表扫描，尽管是一个小表)。将嵌套查询替换为该查询的实际结果再次降低了成本。所以，如果我像这样运行这个查询：
- 嵌套查询，无partition_type:成本
- 值，无partition_type:成本234.712
- 固定值，partition_type:成本9.611
我的问题：
- 如何使最低成本查询(到目前为止)不比其他查询快得多，但速度慢呢？
- 如何能够对实际分区键的嵌套查询增加这么大的成本？我知道这是一个全表扫描，但它大约是8个字节。
在下面解释计划和查询。
查询：
SELECT CASE WHEN COUNT (*) > 0 THEN 0 ELSE 1 END AS result
FROM MYTABLE.ORDERS O
INNER JOIN MYTABLE.BATCH B
ON B.BATCH_ID = O.IN_BATCH_ID
AND B.PARTITION_KEY = O.PARTITION_KEY
AND B.PARTITION_TYPE = O.PARTITION_TYPE
AND B.INSTANCE_NUMBER = O.INSTANCE_NUMBER
WHERE O.PARTITION_KEY = (SELECT actual_partition_key
FROM MYTABLE.CALENDAR
WHERE is_active = 1) --can replace this with fixed value 123
AND O.partition_type = 3 -- can leave this one out
AND B.START_TIME > SYSDATE - 1 / 2 / 24
AND B.START_TIME < SYSDATE - 10 / 60 / 24
AND O.STATE NOT IN ('993890', '999990')
AND O.RECEIVER IN
(SELECT RECEIVER_ID
FROM ( SELECT MAX (CREATION_DATE) AS CREATION_DATE,
RECEIVER_ID
FROM MYTABLE.RECEIVER_AVAILABILITY
GROUP BY RECEIVER_ID) MAX_CREATION_DATE
INNER JOIN MYTABLE.RECEIVER_AVAILABILITY
ON MAX_CREATION_DATE.CREATION_DATE = CREATION_DATE
WHERE state IN (1, 2)
AND creation_date < SYSDATE - 30 / 60 / 24)最大的查询，没有选择当前分区的partition_type和嵌套查询:运行时:2秒
Plan
SELECT STATEMENT ALL_ROWSCost: 20.233.522 Bytes: 60 Cardinality: 1
21 SORT AGGREGATE Bytes: 60 Cardinality: 1
20 FILTER
10 FILTER
9 NESTED LOOPS Cost: 20.233.494 Bytes: 60 Cardinality: 1
7 NESTED LOOPS Cost: 20.233.494 Bytes: 60 Cardinality: 113
4 PARTITION RANGE ITERATOR Cost: 20.233.267 Bytes: 3.842 Cardinality: 113 Partition #: 6 Partitions determined by Key Values
3 PARTITION HASH ALL Cost: 20.233.267 Bytes: 3.842 Cardinality: 113 Partition #: 7 Partitions accessed #1 - #2
2 TABLE ACCESS FULL TABLE MYTABLE.ORDERS Cost: 20.233.267 Bytes: 3.842 Cardinality: 113 Partition #: 7 Partitions determined by Key Values
1 TABLE ACCESS FULL TABLE MYTABLE.CALENDAR Cost: 4 Bytes: 8 Cardinality: 1
6 PARTITION RANGE ITERATOR Cost: 1 Cardinality: 1 Partition #: 10 Partitions determined by Key Values
5 INDEX UNIQUE SCAN INDEX (UNIQUE) MYTABLE.BATCH_PK_IDX Cost: 1 Cardinality: 1 Partition #: 10 Partitions determined by Key Values
8 TABLE ACCESS BY LOCAL INDEX ROWID TABLE MYTABLE.BATCH Cost: 2 Bytes: 26 Cardinality: 1 Partition #: 10 Partitions accessed #1
19 NESTED LOOPS SEMI Cost: 24 Bytes: 36 Cardinality: 1
15 VIEW myuser. Cost: 23 Bytes: 13 Cardinality: 1
14 FILTER
13 SORT GROUP BY Cost: 23 Bytes: 20 Cardinality: 1
12 PARTITION RANGE ALL Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 17 Partitions accessed #1 - #347
11 INDEX FAST FULL SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 17 Partitions accessed #1 - #347
18 PARTITION RANGE ALL Cost: 1 Bytes: 23 Cardinality: 1 Partition #: 19 Partitions accessed #1 - #347
17 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE MYTABLE.RECEIVER_AVAILABILITY Cost: 1 Bytes: 23 Cardinality: 1 Partition #: 19 Partitions accessed #1 - #347
16 INDEX RANGE SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 0 Cardinality: 1 Partition #: 19 Partitions accessed #1 - #347 当用实际的partition_key 123:运行时:6秒替换partition_key的嵌套查询时
Plan
SELECT STATEMENT ALL_ROWSCost: 234.712 Bytes: 62 Cardinality: 1
21 SORT AGGREGATE Bytes: 62 Cardinality: 1
20 NESTED LOOPS SEMI Cost: 234.712 Bytes: 62 Cardinality: 1
7 NESTED LOOPS Cost: 234.688 Bytes: 60 Cardinality: 1
2 PARTITION RANGE ITERATOR Cost: 234.684 Bytes: 26 Cardinality: 1 Partition #: 4 Partitions accessed #3622 - #3623
1 TABLE ACCESS FULL TABLE MYTABLE.BATCH Cost: 234.684 Bytes: 26 Cardinality: 1 Partition #: 4 Partitions accessed #3622 - #3623
6 PARTITION RANGE ITERATOR Cost: 4 Bytes: 34 Cardinality: 1 Partition #: 6 Partitions determined by Key Values
5 PARTITION HASH ALL Cost: 4 Bytes: 34 Cardinality: 1 Partition #: 7 Partitions accessed #1 - #2
4 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE MYTABLE.ORDERS Cost: 4 Bytes: 34 Cardinality: 1 Partition #: 7 Partitions determined by Key Values
3 INDEX RANGE SCAN INDEX MYTABLE.ORDERS_IN_FILE Cost: 2 Cardinality: 1 Partition #: 7 Partitions determined by Key Values
19 VIEW PUSHED PREDICATE VIEW SYS.VW_NSO_1 Cost: 24 Bytes: 2 Cardinality: 1
18 NESTED LOOPS Cost: 24 Bytes: 36 Cardinality: 1
16 NESTED LOOPS Cost: 24 Bytes: 36 Cardinality: 1
13 VIEW myuser. Cost: 23 Bytes: 13 Cardinality: 1
12 FILTER
11 SORT GROUP BY Cost: 23 Bytes: 20 Cardinality: 1
10 FILTER
9 PARTITION RANGE ALL Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 17 Partitions accessed #1 - #347
8 INDEX FAST FULL SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 17 Partitions accessed #1 - #347
15 PARTITION RANGE ALL Cost: 0 Cardinality: 1 Partition #: 19 Partitions accessed #1 - #347
14 INDEX RANGE SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 0 Cardinality: 1 Partition #: 19 Partitions accessed #1 - #347
17 TABLE ACCESS BY LOCAL INDEX ROWID TABLE MYTABLE.RECEIVER_AVAILABILITY Cost: 1 Bytes: 23 Cardinality: 1 Partition #: 19 Partitions accessed #1 然后，我认为最好的查询是:包含partition_key和partition_type (避免全表扫描)：运行时:8秒
Plan
SELECT STATEMENT ALL_ROWSCost: 9.611 Bytes: 100 Cardinality: 1
22 SORT AGGREGATE Bytes: 100 Cardinality: 1
21 NESTED LOOPS SEMI Cost: 9.611 Bytes: 100 Cardinality: 1
8 NESTED LOOPS Cost: 9.587 Bytes: 98 Cardinality: 1
3 PARTITION RANGE SINGLE Cost: 9.587 Bytes: 26 Cardinality: 1 Partition #: 4 Partitions accessed #3622
2 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE MYTABLE.BATCH Cost: 9.587 Bytes: 26 Cardinality: 1 Partition #: 5 Partitions accessed #3622
1 INDEX SKIP SCAN INDEX (UNIQUE) MYTABLE.BATCH_PK_IDX Cost: 9.586 Cardinality: 1 Partition #: 6 Partitions accessed #3622
7 PARTITION RANGE ITERATOR Cost: 0 Bytes: 39.384 Cardinality: 547 Partition #: 7 Partitions determined by Key Values
6 PARTITION HASH ALL Cost: 0 Bytes: 39.384 Cardinality: 547 Partition #: 8 Partitions accessed #1 - #2
5 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED TABLE MYTABLE.ORDERS Cost: 0 Bytes: 39.384 Cardinality: 547 Partition #: 8 Partitions determined by Key Values
4 INDEX RANGE SCAN INDEX MYTABLE.ORDERS_IN_FILE Cost: 0 Cardinality: 1 Partition #: 8 Partitions determined by Key Values
20 VIEW PUSHED PREDICATE VIEW SYS.VW_NSO_1 Cost: 24 Bytes: 2 Cardinality: 1
19 NESTED LOOPS Cost: 24 Bytes: 36 Cardinality: 1
17 NESTED LOOPS Cost: 24 Bytes: 36 Cardinality: 1
14 VIEW myuser. Cost: 23 Bytes: 13 Cardinality: 1
13 FILTER
12 SORT GROUP BY Cost: 23 Bytes: 20 Cardinality: 1
11 FILTER
10 PARTITION RANGE ALL Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 18 Partitions accessed #1 - #347
9 INDEX FAST FULL SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 22 Bytes: 6.080 Cardinality: 304 Partition #: 18 Partitions accessed #1 - #347
16 PARTITION RANGE ALL Cost: 0 Cardinality: 1 Partition #: 20 Partitions accessed #1 - #347
15 INDEX RANGE SCAN INDEX MYTABLE.RECEIVER_AVAIL_IDX Cost: 0 Cardinality: 1 Partition #: 20 Partitions accessed #1 - #347
18 TABLE ACCESS BY LOCAL INDEX ROWID TABLE MYTABLE.RECEIVER_AVAILABILITY Cost: 1 Bytes: 23 Cardinality: 1 Partition #: 20 Partitions accessed #1 回答 1
Stack Overflow用户
发布于 2020-01-09 16:19:11
试着摆脱隔断部分，因为它是最重的部分。如果您只需要检查表中是否有行，则不需要计数记录。
select result
from
(SELECT 1 as result
from dual
where not exists (select 1
FROM MYTABLE.ORDERS O
INNER JOIN MYTABLE.CALENDAR c on c.actual_partition_key = O.PARTITION_KEY
INNER JOIN MYTABLE.BATCH B
ON B.BATCH_ID = O.IN_BATCH_ID
AND B.PARTITION_KEY = O.PARTITION_KEY
AND B.PARTITION_TYPE = O.PARTITION_TYPE
AND B.INSTANCE_NUMBER = O.INSTANCE_NUMBER
WHERE c.is_active = 1
AND O.partition_type = 3 -- can leave this one out
AND B.START_TIME > SYSDATE - 1 / 2 / 24
AND B.START_TIME < SYSDATE - 10 / 60 / 24
AND O.STATE NOT IN ('993890', '999990')
AND O.RECEIVER IN (SELECT RECEIVER_ID
FROM ( SELECT MAX (CREATION_DATE) AS CREATION_DATE,
RECEIVER_ID
FROM MYTABLE.RECEIVER_AVAILABILITY
GROUP BY RECEIVER_ID) MAX_CREATION_DATE
INNER JOIN MYTABLE.RECEIVER_AVAILABILITY ON MAX_CREATION_DATE.CREATION_DATE = CREATION_DATE
WHERE state IN (1, 2)
AND creation_date < SYSDATE - 30 / 60 / 24)
union all
select 0 as result
from dual)
order by result desc nulls last
fetch next 1 row only;我看到您在DB中有分区。这意味着你上了企业版。我建议您使用dbms_sqltune.report_sql_monitor(sql_id)函数进行调优。它将显示每一步所期望的行数和所获得的行数。只要注意那些差别最大的步骤
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59667622
复制相关文章
相似问题
腾讯云开发者
