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.