Oracle Hash Join - Probe Table: Index over Partition?

Question

Both Table P (Parent) and C (Child) have 10 partitions on cat and 316 subpartitions on effective_date. Table P has the following index create index ix_p_cat on p (cat);.

How is it possible that an index range scan with an index on the partition column is preferable (lower cost) to the optimizer than doing a full partition access?

My thinking is that the same amount of data blocks from P are going to be needed in either case, so might as well avoid having to read additional index blocks. However, the optimizer disagrees.

Below are two explain plans. The first is showing that the optimizer wants to use the index to build the hash table, and the second is with a hint to not use the index.

Tables and index are analyzed.

Oracle Enterprise Edition 19c. Thanks in advance.

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

No Index

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

score 2 · Accepted Answer · answered Oct 28 '21 at 05:56

A table with thousands of subpartitions but less than a million rows may have an enormous amount of empty segment space that will cause weird optimizer decisions. Run the below query to see how much space is used by your tables and indexes:

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;

I tried to recreate your tables on my 19c database and each partition of table "P" consumed 2.5 gigabytes of space even though the actual data should only need a few megabytes. The exact value will differ for every system, but I'd guess that most systems will have a large value. Oracle segments are usually heavy data structures intended to hold more than a thousand rows; performance would suck if Oracle allocated one-byte-at-a-time, so instead it usually hands out megabytes-at-a-time. But if you have 316 subpartitions, those megabytes add up.

Normally, the best way to select a large percentage of data is to use a full table scan or a full (sub)partition scan. But if the table has so much wasted space, it's more efficient to use the small index and lookup each row by the ROWID than to full scan all the mostly-empty segments.

You can fix the problem by either using fewer subpartitions, adjusting your segment allocation settings, or shrinking the table like this:

alter table p enable row movement;
alter table p shrink space;
begin
    dbms_stats.gather_table_stats(user, 'P');
end;
/

Oracle Hash Join - Probe Table: Index over Partition?

1 Answers1