Oracle Live SQL - Script: Outer Joins with filters

Statement 1

CREATE TABLE "PAR"  
   (	"ID" NUMBER,  
	"P_NAME" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP",  
	 CONSTRAINT "PAR_PK" PRIMARY KEY ("ID") 
	 )

Table created.

Statement 2

REM INSERTING into BERX.PAR

INSERTING into BERX.PAR

Statement 3

SET DEFINE OFF

Unsupported Command

Statement 4

Insert into PAR (ID,P_NAME) values ('1','P_one')

1 row(s) inserted.

Statement 5

Insert into PAR (ID,P_NAME) values ('2','P_two')

1 row(s) inserted.

Statement 6

Insert into PAR (ID,P_NAME) values ('3','P_three')

1 row(s) inserted.

Statement 7

Insert into PAR (ID,P_NAME) values ('4','P_four')

1 row(s) inserted.

Statement 8

CREATE TABLE "CLD"  
   (	"ID" NUMBER NOT NULL ENABLE,  
	"P_ID" NUMBER NOT NULL ENABLE,  
	"C_NAME" VARCHAR2(30 BYTE) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,  
	"AGE_RANGE" NUMBER(2,0) NOT NULL ENABLE,  
	 CONSTRAINT "CLD_PK" PRIMARY KEY ("ID") , 
     CONSTRAINT "CLD_PAR_FK" FOREIGN KEY ("P_ID") 
	  REFERENCES "PAR" ("ID") ENABLE	  
)

Table created.

Statement 9

REM INSERTING into BERX.CLD

INSERTING into BERX.CLD

Statement 10

SET DEFINE OFF

Unsupported Command

Statement 11

Insert into CLD (ID,P_ID,C_NAME,AGE_RANGE) values ('1','1','1_10','10')

1 row(s) inserted.

Statement 12

Insert into CLD (ID,P_ID,C_NAME,AGE_RANGE) values ('2','2','2_10','10')

1 row(s) inserted.

Statement 13

Insert into CLD (ID,P_ID,C_NAME,AGE_RANGE) values ('3','3','3_10','10')

1 row(s) inserted.

Statement 14

Insert into CLD (ID,P_ID,C_NAME,AGE_RANGE) values ('4','4','4_20','20')

1 row(s) inserted.

Statement 15

commit

Statement processed.

Statement 16

select /* ANSI 1 */ pr.*, cd.* 
from par pr left outer join cld cd on (pr.id = cd.p_id and age_range > 10)


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20
1 P_one  -  -  -  - 
2 P_two  -  -  -  - 
3 P_three  -  -  -  - 

4 rows selected.

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20
1	P_one	-	-	-	-
2	P_two	-	-	-	-
3	P_three	-	-	-	-

Statement 17

explain plan for select /* ANSI 1 */ pr.*, cd.* 
from par pr left outer join cld cd on (pr.id = cd.p_id and age_range > 10)

Statement processed.

Statement 18

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2247011116
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   344 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID"(+))
   3 - filter("AGE_RANGE"(+)>10)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

20 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2247011116

---------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL\| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="CD"."P_ID"(+))
3 - filter("AGE_RANGE"(+)>10)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 19

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '%ANSI 1%' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')


PLAN_TABLE_OUTPUT
SQL_ID  aqxmjg6g40dx9, child number 0
-------------------------------------
select /* ANSI 1 */ pr.*, cd.*  from par pr left outer join cld cd on 
(pr.id = cd.p_id and age_range > 10)
 
Plan hash value: 2247011116
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / PR@SEL$1
   3 - SEL$2BFA4EE4 / CD@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID")
   3 - filter("AGE_RANGE">10)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1; rowset=256) "PR"."ID"[NUMBER,22], 
       "CD"."P_ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30], 
       "CD"."ID"[NUMBER,22], "AGE_RANGE"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30]
   2 - (rowset=256) "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   3 - (rowset=256) "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], 
       "CD"."C_NAME"[VARCHAR2,30], "AGE_RANGE"[NUMBER,22]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

43 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID aqxmjg6g40dx9, child number 0
-------------------------------------
select /* ANSI 1 / pr., cd.* from par pr left outer join cld cd on
(pr.id = cd.p_id and age_range > 10)

Plan hash value: 2247011116

---------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 6 (100)\| \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL\| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$2BFA4EE4
2 - SEL$2BFA4EE4 / PR@SEL$1
3 - SEL$2BFA4EE4 / CD@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="CD"."P_ID")
3 - filter("AGE_RANGE">10)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1; rowset=256) "PR"."ID"[NUMBER,22],
"CD"."P_ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30],
"CD"."ID"[NUMBER,22], "AGE_RANGE"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30]
2 - (rowset=256) "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
3 - (rowset=256) "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22],
"CD"."C_NAME"[VARCHAR2,30], "AGE_RANGE"[NUMBER,22]

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 20

select /* ANSI 2 */  pr.*, cd.* 
from par pr left outer join cld cd on pr.id = cd.p_id  
where age_range > 10


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20

Statement 21

explain plan for select /* ANSI 2 */  pr.*, cd.* 
from par pr left outer join cld cd on pr.id = cd.p_id  
where age_range > 10

Statement processed.

Statement 22

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3984166157
 
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    86 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |     1 |    86 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |        |     1 |    86 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | CLD    |     1 |    56 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PAR_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| PAR    |     1 |    30 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("CD"."AGE_RANGE">10)
   4 - access("PR"."ID"="CD"."P_ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

23 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3984166157

---------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 1 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 2 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL \| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\|* 4 \| INDEX UNIQUE SCAN \| PAR_PK \| 1 \| \| 0 (0)\| 00:00:01 \|
\| 5 \| TABLE ACCESS BY INDEX ROWID\| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("CD"."AGE_RANGE">10)
4 - access("PR"."ID"="CD"."P_ID")

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan

Statement 23

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '%ANSI 2%' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')


PLAN_TABLE_OUTPUT
SQL_ID  2uwb162chv73h, child number 0
-------------------------------------
select /* ANSI 2 */  pr.*, cd.*  from par pr left outer join cld cd on 
pr.id = cd.p_id   where age_range > 10
 
Plan hash value: 3984166157
 
------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |        |       |       |     4 (100)|          |
|- *  1 |  HASH JOIN                    |        |     1 |    86 |     4   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                |        |     1 |    86 |     4   (0)| 00:00:01 |
|     3 |    NESTED LOOPS               |        |     1 |    86 |     4   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR      |        |       |       |            |          |
|  *  5 |      TABLE ACCESS FULL        | CLD    |     1 |    56 |     3   (0)| 00:00:01 |
|  *  6 |     INDEX UNIQUE SCAN         | PAR_PK |     1 |       |     0   (0)|          |
|     7 |    TABLE ACCESS BY INDEX ROWID| PAR    |     1 |    30 |     1   (0)| 00:00:01 |
|-    8 |   TABLE ACCESS FULL           | PAR    |     1 |    30 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$4F080662
   2 - SEL$4F080662
   5 - SEL$4F080662 / CD@SEL$1
   6 - SEL$4F080662 / PR@SEL$1
   7 - SEL$4F080662 / PR@SEL$1
   8 - SEL$4F080662 / PR@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID")
   5 - filter("CD"."AGE_RANGE">10)
   6 - access("PR"."ID"="CD"."P_ID")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "CD"."P_ID"[NUMBER,22], "PR"."ID"[NUMBER,22], 
       "CD"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   2 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   3 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR".ROWID[ROWID,10], "PR"."ID"[NUMBER,22]
   4 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22]

Rows 1 - 50. More rows exist.

PLAN_TABLE_OUTPUT
SQL_ID 2uwb162chv73h, child number 0
-------------------------------------
select /* ANSI 2 / pr., cd.* from par pr left outer join cld cd on
pr.id = cd.p_id where age_range > 10

Plan hash value: 3984166157

------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 4 (100)\| \|
\|- * 1 \| HASH JOIN \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 2 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 3 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\|- 4 \| STATISTICS COLLECTOR \| \| \| \| \| \|
\| * 5 \| TABLE ACCESS FULL \| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\| * 6 \| INDEX UNIQUE SCAN \| PAR_PK \| 1 \| \| 0 (0)\| \|
\| 7 \| TABLE ACCESS BY INDEX ROWID\| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
\|- 8 \| TABLE ACCESS FULL \| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$4F080662
2 - SEL$4F080662
5 - SEL$4F080662 / CD@SEL$1
6 - SEL$4F080662 / PR@SEL$1
7 - SEL$4F080662 / PR@SEL$1
8 - SEL$4F080662 / PR@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="CD"."P_ID")
5 - filter("CD"."AGE_RANGE">10)
6 - access("PR"."ID"="CD"."P_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "CD"."P_ID"[NUMBER,22], "PR"."ID"[NUMBER,22],
"CD"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
2 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
3 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR".ROWID[ROWID,10], "PR"."ID"[NUMBER,22]
4 - "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22]

Statement 24

select /*+ ANSI 3 */  pr.*, cd.* 
from par pr left outer join  
     (select /*+ NO_MERGE */ * from cld where age_range > 10 ) cd  
                                   on pr.id = cd.p_id


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20
1 P_one  -  -  -  - 
2 P_two  -  -  -  - 
3 P_three  -  -  -  - 

4 rows selected.

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20
1	P_one	-	-	-	-
2	P_two	-	-	-	-
3	P_three	-	-	-	-

Statement 25

explain plan for select /*+ ANSI 3 */  pr.*, cd.* 
from par pr left outer join  
     (select /*+ NO_MERGE */ * from cld where age_range > 10 ) cd  
                                   on pr.id = cd.p_id

Statement processed.

Statement 26

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 686165088
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |   344 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |      |     1 |    56 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID"(+))
   4 - filter("AGE_RANGE">10)
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
 
   0 -  SEL$3
         E -  ANSI
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

28 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 686165088

----------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
----------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL \| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\| 3 \| VIEW \| \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\|* 4 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="CD"."P_ID"(+))
4 - filter("AGE_RANGE">10)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------

0 - SEL$3
E - ANSI

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 27

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '%ANSI 3%' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')

ORA-01427: single-row subquery returns more than one row

More Details: https://docs.oracle.com/error-help/db/ora-01427

Statement 28

select /* Oracle 2 */  pr.*, cd.* 
from par pr , cld cd 
where pr.id = p_id (+) 
  and cd.age_range > 10


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20

Statement 29

explain plan for select /* Oracle 2 */  pr.*, cd.* 
from par pr , cld cd 
where pr.id = p_id (+) 
  and cd.age_range > 10

Statement processed.

Statement 30

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3984166157
 
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    86 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |     1 |    86 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |        |     1 |    86 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | CLD    |     1 |    56 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PAR_PK |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| PAR    |     1 |    30 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("CD"."AGE_RANGE">10)
   4 - access("PR"."ID"="P_ID")
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

23 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3984166157

---------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 1 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 2 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL \| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\|* 4 \| INDEX UNIQUE SCAN \| PAR_PK \| 1 \| \| 0 (0)\| 00:00:01 \|
\| 5 \| TABLE ACCESS BY INDEX ROWID\| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("CD"."AGE_RANGE">10)
4 - access("PR"."ID"="P_ID")

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan

Statement 31

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '% Oracle 2 %' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')


PLAN_TABLE_OUTPUT
SQL_ID  av50a1q4wrytc, child number 0
-------------------------------------
select /* Oracle 2 */  pr.*, cd.*  from par pr , cld cd  where pr.id = 
p_id (+)    and cd.age_range > 10
 
Plan hash value: 3984166157
 
------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |        |       |       |     4 (100)|          |
|- *  1 |  HASH JOIN                    |        |     1 |    86 |     4   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                |        |     1 |    86 |     4   (0)| 00:00:01 |
|     3 |    NESTED LOOPS               |        |     1 |    86 |     4   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR      |        |       |       |            |          |
|  *  5 |      TABLE ACCESS FULL        | CLD    |     1 |    56 |     3   (0)| 00:00:01 |
|  *  6 |     INDEX UNIQUE SCAN         | PAR_PK |     1 |       |     0   (0)|          |
|     7 |    TABLE ACCESS BY INDEX ROWID| PAR    |     1 |    30 |     1   (0)| 00:00:01 |
|-    8 |   TABLE ACCESS FULL           | PAR    |     1 |    30 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$26D15252
   2 - SEL$26D15252
   5 - SEL$26D15252 / CD@SEL$1
   6 - SEL$26D15252 / PR@SEL$1
   7 - SEL$26D15252 / PR@SEL$1
   8 - SEL$26D15252 / PR@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="P_ID")
   5 - filter("CD"."AGE_RANGE">10)
   6 - access("PR"."ID"="P_ID")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "P_ID"[NUMBER,22], "PR"."ID"[NUMBER,22], "CD"."ID"[NUMBER,22], 
       "PR"."P_NAME"[VARCHAR2,30], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   2 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   3 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22], "PR".ROWID[ROWID,10], "PR"."ID"[NUMBER,22]
   4 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30], 
       "CD"."AGE_RANGE"[NUMBER,22]

Rows 1 - 50. More rows exist.

PLAN_TABLE_OUTPUT
SQL_ID av50a1q4wrytc, child number 0
-------------------------------------
select /* Oracle 2 / pr., cd.* from par pr , cld cd where pr.id =
p_id (+) and cd.age_range > 10

Plan hash value: 3984166157

------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 4 (100)\| \|
\|- * 1 \| HASH JOIN \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 2 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\| 3 \| NESTED LOOPS \| \| 1 \| 86 \| 4 (0)\| 00:00:01 \|
\|- 4 \| STATISTICS COLLECTOR \| \| \| \| \| \|
\| * 5 \| TABLE ACCESS FULL \| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\| * 6 \| INDEX UNIQUE SCAN \| PAR_PK \| 1 \| \| 0 (0)\| \|
\| 7 \| TABLE ACCESS BY INDEX ROWID\| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
\|- 8 \| TABLE ACCESS FULL \| PAR \| 1 \| 30 \| 1 (0)\| 00:00:01 \|
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$26D15252
2 - SEL$26D15252
5 - SEL$26D15252 / CD@SEL$1
6 - SEL$26D15252 / PR@SEL$1
7 - SEL$26D15252 / PR@SEL$1
8 - SEL$26D15252 / PR@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="P_ID")
5 - filter("CD"."AGE_RANGE">10)
6 - access("PR"."ID"="P_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) "P_ID"[NUMBER,22], "PR"."ID"[NUMBER,22], "CD"."ID"[NUMBER,22],
"PR"."P_NAME"[VARCHAR2,30], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
2 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
3 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22], "PR".ROWID[ROWID,10], "PR"."ID"[NUMBER,22]
4 - "CD"."ID"[NUMBER,22], "P_ID"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30],
"CD"."AGE_RANGE"[NUMBER,22]

Statement 32

select /* Oracle 1 */  pr.*, cd.* 
from par pr , cld cd 
where pr.id = p_id (+) 
  and cd.age_range (+)> 10


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20
1 P_one  -  -  -  - 
2 P_two  -  -  -  - 
3 P_three  -  -  -  - 

4 rows selected.

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20
1	P_one	-	-	-	-
2	P_two	-	-	-	-
3	P_three	-	-	-	-

Statement 33

explain plan for select /* Oracle 1 */  pr.*, cd.* 
from par pr , cld cd 
where pr.id = p_id (+) 
  and cd.age_range (+)> 10

Statement processed.

Statement 34

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2247011116
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |   344 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="P_ID"(+))
   3 - filter("CD"."AGE_RANGE"(+)>10)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

20 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2247011116

---------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL\| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="P_ID"(+))
3 - filter("CD"."AGE_RANGE"(+)>10)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 35

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '%ANSI 1%' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')


PLAN_TABLE_OUTPUT
SQL_ID  aqxmjg6g40dx9, child number 0
-------------------------------------
select /* ANSI 1 */ pr.*, cd.*  from par pr left outer join cld cd on 
(pr.id = cd.p_id and age_range > 10)
 
Plan hash value: 2247011116
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / PR@SEL$1
   3 - SEL$2BFA4EE4 / CD@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID")
   3 - filter("AGE_RANGE">10)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1; rowset=256) "PR"."ID"[NUMBER,22], 
       "CD"."P_ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30], 
       "CD"."ID"[NUMBER,22], "AGE_RANGE"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30]
   2 - (rowset=256) "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   3 - (rowset=256) "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], 
       "CD"."C_NAME"[VARCHAR2,30], "AGE_RANGE"[NUMBER,22]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

43 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID aqxmjg6g40dx9, child number 0
-------------------------------------
select /* ANSI 1 / pr., cd.* from par pr left outer join cld cd on
(pr.id = cd.p_id and age_range > 10)

Plan hash value: 2247011116

---------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \| \| 6 (100)\| \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL\| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\|* 3 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$2BFA4EE4
2 - SEL$2BFA4EE4 / PR@SEL$1
3 - SEL$2BFA4EE4 / CD@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="CD"."P_ID")
3 - filter("AGE_RANGE">10)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1; rowset=256) "PR"."ID"[NUMBER,22],
"CD"."P_ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30],
"CD"."ID"[NUMBER,22], "AGE_RANGE"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30]
2 - (rowset=256) "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
3 - (rowset=256) "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22],
"CD"."C_NAME"[VARCHAR2,30], "AGE_RANGE"[NUMBER,22]

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 36

select /* Oracle 3 */  pr.*, cd.* 
from par pr ,  
     ( select  /*+ NO_MERGE */ * from cld where age_range > 10 ) cd 
where pr.id = p_id (+)


ID P_NAME ID P_ID C_NAME AGE_RANGE
4 P_four 4 4 4_20 20
1 P_one  -  -  -  - 
2 P_two  -  -  -  - 
3 P_three  -  -  -  - 

4 rows selected.

ID	P_NAME	ID	P_ID	C_NAME	AGE_RANGE
4	P_four	4	4	4_20	20
1	P_one	-	-	-	-
2	P_two	-	-	-	-
3	P_three	-	-	-	-

Statement 37

explain plan for select /* Oracle 3 */  pr.*, cd.* 
from par pr ,  
     ( select  /*+ NO_MERGE */ * from cld where age_range > 10 ) cd 
where pr.id = p_id (+)

Statement processed.

Statement 38

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 686165088
 
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |   344 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER    |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|   3 |   VIEW              |      |     1 |    56 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="P_ID"(+))
   4 - filter("AGE_RANGE">10)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

21 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 686165088

----------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
----------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\|* 1 \| HASH JOIN OUTER \| \| 4 \| 344 \| 6 (0)\| 00:00:01 \|
\| 2 \| TABLE ACCESS FULL \| PAR \| 4 \| 120 \| 3 (0)\| 00:00:01 \|
\| 3 \| VIEW \| \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
\|* 4 \| TABLE ACCESS FULL\| CLD \| 1 \| 56 \| 3 (0)\| 00:00:01 \|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("PR"."ID"="P_ID"(+))
4 - filter("AGE_RANGE">10)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Statement 39

select * from dbms_xplan.display_cursor( sql_id => ( 
select /* xxx */ sql_id --, sql_text 
from v$sql  
where sql_text like '%ANSI 1%' and sql_text not like '% xxx %' and sql_text not like 'explain%'  
                                                    ) ,  
    format => 'ALL +adaptive')


PLAN_TABLE_OUTPUT
SQL_ID  aqxmjg6g40dx9, child number 0
-------------------------------------
select /* ANSI 1 */ pr.*, cd.*  from par pr left outer join cld cd on 
(pr.id = cd.p_id and age_range > 10)
 
Plan hash value: 2247011116
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     4 |   344 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| PAR  |     4 |   120 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CLD  |     1 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / PR@SEL$1
   3 - SEL$2BFA4EE4 / CD@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("PR"."ID"="CD"."P_ID")
   3 - filter("AGE_RANGE">10)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1; rowset=256) "PR"."ID"[NUMBER,22], 
       "CD"."P_ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30], 
       "CD"."ID"[NUMBER,22], "AGE_RANGE"[NUMBER,22], "CD"."C_NAME"[VARCHAR2,30]
   2 - (rowset=256) "PR"."ID"[NUMBER,22], "PR"."P_NAME"[VARCHAR2,30]
   3 - (rowset=256) "CD"."ID"[NUMBER,22], "CD"."P_ID"[NUMBER,22], 
       "CD"."C_NAME"[VARCHAR2,30], "AGE_RANGE"[NUMBER,22]
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 

43 rows selected.

Outer Joins with filters - when are they applied

Additional Information