While googling I found a fresh article from Oracle for the VLDB journal: Enhanced Subquery Optimization in Oracle. It primarily discusses subqueries – how Oracle deals with them on optimization and at run-time.
Paper’s topics overview:
- subquery coalescing – here I’ve done some testing of the feature partially available in 11gR2
- (parallel) group-by pushdown – a way to reduce amount of work for GROUP BY queries by skipping processing of non-candidate groups in serial mode and by pushing GROUP BY work down to the producers in the PX. AFAIU this is about step #7 in the following plan:
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50 | 5350 | 2529 (2)| 00:00:31 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 50 | 5350 | 2529 (2)| 00:00:31 | Q1,01 | P->S | QC (RAND) | |* 3 | FILTER | | | | | | Q1,01 | PCWC | | | 4 | HASH GROUP BY | | 50 | 5350 | 2529 (2)| 00:00:31 | Q1,01 | PCWP | | | 5 | PX RECEIVE | | 50 | 5350 | 2529 (2)| 00:00:31 | Q1,01 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | 50 | 5350 | 2529 (2)| 00:00:31 | Q1,00 | P->P | HASH | | 7 | HASH GROUP BY | | 50 | 5350 | 2529 (2)| 00:00:31 | Q1,00 | PCWP | | | 8 | PX BLOCK ITERATOR | | 1000K| 102M| 2500 (1)| 00:00:30 | Q1,00 | PCWC | | | 9 | TABLE ACCESS FULL| T | 1000K| 102M| 2500 (1)| 00:00:30 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(SUM(SYS_OP_CSR(SYS_OP_MSR(SUM("Y"),MAX("PAD")),0))>510000)The inconsistency with the paper is this step does not have FILTER predicate based on the HAVING clause – so, maybe I’m wrong (or Oracle does not explicitly mark this operation, or this is not implemented yet, or my quick test is too simple, or …)
- group-by view elimination – something very close to the join elimination, but involving inline views with GROUP BY
- subquery removal using window functions – different ways to replace so-called “subsumed” aggregate subqueries (correlated and not correlated) with window functions
- scalable grand total calculation with PX – the step #3 in the following plan (i.e. pushing WINDOW function calculation to the slaves instead of doing it by QC):
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000K| 2929K| 2499 (1)| 00:00:30 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 2929K| 2499 (1)| 00:00:30 | Q1,00 | P->S | QC (RAND) | | 3 | WINDOW BUFFER | | 1000K| 2929K| 2499 (1)| 00:00:30 | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 1000K| 2929K| 2499 (1)| 00:00:30 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| T | 1000K| 2929K| 2499 (1)| 00:00:30 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------
- null-aware anti-joins – this is covered in details in Greg Rahn’s blog entry
Timur Akhmadeev's blog 