Discussion :

[Ikebukuro]

Salut tout le monde,

Je lance un SELECT avec deux bind variables déclarées sous SQL*Plus.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> set feedback on sql_id
SQL> VARIABLE DPT_ID01 NUMBER;
SQL> VARIABLE DPT_ID02 NUMBER;
SQL> EXEC :DPT_ID01 := 50;
SQL> EXEC :DPT_ID02 := 80;
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME; 
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales					  80 Alberto		  Errazuriz			    147
…
Shipping				  50 Winston		  Taylor			    180

79 rows selected.
SQL_ID: 43gugwnrgt1mf

La valeur des bind variables dans le plan d'exécution n'set pas bonne...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID	43gugwnrgt1mf, child number 0
-------------------------------------
select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID =
:DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME

Plan hash value: 2480766633

-----------------------------------------------------------------------------------------------------
| Id  | Operation			| Name		    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|		    |	    |	    |	  4 (100)|	    |
|   1 |  SORT ORDER BY			|		    |	 17 |	646 |	  4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS			|		    |	 17 |	646 |	  3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS 		|		    |	 17 |	646 |	  3   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR		|		    |	    |	    |		 |	    |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS	    |	  1 |	 16 |	  2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN 	| DEPT_ID_PK	    |	  1 |	    |	  1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN		| EMP_DEPARTMENT_IX |	  3 |	    |	  0   (0)|	    |
|   8 |    TABLE ACCESS BY INDEX ROWID	| EMPLOYEES	    |	 17 |	374 |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :DPT_ID01 (NUMBER): 80
   2 - :DPT_ID02 (NUMBER): (null)

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

   6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))

Note
-----
   - this is an adaptive plan


40 rows selected.

D’ailleurs si je relance le SELECT avec NULL pour le deuxième DEPARTMENT_ID, on voit bien que le résultat est différent 79 lignes d’un côté, 45 de l’autre… peut-être un bug d’affichage ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 50 OR D.DEPARTMENT_ID IS NULL) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME; 
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Shipping				  50 Adam		  Fripp 			    121
...
Shipping				  50 Winston		  Taylor			    180

45 rows selected.

Et si je mets 80 et NULL, comme affiché dans le plan d'exécution?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = 80 OR D.DEPARTMENT_ID IS NULL) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME; 
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales					  80 Alberto		  Errazuriz			    147
...
Sales					  80 William		  Smith 			    171

34 rows selected.

Ah non, c’est bien un problème de stockage de données.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SQL> select NAME, VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID = '43gugwnrgt1mf';

NAME		VALUE_STRING
--------------------------------
:DPT_ID01	80
:DPT_ID02	NULL

2 rows selected.

Bon, c'est un bug? Il faut renseigner un paramètre?
Merci pour vos retours.

[dell68]

Bonjour,
Si tu es en version 19c : est-ce que tu peux verifier si tu as le même comportement en exécutant ta requete avec le hint
/*+ optimizer_features_enable ( 11.2 ...) */???? pour être sûre que c'est un BUG ?????

[Ikebukuro]

Bingo :-)
Je te remercie beaucoup pour ton aide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
SQL> set feedback on sql_id
SQL> VARIABLE DPT_ID01 NUMBER;
SQL> VARIABLE DPT_ID02 NUMBER;
SQL> EXEC :DPT_ID01 := 50;
PL/SQL procedure successfully completed.
SQL_ID: gpg8a1fu1b3s1
 
SQL> EXEC :DPT_ID02 := 80;
PL/SQL procedure successfully completed.
SQL_ID: 9vbcgx7r32uv1
 
SQL> select /*+ optimizer_features_enable('11.2.0.1') */ D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME;
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales					  80 Alberto		  Errazuriz			    147
...
Shipping				  50 Winston		  Taylor			    180
 
79 rows selected.
 
SQL_ID: 29rn000mnkzgw
 
SQL> select NAME, VALUE_STRING from V$SQL_BIND_CAPTURE where SQL_ID = '29rn000mnkzgw';
NAME			VALUE_STRING
---------------------------------
:DPT_ID01		50
:DPT_ID02		80
2 rows selected.
 
SQL_ID: fcjhgqc1pf2dt 
 
 
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('29rn000mnkzgw', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	29rn000mnkzgw, child number 0
-------------------------------------
select /*+ optimizer_features_enable('11.2.0.1') */ D.DEPARTMENT_NAME,
E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from
employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
(D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by
D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
 
Plan hash value: 2480766633
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation			| Name		    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|		    |	    |	    |	  4 (100)|	    |
|   1 |  SORT ORDER BY			|		    |	 14 |	532 |	  4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS			|		    |	    |	    |		 |	    |
|   3 |    NESTED LOOPS 		|		    |	 14 |	532 |	  3   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR		|		    |	    |	    |		 |	    |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS	    |	  2 |	 32 |	  2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN 	| DEPT_ID_PK	    |	  2 |	    |	  1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN		| EMP_DEPARTMENT_IX |	  7 |	    |	  0   (0)|	    |
|   8 |    TABLE ACCESS BY INDEX ROWID	| EMPLOYEES	    |	  7 |	154 |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :DPT_ID01 (NUMBER): 50
   2 - :DPT_ID02 (NUMBER): 80
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))
 
 
37 rows selected.

[dell68]

Apparemment c'est un Bug sur la 19c !

[Mohamed.Houri]

Hello

Ce n'est probablement pas un bug mais juste une histoire de partage de plan. Peeked binds montre les valeurs des binds variables utilisées lors de l'optimisation du plan. Il se peut que votre execution n'a fait que partager le plan déjà en mémoire.

Pour être sûr il faut soit vider le cache et recommencer ou changer légèrement le sql_text pour produire une nouvelle optimisation à la lumière de ce que vous avez fait en utilisant le hint optimizer_features_enable sans le savoir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
var empid number
exec :empid := 7839
 
 
SQL> select * from emp where empno =:empid;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
 
 
SQL> select * from table (dbms_xplan.display_cursor(null, null, format => 'advanced'));
 
 
SQL_ID  8r7sgpm6d2p0j, child number 0
-------------------------------------
select * from emp where empno =:empid
 
Plan hash value: 2949544139
------------------------------------------------------
| Id  | Operation                   | Name   | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |
------------------------------------------------------
 
 
Peeked Binds (identified by position):
--------------------------------------
   1 - :EMPID (NUMBER): 7839
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=:EMPID)
 
 
 
SQL> exec :empid := 7698
 
PL/SQL procedure successfully completed.
 
SQL> select * from emp where empno =:empid;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 
SQL> select * from table (dbms_xplan.display_cursor(null, null, format => 'advanced'));
 
 
SQL_ID  8r7sgpm6d2p0j, child number 0  --> plan partagé
-------------------------------------
------------------------------------------------------
| Id  | Operation                   | Name   | Rows  |
------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |
------------------------------------------------------
 
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :EMPID (NUMBER): 7839 --> bind variable de la première execution
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=:EMPID)

Bien à vous
Mohamed Houri

[Ikebukuro]

Merci beaucoup pour ton retour, je reteste ça bientôt.

Je vide la Shared Pool.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SQL> alter system flush shared_pool;
System altered.

J'exécute le SELECT avec deux bind variables.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> set feedback on sql_id
SQL> VARIABLE DPT_ID01 NUMBER;
SQL> VARIABLE DPT_ID02 NUMBER;
 
SQL> EXEC :DPT_ID01 := 50;
PL/SQL procedure successfully completed.
 
SQL> EXEC :DPT_ID02 := 80;
PL/SQL procedure successfully completed.
 
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME; 
 
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Sales					  80 Alberto		  Errazuriz			    147
…
Shipping				  50 Winston		  Taylor			    180
 
79 rows selected.
 
SQL_ID: 43gugwnrgt1mf

Dans le plan d'exécution on a les bonnes valeurs des bind variables.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	43gugwnrgt1mf, child number 0
-------------------------------------
select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID =
:DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
 
Plan hash value: 2480766633
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation			| Name		    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|		    |	    |	    |	  4 (100)|	    |
|   1 |  SORT ORDER BY			|		    |	 78 |  2964 |	  4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS			|		    |	 78 |  2964 |	  3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS 		|		    |	 78 |  2964 |	  3   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR		|		    |	    |	    |		 |	    |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS	    |	  2 |	 32 |	  2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN 	| DEPT_ID_PK	    |	  2 |	    |	  1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN		| EMP_DEPARTMENT_IX |	  7 |	    |	  0   (0)|	    |
|   8 |    TABLE ACCESS BY INDEX ROWID	| EMPLOYEES	    |	 39 |	858 |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :DPT_ID01 (NUMBER): 50
   2 - :DPT_ID02 (NUMBER): 80
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))
 
Note
-----
   - this is an adaptive plan
 
 
40 rows selected.

Je change la valeur des bind variables.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> EXEC :DPT_ID01 := 40;
PL/SQL procedure successfully completed.
 
SQL> EXEC :DPT_ID02 := 60;
PL/SQL procedure successfully completed.
 
SQL> select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME, E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID = D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID = :DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME; 
DEPARTMENT_NAME 	       DEPARTMENT_ID FIRST_NAME 	  LAST_NAME		    EMPLOYEE_ID
------------------------------ ------------- -------------------- ------------------------- -----------
Human Resources 			  40 Susan		  Mavris			    203
IT					  60 Alexander		  Hunold			    103
IT					  60 Bruce		  Ernst 			    104
IT					  60 David		  Austin			    105
IT					  60 Diana		  Lorentz			    107
IT					  60 Valli		  Pataballa			    106
 
6 rows selected.

Dans le plan d'exécution, on a les valeurs de la première exécution, pas de la deuxième... tu avais raison Mohamed, à nouveau merci pour ton aide

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('43gugwnrgt1mf', NULL, FORMAT => 'TYPICAL +PEEKED_BINDS'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	43gugwnrgt1mf, child number 0
-------------------------------------
select D.DEPARTMENT_NAME, E.DEPARTMENT_ID, E.FIRST_NAME, E.LAST_NAME,
E.EMPLOYEE_ID from employees E, departments D where E.DEPARTMENT_ID =
D.DEPARTMENT_ID AND (D.DEPARTMENT_ID = :DPT_ID01 OR D.DEPARTMENT_ID =
:DPT_ID02) order by D.DEPARTMENT_NAME, E.FIRST_NAME, E.LAST_NAME
 
Plan hash value: 2480766633
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation			| Name		    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		|		    |	    |	    |	  4 (100)|	    |
|   1 |  SORT ORDER BY			|		    |	 78 |  2964 |	  4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS			|		    |	 78 |  2964 |	  3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS 		|		    |	 78 |  2964 |	  3   (0)| 00:00:01 |
|   4 |     INLIST ITERATOR		|		    |	    |	    |		 |	    |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS	    |	  2 |	 32 |	  2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN 	| DEPT_ID_PK	    |	  2 |	    |	  1   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN		| EMP_DEPARTMENT_IX |	  7 |	    |	  0   (0)|	    |
|   8 |    TABLE ACCESS BY INDEX ROWID	| EMPLOYEES	    |	 39 |	858 |	  1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Peeked Binds (identified by position):
--------------------------------------
 
   1 - :DPT_ID01 (NUMBER): 50
   2 - :DPT_ID02 (NUMBER): 80
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access(("D"."DEPARTMENT_ID"=:DPT_ID01 OR "D"."DEPARTMENT_ID"=:DPT_ID02))
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter(("E"."DEPARTMENT_ID"=:DPT_ID01 OR "E"."DEPARTMENT_ID"=:DPT_ID02))
 
Note
-----
   - this is an adaptive plan
 
 
40 rows selected.