oracle: with the partitioned table, how can I use suggestions to group each partition separately?

The database does this by default, no suggestions / adjustments are needed.

create data from the table (partitioned_key_index number, some_dummy_measure number)
partition by list (partitioned_key_index)
(
partition p1 values ​​(1),
partition p2 values ​​(2),
partition p3 values ​​(3),
partition p4 values ​​(4),
partition p5 values ​​(5)
);

insert in data with g as (select * of dual connection per level <= 1000)
select mod (rownum, 5) + 1, rownum of g, g where rownum <= 500000;
commit;

Then, execute the query:

alter the session set statistics_level = all;

select partitioned_key_index, sum (some_dummy_measure) of the data
group by partitioned_key_index;


PARTITIONED_KEY_INDEX SUM (SOME_DUMMY_MEASURE)
--------------------- -----------------------
1 25000250000
2 24999850000
3 24999950000
4 25000050000
5 25000150000

Check what happened:

SQL> select * from table (dbms_xplan.display_cursor (format => & # 39; allstats last & # 39;));

PLAN_TABLE_OUTPUT
-------------------------------------------------- -------------------------------------------------- -------------
SQL_ID 537qapda9hdy4, child number 0
-------------------------------------
select partitioned_key_index, sum (some_dummy_measure) of the data group
by partitioned_key_index

Plan hash value: 3405952922

-------------------------------------------------- -------------------------------------------------- -------------
| Id | Operation | Name | Start | E-Rows | A-Rows | A-Time | Tampons | OMem | 1Mem | Used-Mem |
-------------------------------------------------- -------------------------------------------------- -------------
| 0 | SELECT DECLARATION | | 1 | | 5 | 00: 00: 00.14 | 1065 | | | |
| 1 | LIST OF PARTITIONS ALL | | 1 | 585K | 5 | 00: 00: 00.14 | 1065 | | | |
| 2 | HASH GROUP BY | | 5 | 585K | 5 | 00: 00: 00.14 | 1065 | 34M | 6473K | 738K (0) |
| 3 | FULL ACCESS TABLE | DATA | 5 | 585K | 500K | 00: 00: 00.06 | 1065 | | | |
-------------------------------------------------- -------------------------------------------------- -------------

Note
-----
- Dynamic sampling used for this statement (level = 2)

Operations below ALL PARTITIONS LIST They were made for all partitions. We have 5 partitions, including the GROUP BY. As you can see in the Starts column, HASH GROUP BY It was really done 5 times.

This is how it looks, when GROUP BY It is done for the whole table at the same time:

select (partitioned_key_index + 0), sum (some_dummy_measure) of the data
group by (partitioned_key_index + 0);

(PARTITIONED_KEY_INDEX + 0) SUM (SOME_DUMMY_MEASURE)
------------------------- -----------------------
1 25000250000
2 24999850000
5 25000150000
4 25000050000
3 24999950000

SQL> select * from table (dbms_xplan.display_cursor (format => & # 39; allstats last & # 39;));

PLAN_TABLE_OUTPUT
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------
SQL_ID 2xxf42mtp53sc, child number 0
-------------------------------------
select (partitioned_key_index + 0), sum (some_dummy_measure) of the data
group by (partitioned_key_index + 0)

Plan hash value: 3651737839

-------------------------------------------------- -------------------------------------------------- -------------
| Id | Operation | Name | Start | E-Rows | A-Rows | A-Time | Tampons | OMem | 1Mem | Used-Mem |
-------------------------------------------------- -------------------------------------------------- -------------
| 0 | SELECT DECLARATION | | 1 | | 5 | 00: 00: 00.24 | 1065 | | | |
| 1 | HASH GROUP BY | | 1 | 585K | 5 | 00: 00: 00.24 | 1065 | 34M | 6473K | 4574K (0) |
| 2 | LIST OF PARTITIONS ALL | | 1 | 585K | 500K | 00: 00: 00.14 | 1065 | | | |
| 3 | FULL ACCESS TABLE | DATA | 5 | 585K | 500K | 00: 00: 00.06 | 1065 | | | |
-------------------------------------------------- -------------------------------------------------- -------------

Note
-----
- Dynamic sampling used for this statement (level = 2)


20 selected rows

HASH GROUP BY it was done only once, for the entire amount of data, after it was collected from all the partitions.