Cost Based Transformations (Oracle Database 10g to 11g Change)
from http://www.dbaref.com/home/oracle-11g-new-features/costbasedtransformationsoracledatabase10gto11gchange
In Oracle Database 11g three new enhancements have been made to the cost based transformations. The three enhancements are turned on by default and are controlled by the optimizer_features_enabled initialization parameter.
* Null aware anti join
This Feature optimizes queries which have a where clause predicate involving a NOT IN or ALL operator on a column which is NULLABLE. This feature is to improve the performance of this type of query on large dataset. Take for example the following query involving two tables T1 and T2,
SELECT ....
FROM T1
WHERE T1.x NOT IN (select T2.y where ....);
The T2.y column is a NULLABLE column, so in Oracle Database 10g we cannot unnest the subquery thus the only possible execution plan is a cartesian product. In Oracle Database 11g we can use the new null aware anit-join transformation, thus enabling us to unnest the subquery, which means we can use a Nested Loop join, a Hash Join or a Sort Merge Join.
The usage of the null aware anti join feature can be identified in the explain plan by the follow type of operators.
HASH JOIN RIGHT ANTI NA| <--NA means Null Aware
ANTI SNA | <- SNA means Single Null-Aware Anti-Join
Example
select count(*)
from emp
where mgr not in (select mgr from emp);
11.1.0.7.0 Execution Plan
Execution Plan
----------------------------------------------------------
Plan hash value: 54517352
----------------------------------------------------------------------
| Id| Operation | Name| Rows| Bytes| Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 7 (15)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|*2 | HASH JOIN ANTI NA | | 12 | 96 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
10.2.0.4.0 Execution Plan
Execution Plan
----------------------------------------------------------
Plan hash value: 492197985
----------------------------------------------------------------------
| Id| Operation | Name| Rows| Bytes| Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 21 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|*2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
|*4 | TABLE ACCESS FULL| EMP | 2 | 8 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
* Join Predicate Pushdown
Join predicate pushdown is now available for queries with group-by, distinct, semi/anti-joined view.
Oracle Database 10g introduced the cost based transformation called predicate push down. Predicate push down enables queries that have a join predicate between a table and a view to use a index based nested loop join by pushing the join predicate inside the view. Imagine you have a query, which contains a view V, and a table T. The query also has a join predicate of T.x = V.y. Prior to Oracle Database 10g we could only use two join methods, a hash join or a sort merge join. However, with the introduction of predicate push down we can push the join predicate into the view so the join becomes T.x = T2.y (where T2 is the table inside view V which has the column y in it) then if there is an index present we can do an nested loops join. In Oracle Database 11g we have extended the predicate push down capabilities to include queries, which contain group by, distinct, anti-join and semi-joins. The usage of the new predicate push down feature can be identified in the explain plan by the following operator
VIEW PUSHED PREDICATE
Example
select p.prod_name, p.prod_desc, v.qu
from products p,
(select s.prod_id, sum(quantity_sold) qu
from sales s
group by prod_id) v
where v.prod_id = p.prod_id
and p.supplier_id = 12;
11.1.0.7.0 Execution Plan
-------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 420 (0)| 00:00:06
| 1 | NESTED LOOPS | | 1 | 78 | 420 (0)| 00:00:06
|*2 | TABLE ACCESS FULL | PRODUCTS | 1 | 65 | 3 (0)| 00:00:01
| 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 417 (0)| 00:00:06
|*4 | FILTER | | | | | |
| 5 | SORT AGGREGATE | | 1 | 7 | | |
| 6 | PARTITION RANGE ALL | | 12762 | 89334 | 417 (0)| 00:00:06
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 89334 | 417 (0)| 00:00:06
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | |
|*9 | BITMAP INDEX SINGLE VALUE | SALES_P_BIX | | | | |
-------------------------------------------------------------------------------------------------
10.2.0.4 Execution Plan
------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 4824 | 424 (1)| 00:00:06 |
| 1 | HASH GROUP BY | | 36 | 4824 | 424 (1)| 00:00:06 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 12762 | 89334 | 422 (0)| 00:00:06 |
| 3 | NESTED LOOPS | | 12762 | 1670K| 422 (0)| 00:00:06 |
|*4 | TABLE ACCESS FULL | PRODUCTS | 1 | 127 | 3 (0)| 00:00:01 |
| 5 | PARTITION RANGE ALL | | | | | |
| 6 | BITMAP CONVERSION TO ROWIDS | | | | | |
|*7 | BITMAP INDEX SINGLE VALUE | SALES_P_BIX | | | | |
------------------------------------------------------------------------------------------------
Multi level Push predicate
Join predicate push down has also been enhanced to work on a view inside of the view.
* Group By Placement
This new transformation allows the optimizer to rewrite queries in order to minimize the number of rows necessary for a join. The rewrite works by placing the group by operator earlier in the execution plan so less rows are necessary for a join operation. For example if you had the following query
SELECT sum(T1.x), T2.y
FROM T1,T2
WHERE T1.x = T2.z
GROUP BY T2.y;
The optimizer transforms the query into the following:
SELECT sum(V.sumv), T2.y
FROM T2, (select sum(T1.x) as sumv from T1 group by T2.z)V
WHERE V.z=T2.z;
Here the group by happens inside the view thus reducing the number of rows coming out of the view into the join with T2.
For example:
select prod_name, prod_desc, sum(quantity_sold)
from products p, sales s
where p.prod_id = s.prod_id
group by prod_name, prod_desc;
11.1.0.7.0 Execution Plan
---------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72 | 5400 | 557 (14)| 00:00:07 |
| 1 | HASH GROUP BY | | 72 | 5400 | 557 (14)| 00:00:07 |
|*2 | HASH JOIN | | 72 | 5400 | 556 (13)| 00:00:07 |
| 3 | VIEW | VW_GBC_5 | 72 | 1224 | 552 (13)| 00:00:07 |
| 4 | HASH GROUP BY | | 72 | 792 | 552 (13)| 00:00:07 |
| 5 | PARTITION RANGE ALL| | 918K| 9870K| 497 (4)| 00:00:06 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 9870K| 497 (4)| 00:00:06 |
| 7 | TABLE ACCESS FULL | PRODUCTS | 72 | 4176 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
The Oracle Database 11g plan shows a view was created (ID 3) and a new group by was added inside the view (ID4).
10.2.0.4 Execution Plan
--------------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3565 | 226K| | 5603 (2)| 00:01:08 |
| 1 | HASH GROUP BY | | 3565 | 226K| 67M| 5603 (2)| 00:01:08 |
|*2 | HASH JOIN | | 918K| 56M| | 507 (5)| 00:00:07 |
| 3 | TABLE ACCESS FULL |PRODUCTS| 72 | 4176 | | 3 (0)| 00:00:01 |
| 4 | PARTITION RANGE ALL| | 918K| 6281K| | 497 (4)| 00:00:06 |
| 5 | TABLE ACCESS FULL | SALES | 918K| 6281K| | 497 (4)| 00:00:06 |
--------------------------------------------------------------------------------------
