oracle - Oracle Hash Join - 探测表:分区上的索引?
问题描述
表P
(父)和C
(子)都有 10 个分区cat
和 316 个子分区effective_date
。表P
有以下索引create index ix_p_cat on p (cat);
。
与执行完整分区访问相比,对优化器而言,使用分区列上的索引进行索引范围扫描怎么可能更可取(成本更低)?
我的想法是,在任何一种情况下都需要来自 P 的相同数量的数据块,因此不妨避免读取额外的索引块。但是,优化器不同意。
以下是两个解释计划。第一个是显示优化器想要使用索引来构建哈希表,第二个是提示不使用索引。
分析了表格和索引。
Oracle 企业版 19c。提前致谢。
select C.some_col
from "P"
join "C"
on P.code = C.code
and P.cat = :cat
and C.cat = :cat
;
------------------------------------------------------------------------------------------------------------------------
| id | Operation | name | rows | Bytes | cost (%CPU)| time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | select statement | | 275G| 5127G| 1280K (58)| 00:00:51 | | |
|* 1 | hash join | | 275G| 5127G| 1280K (58)| 00:00:51 | | |
| 2 | table access by global index ROWID BATCHED| P | 60363 | 412K| 1642 (1)| 00:00:01 | ROWID | ROWID |
|* 3 | index range scan | IX_P_CAT | 60363 | | 231 (0)| 00:00:01 | | |
| 4 | partition LIST single | | 41M| 510M| 539K (1)| 00:00:22 | key | key |
| 5 | partition range all | | 41M| 510M| 539K (1)| 00:00:22 | 1 | 316 |
| 6 | table access full | C | 41M| 510M| 539K (1)| 00:00:22 | | |
------------------------------------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
2 - SEL$58A6D7F6 / p@SEL$1
3 - SEL$58A6D7F6 / p@SEL$1
6 - SEL$58A6D7F6 / C@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."CODE"="C"."CODE")
3 - access("P"."CAT"=:CAT)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "C"."SOME_COL"[NUMBER,22], "C"."SOME_COL"[NUMBER,22]
2 - "P"."CODE"[CHARACTER,2]
3 - "P".ROWID[ROWID,10]
4 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
5 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
6 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
Note
-----
- this is an adaptive plan
无索引
select /*+ no_index(P) */
C.some_col
from "P"
join "C"
on P.code = C.code
and P.cat = :cat
and C.cat = :cat
;
-----------------------------------------------------------------------------------------------
| id | Operation | name | rows | Bytes | cost (%CPU)| time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | select statement | | 275G| 5127G| 1287K (58)| 00:00:51 | | |
|* 1 | hash join | | 275G| 5127G| 1287K (58)| 00:00:51 | | |
| 2 | partition LIST single| | 60363 | 412K| 8152 (1)| 00:00:01 | key | key |
| 3 | partition range all | | 60363 | 412K| 8152 (1)| 00:00:01 | 1 | 316 |
| 4 | table access full | P | 60363 | 412K| 8152 (1)| 00:00:01 | | |
| 5 | partition LIST single| | 41M| 510M| 539K (1)| 00:00:22 | key | key |
| 6 | partition range all | | 41M| 510M| 539K (1)| 00:00:22 | 1 | 316 |
| 7 | table access full | C | 41M| 510M| 539K (1)| 00:00:22 | | |
-----------------------------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$58A6D7F6
4 - SEL$58A6D7F6 / p@SEL$1
7 - SEL$58A6D7F6 / C@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."CODE"="C"."CODE")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "C"."SOME_COL"[NUMBER,22], "C"."SOME_COL"[NUMBER,22]
2 - "P"."CODE"[CHARACTER,2]
3 - "P"."CODE"[CHARACTER,2]
4 - "P"."CODE"[CHARACTER,2]
5 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
6 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
7 - "C"."CODE"[CHARACTER,2], "C"."SOME_COL"[NUMBER,22]
Hint Report (identified by operation id / query block name / object alias):
Total hints for statement: 1
---------------------------------------------------------------------------
4 - SEL$58A6D7F6 / p@SEL$1
- no_index(p)
Note
-----
- this is an adaptive plan
解决方案
具有数千个子分区但少于一百万行的表可能有大量的空段空间,这将导致奇怪的优化器决策。运行以下查询以查看表和索引使用了多少空间:
select segment_name, sum(bytes)/1024/1024 mb, count(*) segment_count
from dba_segments
where segment_name in ('P', 'C', 'IX_P_CAT')
group by segment_name;
我试图在我的 19c 数据库上重新创建您的表,并且表“P”的每个分区消耗了 2.5 GB 的空间,尽管实际数据应该只需要几兆字节。每个系统的确切值都会有所不同,但我猜大多数系统都会有很大的值。Oracle 段通常是用于容纳超过一千行的重型数据结构;如果 Oracle 一次分配一个字节,性能会很差,因此它通常一次分配兆字节。但是如果你有 316 个子分区,那么这些兆字节加起来。
通常,选择大部分数据的最佳方式是使用全表扫描或全(子)分区扫描。但是,如果表有这么多浪费的空间,那么使用小索引并按 ROWID 查找每一行比完全扫描所有大部分为空的段更有效。
您可以通过使用更少的子分区、调整段分配设置或缩小表来解决问题,如下所示:
alter table p enable row movement;
alter table p shrink space;
begin
dbms_stats.gather_table_stats(user, 'P');
end;
/
推荐阅读
- node.js - Node.js - 仅显示 JSON 响应中的最后一个元素
- c - stdout 和 stderr 纠缠不清?
- soap - 在 WSO2 ESB 中调用代理服务时找不到操作的端点引用 (EPR)
- excel - 在excel单元格中定义数组并在excel数组公式中使用该单元格
- python - 将下一行附加到上一行
- nginx - 入口控制器不处理流量
- python - 数据框列的正则表达式
- java - 如何将 offsetTime 添加到 UTC 时间以获得正确的 ZonedDateTime?
- java - 如何在 build.bnd 中定义可选分辨率的通用导入包
- monitoring - Prometheus EKS 集群监控