Oracle Hash Join - Probe Table: Index over Partition?

Question

表P(Parent)和C(Child)在cat上有10个分区，在effective_date上有316个子分区。表P的索引create index ix_p_cat on p (cat);

对于优化器来说，在分区列上使用索引进行索引范围扫描怎么会比进行完整分区访问更可取(成本更低)呢?

我的想法是，在任何一种情况下都需要来自p的相同数量的数据块，因此最好避免必须读取额外的索引块。然而，优化器不同意。

下面是两个解释方案。第一个显示了优化器想要使用索引来构建哈希表，第二个显示了不使用索引的提示。

分析表和索引

Oracle Enterprise Edition 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  

Answer 1

具有数千个子分区但少于100万行的表可能有大量的空段空间，这将导致奇怪的优化器决策。运行下面的查询，查看表和索引使用了多少空间:

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;
/