Discussion :

[Rhodo33]

Bonjour,
J'utilise Oracle 11 et j'aimerais optimiser mes requêtes SQL en temps d'exécution :
1 - Je dois entrer 10 millions de lignes dans une table Ta. Cette écriture ne se fera qu'une seule fois.
2 - Puis je dois exécuter une cinquantaine de requêtes SQL différentes sur cette tables Ta reliées à d'autres via des JOIN.

Je peux déclarer quelques colonnes de Ta avec la contrainte NOT NULL ENABLE et éventuellement avec des valeurs par défauts.

Ces contraintes peuvent-elles me faire gagner du temps lors du remplissage de la table ou lors de mes requêtes SQL ?

[Pomalaix]

Une contrainte NOT NULL peut, dans les cas favorables, permettre de répondre à la requête par le seul usage de l'index, c'est à dire sans consulter la table dont il dépend.
C'est ce qu'on appelle un index couvrant : un index basé sur toutes les colonnes impliquées dans la requête.

En l'absence de la contrainte NOT NULL, comme les index normaux (je précise monocolonnes pour contrer les pinailleurs à la Mnitu ou MHouri ) de type BTree ne stockent pas les valeurs nulles, on n'a pas la certitude de retrouver les données dans l'index, et on est donc forcé de passer par la table.

Cela dit, une contrainte doit d'abord être fondée fonctionnellement. Si en plus elle permet d'améliorer les performances, c'est du bonus, mais ça ne peut pas être sa seule raison d'être.

[pacmann]

Salut,

Pom', tu peux étendre ton raisonnement aux indexes multi-colonnes, qui ne stockent que les entrées dont au moins une des colonnes est non nulle

[Pomalaix]

Pom', tu peux étendre ton raisonnement aux indexes multi-colonnes, qui ne stockent que les entrées dont au moins une des colonnes est non nulle

C'est bien le sens de ma parenthèse...

[Mohamed.Houri]

Ces contraintes peuvent-elles me faire gagner du temps lors du remplissage de la table ou lors de mes requêtes SQL ?

Il y a une nouvelle optimisation des DDL en 11g (DDL optimisation). Qui permet d'éviter de faire un update de la valeur par défaut sur la table lorsque cette colonne est ajoutée. Uniquement son "méta data" est enregistré. Exemple

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
 
QL> drop table t;
 
Table dropped.
 
SQL> create table t as select rownum n1 from dual connect by level <= 1e4;
 
Table created.
 
 
SQL> alter table t add n2 number default 0 not null;
 
Table altered.
 
 
SQL> select count(1) from t where n2 = 42;
 
  COUNT(1)
----------
         0
 
SQL> select * from table(dbms_xplan.display_cursor);
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     9 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    13 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(NVL("N2",0)=42)
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Avez-vous remarqué quelques choses?

Sinon, observez alors très bien la partie prédicat.

Code :

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

1
2
 
2 - filter(NVL("N2",0)=42)

Ceci ne correspond pas à la partie where clause de mon select

Code :

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

1
2
 
SQL> select count(1) from t where n2 = 42;

Ceci vient du fait que la colonne n2 a été ajoutée sans être mise à jour accélérant ainsi le DDL.

Par contre il existe un inconvénient à cela lors de vos "selects". Vous créez un index sur n2 et votre prédicat est changé en nvl(n2, 0). Ceci peut faire en sorte que votre index ne soit pas utilisé. Bien que dans ce cas il l'ait été pour moi. Mais ce n'est pas forcément le cas tous les jours.

[mnitu]

D’une manière générale l’ajout d’une contrainte implique une action (vérification) de plus et cela ne va pas avec l’amélioration des performances des inserts (remplissage). Mais cela implique également que les réponses que la base donne à vos questions peuvent être fausses et ça peut être très grave.

D’autre part la présence des contraints permet à l’optimiseur surtout dans les dernières versions d’Oracle de transformer vos requêtes d’une manière parfois très efficace.

[Rhodo33]

Merci pour ces infos.

En fait, je remplis la table Ta à partir d'un gros fichier "plat" (plusieurs Go) au format CSV et je me demande si la chasse aux valeurs Null en vaut la peine et permettrait de gagner du temps ( càd quelques heures !).
J'ai l'impression que oui vu quelques essais, mais je n'en ai pas une totale certitude.
Vos réponse me laissent penser que si cette action est facile, il vaut mieux ne pas s'en passer ?

(Par exemple, si une valeur numérique manque, je peux mettre un -1 de convention sans difficulté quant au codage.)

[mnitu]

Si les seules contraintes impliquées sont de type not null alors laisse-les en place et testez le chargement de votre table. Si vous dépassez le fenêtre de temps envisageable pour cette opération il y a des autres techniques d’optimisation de chargement qui peuvent être envisagées.