Discussion :

[Ikebukuro]

Hello tout le monde,

J'ai voulu créer un index partiel sous Oracle même si je sais qu'Oracle ne le fais pas en standard (sauf pour les attributs NULL où là les rows avec cet attribut à Null ne sont pas indexés).

J'ai créé la fonction suivante qui retourne 80 pour les enregistrements avec le champ EMPLOYEES.DEPARTMENT_ID à 80 et NULL sinon.

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
CREATE OR REPLACE FUNCTION F_dpt_id_80(p_dpt_id EMPLOYEES.DEPARTMENT_ID%TYPE)
RETURN EMPLOYEES.DEPARTMENT_ID%TYPE 
DETERMINISTIC 
IS
BEGIN
	IF p_dpt_id = 80 THEN
		return p_dpt_id;
	ELSE
		return NULL;
	END IF;
 
EXCEPTION
	WHEN OTHERS THEN
	NULL;
END;
/

Ensuite je crée un index sur cette fonction.

Code :

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

create index EMP_DPT_ID_80 ON EMPLOYEES(F_dpt_id_80(department_id));

Dans ALL_INDEXES j'ai le bon nombre d'enregs, à savoir 34, ce qui correspond bien au nombre d'enregs avec la valeur 80 donc j'ai un index partiel sur la valeur indexée EMPLOYEES.DEPARTMENT_ID.

Là où ça coince c'est qu'Oracle refuse d'utiliser cet index même si j'utilise un hint :calim2.
Je précise que j'ai validé la syntaxe de l'emploi du hint en utilisant un autre ordre SQL avec un autre index ou même en mettant INDEX en minuscules.

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
76
77
78
79
80
81
SQL> select /*+ INDEX(EMPLOYEES EMP_DPT_ID_80) */ first_name from employees where department_id =80;

FIRST_NAME
--------------------
John
Karen
Alberto
Gerald
Eleni
Peter
David
Peter
Christopher
Nanette
Oliver

FIRST_NAME
--------------------
Janette
Patrick
Allan
Lindsey
Louise
Sarath
Clara
Danielle
Mattea
David
Sundar

FIRST_NAME
--------------------
Amit
Lisa
Harrison
Tayler
William
Elizabeth
Sundita
Ellen
Alyssa
Jonathon
Jack

FIRST_NAME
--------------------
Charles

34 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    10 |   100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    10 |   100 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("DEPARTMENT_ID"=80)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1279  bytes sent via SQL*Net to client
        545  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         34  rows processed

Donc mon index existe MAIS Oracle refuse de l'utiliser...
Si vous pouviez me dire pourquoi, un grand merci par avance

[mnitu]

Ca marche sans aucun hint sql mais il y a mieux à faire. Et supprimez vite le bloc d'exception qui est un vrai bug dans ce cas.

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
 
SQL> explain plan for
  2  
  2  select first_name
  3    from hr.employees e
  4   where F_dpt_id_80(department_id) = 80
  5  /
Explained
 
SQL> select * from table(dbms_xplan.display)
  3  /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 312955501
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    20 |     2   (0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    20 |     2   (0
|*  2 |   INDEX RANGE SCAN          | EMP_DPT_ID_80 |     1 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HR"."F_DPT_ID_80"("DEPARTMENT_ID")=80)
14 rows selected

[Waldar]

Donc mon index existe MAIS Oracle refuse de l'utiliser...

Parce que vous n'utilisez pas la fonction dans votre requête, cf. l'exemple de mnitu.

[Ikebukuro]

OUF, un gros merci pour vos réponses car je m'arrachais mes quelques rares cheveux :-)
Finalement c'était juste une erreur bête de ma part

mnitu, quand tu dis "qu'il y a mieux à faire", tu penses à quoi?

[mnitu]

...
mnitu, quand tu dis "qu'il y a mieux à faire", tu penses à quoi?

LA fonction en question peut être remplacée par un simple Case mais bon comme j'imagine que c'est juste un test pour comprendre que ça marche cela n'a pas finalement d'importance.