Query transformation of the optimizer

1. View Merging

Sql> CREATE VIEW EMP_VM as 2 Select Empno,ename,job,sal,comm,deptno 3 from EMP 4 where deptno=30; View created.     Sql> select Empno 2 from EMP_VM 3 where empno > 150; EMPNO----------7499 7521 7654 7698 7844 79006 rows selected. Sql> select * FROM table (dbms_xplan.display_cursor (null, NULL, ' advanced ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------sql_id 997U1V1B1K5SC, child number 0---- ---------------------------------select empno from EMP_VM where empno > 150Plan hash value:169057108---------------- ----------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |--------------------------------------------------------------------------------------| 0 |        SELECT STATEMENT |       |       |     |          2 (100) | ||  * 1 | TABLE ACCESS by INDEX rowid|     EMP |    5 |     35 | 2 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |    Pk_emp |       14 |     | 1 (0) | 00:00:01 |--------------------------------------------------------------------------------------Query Block Name/ Object Alias (identified by Operation ID):-------------------------------------------------------------1-sel$ F5BB74E1/[email protected]$2 2-sel$f5bb74e1/[email protected]$2outline Data-------------/*+ BEGIN      _outline_data ignore_optim_embedded_hints optimizer_features_enable (' 11.2.0.4 ') db_version (' 11.2.0.4 ') All_rows outline_leaf (@ "sel$f5bb74e1") MERGE (@ "sel$2") OUTLINE (@ "sel$1") OUTLINE (@ "sel$2") INDEX _RS_ASC (@ "sel$f5bb74e1" "emp" @ "sel$2" ("EMP"). EMPNO ")) End_outline_data */predicate information (identified by Operation ID):---------------------------------------------------1-filter (" DEPTNO "=30) 2-access (" EMPNO ">150) Column Projection information (identified by Operation ID):---------------------- -------------------------------------1-"EMPNO" [number,22] 2-"EMP". ROWID[ROWID,10], "EMPNO" [number,22]49 rows selected.  Sql>

2. Predicate propulsion

The optimizer pushes the related predicate of the query block into the view query block.

A. Defining a view

Create View ALL_EMP_VW as (select Empno, ename, Job, comm, Deptno from EMP) union (select Empno, ename, Job, Comm, Deptno from Contract_workers);

B. Executing a query

Select Enamefrom all_emp_vwwhere empno = 50;

C. Predicate propulsion (SQL that the optimizer actually executes)

Select Enamefrom (select Empno, ename, Job, comm, deptno from EMP where Empno=50unionselect empno, ename, Job, Comm, Dept No from contract_workers where empno=50);

3. Non-nested subqueries: Converting subqueries to equivalent join connections

4. Using materialized views for query rewriting

Query transformation of the optimizer