How to workaround the internal limitation of a single work area

When using automatic PGA management mode,as you may know there is limitation on the size of the assigned memory indicated by :

• _pga_max_size Maximum size of the PGA memory for one process
• _smm_max_size maximum work area size per porcess
• _smm_px_max_size maximum work area size per parallel query

The calculation of this parameters depend on the value set for “pga_aggregate_target”. Also the memory bound is adjusted based on the workload.For more info on the algorithm please check this white paper .

Here is a quick view :

Not-Null impact on performance : Another special case (Null-Aware Anti-Join “HASH JOIN ANTI NA”)

I know that there is many blogs explaining why we should explicitly declare a column “NOT NULL” if it will not hold null data (Like the impact on INDEX usage or join method),but here i will only focus on the join method implemented in 11G that’s Null-Aware Anti-Join and specifically on some ‘limitation’ of the “HASH JOIN ANTI NA” that i have observed.

For more information on “Null-Aware Anti-Join” and it’s algorithm take a look at : http://www.vldb.org/pvldb/2/vldb09-423.pdf

Here is a quick test case :

• Oracle 11.2.0.3.9
• OS : HP-UX V3