IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Nouvelles fonctionnalités détaillées de Firebird 5

Partie 2 : SQL

Cette documentation est sponsorisée et créée avec le parrainage et le soutien d'IBSurgeon, fournisseur de HQbird (distribution avancée de Firebird) et fournisseur de services d'optimisation des performances, de migration et de support technique pour Firebird. Elle est sous licence de documentation publique.

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. 1 commentaire Donner une note à l´article (5)

Article lu   fois.

Les deux auteur et traducteur

Traducteur : Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Nouvelles fonctionnalités du langage SQL

I-A. Prise en charge de la clause WHEN NOT MATCHED BY SOURCE dans l'instruction MERGE

L'opérateur MERGE fusionne les lignes de la table source et de la table cible (ou vue actualisable).

Lors de l'exécution d'une instruction MERGE, les lignes sources sont lues, puis l’ordre INSERT, UPDATE ou DELETE est effectué sur la table cible en fonction des conditions.

Syntaxe de MERGE :

 
Sélectionnez
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.
MERGE
  INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join condition>
  <merge when> [<merge when> ...]
  [<plan clause>]
  [<order by clause>]
  [<returning clause>]

<source> ::= tablename | (<select_stmt>)

<merge when> ::=
    <merge when matched>
  | <merge when not matched by target>
  | <merge when not matched by source>

<merge when matched> ::=
  WHEN MATCHED [ AND <condition> ]
  THEN { UPDATE SET <assignment_list> | DELETE }

<merge when not matched by target> ::=
  WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ]
  THEN INSERT [ <left paren> <column_list> <right paren> ]
  VALUES <left paren> <value_list> <right paren>

<merge when not matched by source> ::=
  WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
  { UPDATE SET <assignment list> | DELETE }

Firebird 5.0 a introduit les branches conditionnelles <merge when not matched by source> , qui vous permettent de mettre à jour ou de supprimer des lignes de la table cible si elles ne sont pas présentes dans la source de données.

Désormais, l'opérateur MERGE est un outil véritablement universel pour toute modification de la table cible pour un certain ensemble de données.

La source de données peut être une table, une vue, une procédure stockée ou une table dérivée. Lorsqu'une instruction MERGE est exécutée, une jointure est effectuée entre la table source (USING) et la table cible. Le type de jointure dépend de la présence de clauses WHEN NOT MATCHED :

  • <merge when not matched by target>
    <merge when not matched by source>  FULL JOIN
  • <merge when not matched by source>  RIGHT JOIN
  • <merge when not matched by target>  LEFT JOIN
  • only <merge when matched>  INNER JOIN

L'action sur la table cible, ainsi que les conditions dans lesquelles elle est effectuée, sont décrites dans la clause WHEN.

Il est possible d'avoir plusieurs clauses WHEN MATCHED, WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE.

Si la condition de la clause WHEN n'est pas remplie, Firebird l'ignore et passe à la clause suivante.

Cela continuera jusqu'à ce que la condition de l'une des clauses WHEN soit remplie. Dans ce cas, l'action associée à cette clause WHEN est effectuée pour la ligne résultante de la jointure sur la table cible. Une seule action est effectuée pour chaque ligne de résultat de la jointure.

I-A-1. WHEN MATCHED

Spécifie que toutes les lignes cibles qui correspondent aux lignes renvoyées par l'expression <source> ON <join condition> et satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou supprimées (clause DELETE) conformément à la clause <merge when matched>.

Plusieurs clauses WHEN MATCHED sont autorisées. Si c’est le cas, elles doivent toutes être complétées par des termes de recherche supplémentaires, à l'exception du dernier. Une instruction MERGE ne peut pas mettre à jour la même ligne plusieurs fois ni mettre à jour et supprimer la même ligne en même temps.

I-A-2. WHEN NOT MATCHED BY SOURCE

Spécifie que toutes les lignes cibles qui ne correspondent pas aux lignes renvoyées par l'expression <source> ON <join condition> et satisfont à des conditions de recherche supplémentaires sont mises à jour (clause UPDATE) ou sont supprimées (clause DELETE) conformément à la clause <merge when not matched by source >. La clause WHEN NOT MATCHED BY SOURCE est devenue disponible dans Firebird 5.0.

Plusieurs clauses WHEN NOT MATCHED BY SOURCE sont autorisées. Si c’est le cas, elles doivent toutes être complétées par des termes de recherche supplémentaires, à l'exception du dernier.

Note : dans la liste SET d'une clause UPDATE, cela n'a aucun sens d'utiliser des expressions qui font référence à <source>, puisqu'aucune entrée de <source> ne correspond aux entrées cibles.

I-A-3. Exemple d’utilisation du MERGE avec la clause WHEN NOT MATCHED BY SOURCE

Supposons que vous ayez une liste de prix dans la table temporaire tmp_price et que vous deviez mettre à jour le prix actuel afin que :

  • si le produit ne figure pas dans la liste de prix actuelle, ajoutez-le ;
  • si le produit figure dans la liste de prix actuelle, mettez à jour son prix ;
  • si le produit est inclus dans la liste de prix en vigueur, mais n'est pas dans la nouvelle, alors supprimez cette ligne de prix.

Toutes ces actions peuvent être effectuées dans une seule commande SQL :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
MERGE INTO price
USING tmp_price
   ON price.good_id = tmp_price.good_id
WHEN NOT MATCHED
--  add if it wasn't there
    THEN INSERT(good_id, name, cost)
    VALUES(tmp_price.good_id, tmp_price.name, tmp_price.cost)
WHEN MATCHED AND price.cost <> tmp_price.cost THEN
--  update the price if the product is in the new price list -  and the price is different
    UPDATE SET cost = tmp_price.cost
WHEN NOT MATCHED BY SOURCE
--  if there is no product in the new price list, then we remove it -  from the current price list
    DELETE;

Dans cet exemple, à la place de la table temporaire tmp_price, il peut y avoir une requête SELECT ou une procédure stockée plus ou moins complexe.

Notez que puisque les clauses WHEN NOT MATCHED [BY TARGET] et WHEN NOT MATCHED BY SOURCE sont présentes, la jointure entre la table cible et la source de données se fera à l'aide d'un FULL JOIN.

Dans la version actuelle de Firebird, FULL JOIN n'utilisera pas d'index à droite ni à gauche et la performance sera dégradée.

I-B. Clause SKIP LOCKED

Firebird 5.0 a introduit la clause SKIP LOCKED, qui peut être utilisée dans les instructions SELECT .. WITH LOCK, UPDATE et DELETE.

L'utilisation de cette clause amène le moteur à ignorer les lignes verrouillées par d'autres transactions au lieu de les attendre ou de provoquer des erreurs de conflit de mise à jour.

L'utilisation de SKIP LOCKED est utile pour implémenter des files d'attente de travail, dans lesquelles un ou plusieurs processus soumettent du travail à une table et émettent un événement, tandis que les threads de travail (exécuteur) écoutent les événements et lisent/suppriment des éléments de la table. Grâce à SKIP LOCKED, plusieurs travaux peuvent recevoir des tâches exclusives d'une table sans conflits.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT
  [FIRST ...]
  [SKIP ...]
  FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [{ ROWS ... } | {OFFSET ...} | {FETCH ...}]
  [FOR UPDATE [OF ...]]
  [WITH LOCK [SKIP LOCKED]]
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
UPDATE <sometable>
  SET ...
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
DELETE FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]

Lorsque les clauses FIRST/SKIP/ROWS/OFFSET/FETCH sont présentes dans la requête, le moteur ignorera d'abord les lignes verrouillées, puis appliquera des restrictions (FIRST…​) aux lignes restantes dans un ensemble de résultats.

Exemples :

  • créer une table et un déclencheur
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create table emails_queue (
  subject varchar(60) not null,
  text blob sub_type text not null
);
set term !;
create trigger emails_queue_ins after insert on emails_queue
as
begin
  post_event('EMAILS_QUEUE');
end!
set term ;!
  • envoyer un message par l’application
 
Sélectionnez
1.
2.
3.
insert into emails_queue (subject, text)
values ('E-mail subject', 'E-mail text...');
commit;
  • application cliente
 
Sélectionnez
1.
2.
3.
4.
5.
6.
-- The client application can check table to the EMAILS_QUEUE event,
-- to send emails using this command :
delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

Plusieurs instances d'une application peuvent être en cours d'exécution, par exemple pour l'équilibrage de charge.

Note : l'utilisation de SKIP LOCKED pour organiser les files d'attente sera abordée dans un article séparé.

I-C. Prise en charge du renvoi de plusieurs lignes par les opérateurs avec la clause RETURNING

Depuis Firebird 5.0, les instructions de modification côté client INSERT .. SELECT, UPDATE, DELETE, UPDATE OR INSERT et MERGE, avec la clause RETURNING renverront un curseur : cela signifie qu'elles sont capables de renvoyer plusieurs lignes au lieu d’émettre le message d’erreur "multiple rows in singleton select " comme précédemment.

Ces requêtes sont désormais décrites comme isc_info_sql_stmt_select, alors que dans les versions précédentes, elles étaient décrites comme isc_info_sql_stmt_exec_procedure.

Les instructions singleton INSERT .. VALUES et les instructions positionnées UPDATE et DELETE (celles contenant une clause WHERE CURRENT OF) conservent le comportement existant et sont décrites comme isc_info_sql_stmt_exec_procedure.

Cependant, toutes ces instructions, si elles sont utilisées dans PSQL et si la clause RETURNING est utilisée, sont toujours traitées comme des singletons.

Exemples d'instructions de modification contenant RETURNING et renvoyant un curseur :

 
Sélectionnez
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.
INSERT INTO dest(name, val)
SELECT desc, num + 1 FROM src WHERE id_parent = 5
RETURNING id, name, val;

UPDATE dest
SET a = a + 1
RETURNING id, a;

DELETE FROM dest
WHERE price < 0.52
RETURNING id;

MERGE INTO PRODUCT_INVENTORY AS TARGET
USING (
  SELECT
    SL.ID_PRODUCT,
    SUM(SL.QUANTITY)
  FROM
    SALES_ORDER_LINE SL
    JOIN SALES_ORDER S ON S.ID = SL.ID_SALES_ORDER
  WHERE S.BYDATE = CURRENT_DATE
    AND SL.ID_PRODUCT = :ID_PRODUCT
  GROUP BY 1
) AS SRC(ID_PRODUCT, QUANTITY)
ON TARGET.ID_PRODUCT = SRC.ID_PRODUCT
WHEN MATCHED AND TARGET.QUANTITY - SRC.QUANTITY <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET
    TARGET.QUANTITY = TARGET.QUANTITY - SRC.QUANTITY,
    TARGET.BYDATE = CURRENT_DATE
RETURNING OLD.QUANTITY, NEW.QUANTITY, SRC.QUANTITY;

I-D. index partiels

Dans Firebird 5.0, lors de la création d'un index, il est devenu possible de spécifier une clause WHERE facultative, qui spécifie une condition de recherche limitant le sous-ensemble de lignes de table à indexer.

De tels index sont appelés index partiels. La condition de recherche doit contenir une ou plusieurs colonnes de table. La définition d'index partiel peut inclure une spécification UNIQUE. Dans ce cas, chaque clef de l'index doit être unique. Cela vous permet de garantir l'unicité d'un certain sous-ensemble de lignes de la table.

La définition d'un index partiel peut également inclure une clause COMPUTED BY afin que l'index partiel puisse être calculé.

La syntaxe complète pour créer un index est donc la suivante :

 
Sélectionnez
1.
2.
3.
4.
5.
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX indexname ON tablename
{(<column_list>) | COMPUTED [BY] (<value_expression>)}
[WHERE <search_condition>]
<column_list> ::= col [, col ...]

L'optimiseur ne peut utiliser un index partiel que dans les cas suivants :

  • la clause WHERE inclut exactement la même expression logique que celle définie pour l'index ;
  • la condition de recherche définie pour l'index contient des expressions booléennes combinées avec un OU, et l'une d'entre elles est explicitement incluse dans la clause WHERE ;
  • la condition de recherche définie pour l'index spécifie IS NOT NULL et la clause WHERE inclut une expression pour la même colonne dont on sait qu'elle ignore NULL.

Si un index régulier et un index partiel existent pour le même ensemble de colonnes, l'optimiseur choisira l'index régulier même si la clause WHERE inclut la même expression que celle définie dans l'index partiel.

La raison de ce comportement est que l'index régulier a une meilleure sélectivité que l'index partiel.

Mais il existe des exceptions à cette règle : l'utilisation de prédicats avec une mauvaise sélectivité sur les colonnes indexées, tels que <>, IS DISTINCT FROM ou IS NOT NULL, à condition que le prédicat soit utilisé dans un index partiel.

Note : les index partiels ne peuvent pas être utilisés pour contraindre une clef primaire ou une clef étrangère : la clause USING INDEX ne peut pas spécifier une définition d'index partiel.

Voyons quand les indices partiels sont utiles.

Exemple 1. Unicité partielle

Disons que nous avons une table stockant l’adresse courriel d’une personne.

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE MAN_EMAILS                                      
 (  CODE_MAN_EMAIL  BIGINT  GENERATED BY DEFAULT AS IDENTITY 
  , CODE_MAN        BIGINT      NOT NULL                     
  , EMAIL           VARCHAR(50) NOT NULL                     
  , DEFAULT_FLAG    BOOLEAN     DEFAULT FALSE NOT NULL       
  , CONSTRAINT PK_MAN_EMAILS                                 
       PRIMARY KEY(CODE_MAN_EMAIL)                           
  , CONSTRAINT FK_EMAILS_REF_MAN                             
       FOREIGN KEY(CODE_MAN) REFERENCES MAN(CODE_MAN)        
 )                                                           
;

Une personne peut avoir plusieurs adresses courriel, mais une seule peut être l'adresse par défaut. Un index réguliler ou une restriction unique ne fonctionnera pas dans ce cas, puisque nous serons limités à seulement deux adresses.

Ici, nous pouvons utiliser l'index unique partiel :

 
Sélectionnez
1.
2.
3.
4.
SELECT                                                        
  COUNT(*) FILTER(WHERE IS_ANCESTOR IS TRUE)  AS CNT_ANCESTOR,
  COUNT(*) FILTER(WHERE IS_ANCESTOR IS FALSE) AS CNT_OTHER    
FROM HORSE

Ainsi, pour une personne, on autorise autant d'adresses que souhaité avec DEFAULT_FLAG=FALSE et une seule adresse avec DEFAULT_FLAG=TRUE.

Les index partiels peuvent être utilisés simplement pour rendre l'index plus compact.

Exemple 2. Réduire la taille de l'index

Supposons que vous ayez une table de chevaux HORSE dans votre base de données et qu'elle comporte la colonne IS_ANCESTOR utilisée pour indiquer si le cheval est l'ancêtre d'une lignée ou d'une famille. Évidemment, il y a des centaines de fois moins d’ancêtres que les autres chevaux – voir le résultat de la requête ci-dessous :

 
Sélectionnez
1.
2.
3.
4.
SELECT                                                        
  COUNT(*) FILTER(WHERE IS_ANCESTOR IS TRUE)  AS CNT_ANCESTOR,
  COUNT(*) FILTER(WHERE IS_ANCESTOR IS FALSE) AS CNT_OTHER    
FROM HORSE

CNT_ANCESTOR CNT_OTHER
===================== =====================
1426 518197

Le but est d’obtenir rapidement une liste d’ancêtres. D'après les statistiques ci-dessus, il est également évident que pour l'option IS_ANCESTOR IS FALSE, l'utilisation de l'index est pratiquement inutile.

Essayons de créer un index régulier :

CREATE INDEX IDX_HORSE_ANCESTOR ON HORSE(IS_ANCESTOR);

Mais dans ce cas, un tel index sera redondant.

Regardons ses statistiques à l'aide de l'outil gstat :

Index IDX_HORSE_ANCESTOR (26)
Root page: 163419, depth: 2, leaf buckets: 159, nodes: 519623
Average node length: 4.94, total dup: 519621, max dup: 518196
Average key length: 2.00, compression ratio: 0.50
Average prefix length: 1.00, average data length: 0.00
Clustering factor: 9809, ratio: 0.02
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 158

Comme vous pouvez le voir, l'index partiel est beaucoup plus compact : il y a 1 426 nœuds dans l'index partiel au lieu de 519 623 dans l'index régulier.

Vérifions qu'il peut être utilisé pour obtenir des ancêtres :

 
Sélectionnez
1.
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE IS_ANCESTOR IS TRUE;
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" Access By ID
                -> Bitmap
                    -> Index "IDX_HORSE_ANCESTOR" Full Scan
                COUNT

=====================
1426
Current memory = 556868928
Delta memory = 176
Max memory = 575376064
Elapsed time = 0.007 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 2192
Per table statistics:
------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
------------+---------+---------+---------+---------+---------+---------+---------+---------+
HORSE | | 1426| | | | | | |
------------+---------+---------+---------+---------+---------+---------+---------+---------+

Veuillez noter que si vous spécifiez WHERE IS_ANCESTOR ou WHERE IS_ANCESTOR = TRUE dans la requête, l'index ne sera pas utilisé. Il est nécessaire que l'expression spécifiée pour filtrer l'index corresponde complètement à l'expression dans le WHERE de votre requête.

Un autre cas où les index partiels peuvent être utiles est celui de leur utilisation avec des prédicats non sélectifs.

Exemple 3. Utilisation d'index partiels avec des prédicats non sélectifs

Supposons que nous devions récupérer tous les chevaux morts dont la date de décès est connue. Un cheval est définitivement mort s'il possède une date de décès, mais il arrive souvent qu'elle ne soit pas répertoriée ou qu'elle soit tout simplement inconnue. De plus, le nombre de dates de décès inconnues est bien supérieur à celui des dates connues. Pour ce faire, nous écrirons la requête suivante :

 
Sélectionnez
1.
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE DEATHDATE IS NOT NULL;

Nous souhaitons obtenir cette liste le plus rapidement possible, nous allons donc essayer de créer un index sur la colonne DEATHDATE.

 
Sélectionnez
1.
2.
CREATE INDEX IDX_HORSE_DEATHDATE
ON HORSE(DEATHDATE);

Essayons maintenant d'exécuter la requête ci-dessus et d'examiner son plan et ses statistiques :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" Full Scan

                COUNT

=====================
16234
Current memory = 2579550800
Delta memory = 176
Max memory = 2596993840
Elapsed time = 0.196 sec
Buffers = 153600
Reads = 0
Writes = 0
Fetches = 555810
Per table statistics:
------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
------------+---------+---------+---------+---------+---------+---------+---------+---------+
HORSE | 519623| | | | | | | |
------------+---------+---------+---------+---------+---------+---------+---------+---------+

Comme vous pouvez le constater, il n’a pas été possible d’utiliser l’index.

La raison en est que les prédicats IS NOT NULL, <>, IS DISTINCT FROM sont peu sélectifs.

Actuellement, Firebird ne dispose pas d'histogrammes avec la distribution des valeurs des clefs d'index et la distribution est donc supposée uniforme. Avec une distribution uniforme, cela n'a aucun sens d'utiliser un index pour de tels prédicats, ce qui est fait.

Essayons maintenant de supprimer l'index créé précédemment et de créer un index partiel à la place :

 
Sélectionnez
1.
2.
3.
4.
DROP INDEX IDX_HORSE_DEATHDATE;

CREATE INDEX IDX_HORSE_DEATHDATE
ON HORSE(DEATHDATE) WHERE DEATHDATE IS NOT NULL;

Et relançons la requête précédente

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" Access By ID
                -> Bitmap
                    -> Index "IDX_HORSE_DEATHDATE" Full Scan

                COUNT

=====================
16234
Current memory = 2579766848
Delta memory = 176
Max memory = 2596993840
Elapsed time = 0.017 sec
Buffers = 153600
Reads = 0
Writes = 0
Fetches = 21525
Per table statistics:
------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
------------+---------+---------+---------+---------+---------+---------+---------+---------+
HORSE | | 16234| | | | | | |
------------+---------+---------+---------+---------+---------+---------+---------+---------+

Comme vous pouvez le voir, l'optimiseur a réussi à utiliser notre index. Mais le plus intéressant est que notre index continuera à fonctionner avec d'autres prédicats de comparaison de dates (mais cela ne fonctionnera pas pour IS NULL).

Voir exemple ci-dessous :

 
Sélectionnez
1.
2.
3.
SELECT COUNT(*)
FROM HORSE
WHERE DEATHDATE = DATE'01.01.2005';
 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
Select Expression
    -> Aggregate
        -> Filter
            -> Table "HORSE" Access By ID
                -> Bitmap
                    -> Index "IDX_HORSE_DEATHDATE" Range Scan (full match)

                COUNT

=====================
190
Current memory = 2579872992
Delta memory = 192
Max memory = 2596993840
Elapsed time = 0.004 sec
Buffers = 153600
Reads = 0
Writes = 0
Fetches = 376
Per table statistics:
------------+---------+---------+---------+---------+---------+---------+---------+---------+
Table name | Natural | Index | Insert | Update | Delete | Backout | Purge | Expunge |
------------+---------+---------+---------+---------+---------+---------+---------+---------+
HORSE | | 190| | | | | | |
------------+---------+---------+---------+---------+---------+---------+---------+---------+

Dans ce cas, l'optimiseur s'est rendu compte que la condition de filtre IS NOT NULL dans l'index partiel couvre tous les autres prédicats qui ne se comparent pas à NULL.

Il est important de noter que si vous spécifiez la condition COLONNE > 2 dans l'index partiel et que la requête contient la condition de recherche COLONNE > 1, alors malgré le fait que tout nombre supérieur à 2 est également supérieur à 1, l'index partiel sera ne pas être utilisé. L'optimiseur n'est pas assez intelligent pour dériver cette condition d'équivalence.

I-E. Fonctions UNICODE_CHAR et UNICODE_VAL

Firebird 2.1 a introduit les fonctions ASCII_CHAR - renvoyant un caractère par son code dans la table ASCII, et ASCII_VAL - renvoyant le code dans la table ASCII par caractère. Ces fonctions s'appliquent uniquement aux codages sur un seul octet ; il n'y a rien de similaire pour UTF-8. Firebird 5.0 a ajouté deux fonctions supplémentaires qui fonctionnent avec les encodages multioctets :

UNICODE_CHAR (number)
UNICODE_VAL (string)

La fonction UNICODE_CHAR renvoie le caractère UNICODE pour le point de code donné. La fonction UNICODE_VAL renvoie le point de code UTF-32 pour le premier caractère d'une chaîne. Pour une chaîne vide, 0 est renvoyé.

 
Sélectionnez
1.
2.
3.
4.
SELECT                                         
  UNICODE_VAL(UNICODE_CHAR(0x1F601)) AS CP_VAL,
  UNICODE_CHAR(0x1F601)              AS CH     
FROM RDB$DATABASE

Exemple de résultat(1) :

I-F. Expressions de requête entre parenthèses

Dans la version 5.0, la syntaxe DML a été étendue pour permettre l'utilisation d'une expression de requête entre parenthèses (SELECT, y compris les clauses order by, offset et fetch, mais sans clause with), alors qu'auparavant seule la spécification de requête était autorisée (SELECT sans clauses with, order by, offset et fetch).

Cela vous permet d'écrire des requêtes plus claires, notamment dans les opérateurs UNION, et offre une plus grande compatibilité avec les opérateurs générés par certains ORM.

Note : l’utilisation d’expressions de requête entre parenthèses n’est pas gratuite du point de vue du moteur Firebird, car elles nécessitent un contexte de requête supplémentaire par rapport à une simple spécification de requête. Le nombre maximum de contextes de requête dans une instruction est limité à 255.

Exemple :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

I-G. Littéraux améliorés

I-G-1. Syntaxe complète des chaînes littérales

La syntaxe littérale de la chaîne de caractères a été modifiée pour prendre en charge la syntaxe SQL standard complète. Cela signifie que le littéral peut être « interrompu » par des espaces ou des commentaires. Cela peut être utilisé, par exemple, pour diviser un long littéral sur plusieurs lignes ou pour fournir des commentaires en ligne.

Syntaxe littérale de chaîne selon ISO/IEC 9075-2:2016 SQL - Partie 2 : Fondation

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
<character string literal> ::=
  [ <introducer> <character set specification> ]
    <quote> [ <character representation>... ] <quote>
    [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }...

Exemple :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
-- spaces between literals
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: 'abcd'

-- comment and spaces between literals
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: 'abcd'

I-G-2. Syntaxe complète pour les littéraux binaires

La syntaxe des littéraux de chaîne binaire a été modifiée pour prendre en charge la syntaxe SQL standard complète. Cela signifie que le littéral peut contenir des espaces pour séparer les caractères hexadécimaux et peut être « interrompu » par des espaces ou des commentaires. Cela peut être utilisé, par exemple, pour rendre une chaîne hexadécimale plus lisible en regroupant des caractères, pour diviser un long littéral sur plusieurs lignes ou pour fournir des commentaires en ligne.

La syntaxe littérale binaire est conforme à la norme ISO/IEC 9075-2:2016 SQL - Partie 2 : Fondation

 
Sélectionnez
1.
2.
3.
4.
5.
<binary string literal> ::=
  {X|x} <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }...
      ] <quote>
    [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
    <hexit> [ <space>... ] }... ] <quote> }... ]

Exemples :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
-- Grouping by bytes (spaces within a literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY

--  spaces between literals
select _win1252 x'42494e'
                 '415259'
from RDB$DATABASE;
-- output: BINARY

I-H. Prédicat IN amélioré

Avant Firebird 5.0, le prédicat IN avec une liste de constantes était limité à 1 500 éléments, car il était traité en convertissant de manière récursive l'expression originale en une forme équivalente.

Ceci

F IN (V1, V2, ... VN)

Est transformé en

(F = V1) OR (F = V2) OR .... (F = VN)

Depuis Firebird 5.0, le traitement des prédicats IN <list> est linéaire. La limite de 1 500 objets a été augmentée à 65 535 objets. De plus, les requêtes utilisant le prédicat IN avec une liste de constantes sont traitées beaucoup plus rapidement. Cela a été discuté en détail dans la première partie.

I-I. Package RDB$BLOB_UTIL

Les opérations avec les BLOB dans PSQL n'étaient pas rapides, car toute modification d'un BLOB crée toujours un nouveau BLOB temporaire, ce qui entraîne une consommation de mémoire supplémentaire et, dans certains cas, un fichier de base de données plus volumineux pour stocker les BLOB temporaires. Dans Firebird 4.0.2, une fonction intégrée, BLOB_APPEND, a été ajoutée pour résoudre les problèmes de concaténation de BLOB. Dans Firebird 5.0, un package RDB$BLOB_UTIL intégré a été ajouté avec des procédures et des fonctions pour une manipulation plus efficace des BLOB.

Nous montrerons plusieurs exemples pratiques d'utilisation des fonctions du package RDB$NLOB_UTIL. La description complète peut être trouvée dans les notes de version de Firebird 5.0 et dans la référence du langage SQL de Firebird 5.0.

I-I-1. Utilisation de la fonction RDB$BLOB_UTIL.NEW_BLOB

La fonction RDB$BLOB_UTIL.NEW_BLOB crée un nouveau BLOB SUB_TYPE BINARY. Elle renvoie un BLOB adapté à l'ajout de données, à l’instar de BLOB_APPEND.

La différence par rapport à BLOB_APPEND est que vous pouvez définir les paramètres SEGMENTED et TEMP_STORAGE.

La fonction BLOB_APPEND crée toujours des blobs dans un stockage temporaire, ce qui n'est pas toujours la meilleure approche si le blob créé est stocké dans une table permanente, car cela nécessiterait une opération de copie.

Le BLOB renvoyé par RDB$BLOB_UTIL.NEW_BLOB peut être utilisé avec BLOB_APPEND pour ajouter des données, même si TEMP_STORAGE = FALSE.

Tableau 1. Paramètres d'entrée pour la fonction RDB$BLOB_UTIL.NEW_BLOB

Paramètre

Type

Description

SEGMENTED

BOOLEAN NOT NULL

Type de BLOB.
VRAI - un BLOB segmenté sera créé,
FAUX - un BLOB en streaming sera créé.

TEMP_STORAGE

BOOLEAN NOT NULL

Dans quel stockage le BLOB est-il créé ?
VRAI - temporaire
FAUX - permanent (pour écrire dans une table normale).

I-I-1-a. Lire des BLOBs par morceaux

Lorsque vous deviez lire une partie d'un BLOB, vous utilisiez la fonction SUBSTRING, mais cette fonction présente un inconvénient majeur : elle renvoie toujours un nouveau BLOB temporaire.

Depuis Firebird 5.0, vous pouvez utiliser la fonction RDB$BLOB_UTIL.READ_DATA à cet effet.

Tableau 2. Paramètres d'entrée pour la fonction RDB$BLOB_UTIL.READ_DATA

Paramètre

Type

Description

HANDLE

INTEGER NOT NULL

N° du HANDLE de BLOB ouvert

LENGTH

INTEGER

Nombre de bytes à lire

Type de retour :VARBINAIRE(32765)

La fonction RDB$BLOB_UTIL.READ_DATA est utilisée pour lire des éléments de données à partir d'un handle BLOB ouvert avec RDB$BLOB_UTIL.OPEN_BLOB. Lorsque le BLOB a été entièrement lu et qu'il n'y a plus de données, il renvoie NULL.

Si la valeur du paramètre LENGTH est un nombre positif, un VARBINARY de longueur maximale LENGTH est renvoyé.

Si NULL est transmis à LENGTH, un segment BLOB d'une longueur maximale de 32 765 est renvoyé.

Lorsque vous avez terminé avec un handle de BLOB, vous devez le fermer à l'aide de la procédure RDB$BLOB_UTIL.CLOSE_HANDLE.

Exemple 4. Ouvrir un BLOB et le renvoyer morceau par morceau dans EXECUTE BLOCK

 
Sélectionnez
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.
execute block returns (s varchar(10))
as
    declare b blob = '1234567';
    declare bhandle integer;
begin
    --  opens a BLOB for reading and returns its handle.
    bhandle = rdb$blob_util.open_blob(b);

    -- Getting the blob in parts
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- When there is no more data, NULL is returned.
    s = rdb$blob_util.read_data(bhandle, 3);
    suspend;

    -- Close the BLOB handle.
    execute procedure rdb$blob_util.close_handle(bhandle);
end

En passant la valeur NULL en paramètre LENGTH, vous pouvez lire un BLOB segment par segment, si les segments ne dépassent pas 32765 octets.

Écrivons une procédure pour renvoyer un BLOB segment par segment

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE OR ALTER PROCEDURE SP_GET_BLOB_SEGEMENTS (
  TXT BLOB SUB_TYPE TEXT CHARACTER SET NONE
)
RETURNS (
  SEG VARCHAR(32765) CHARACTER SET NONE
)
AS
  DECLARE H INTEGER;
BEGIN
  H = RDB$BLOB_UTIL.OPEN_BLOB(TXT);
  SEG = RDB$BLOB_UTIL.READ_DATA(H, NULL);
  WHILE (SEG IS NOT NULL) DO
  BEGIN
    SUSPEND;
    SEG = RDB$BLOB_UTIL.READ_DATA(H, NULL);
  END
  EXECUTE PROCEDURE RDB$BLOB_UTIL.CLOSE_HANDLE(H);
END

On peut l'utiliser par exemple comme ceci :

 
Sélectionnez
1.
2.
3.
4.
5.
6.
7.
8.
9.
WITH
  T AS (
    SELECT LIST(CODE_HORSE) AS B
    FROM HORSE
  )
SELECT
  S.SEG
FROM T
  LEFT JOIN SP_GET_BLOB_SEGEMENTS(T.B) S ON TRUE

II. Résumé

Les nouvelles commandes SQL de Firebird 5.0 se concentrent sur une commodité accrue pour les développeurs (MERGE, SKIP LOCK), la prise en charge de grandes bases de données (index PARTIAL) et des performances accrues (RDB$BLOB_UTILS) de grandes bases de données avec des tables très longues.

Dans la partie suivante, nous examinerons plus en détail l’utilisation pratique de SKIP LOCK.

Veuillez envoyer toutes vos questions concernant ce document à Alexey Kovyazin sur firebirdsql.org.

III. Remerciements Developpez.com

Vous trouverez la version originale an anglais de cet article à cette adresse :

Detailed New Features of Firebird 5. Part 2: SQLs

Nous remercions le support IBSurgeon qui nous en a autorisé la traduction.

Auteur : Denis Simonov, adapté et édité par Alexey Kovyazin version 1.0 anglaise du 03.12.2023

Nous remercions également Escartefigue pour la mise au gabarit, la traduction et la relecture orthographique et SergioMaster pour la relecture technique.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   


Le résultat de la requête a été ajouté dans la traduction en français du document

Ce document est sous licence de documentation publique