Enhanced Subquery Optimizations in Oracle

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 coalescinghere 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s