Le blog de Pacman

1 décembre 2009 2 01 /12 /décembre /2009 13:22

Update JOIN : MERGE vs (in-line)view update !

Dans un article précédent, je disais que grâce à l'évolution de l'instruction MERGE sous 10g, on pouvait enfin faire des UPDATE de jointure efficaces sous Oracle.

Ce n'est pas tout à fait vrai. Car Oracle permet de mettre à jour des vues... sous certaines conditions seulement !
Nous allons donc dans la suite illustrer les limitations de l'in-line view update, et donc l'avantage certain de l'instruction MERGE.

1) L'exemple basique d'update d'in-line view

Reprenons les jeux de test créés dans l'article précédent et la requête de Mnitu (cf commentaire) :

SQL> set autotrace on
SQL> UPDATE (
2    SELECT a.val oldval, b.val newval
3    FROM test_merge a
4      JOIN test_merge_source b ON a.id = b.id
5 )
6   SET oldval = newval;

1000 rows updated.

------------------------------------------------------------------------
| Id | Operation           | Name              | Rows | Bytes | Cost |
-----------------------------------------------------------------------|
|   0 | UPDATE STATEMENT    |                   | 1000 | 13000 |   527|
|   1 | UPDATE             | TEST_MERGE        |       |       |      |
|* 2 |   HASH JOIN         |                   | 1000 | 13000 |   527|
|   3 |    TABLE ACCESS FULL| TEST_MERGE_SOURCE | 1000 | 7000 |     3|
|   4 |    TABLE ACCESS FULL| TEST_MERGE        | 1010K| 5918K|   501|
------------------------------------------------------------------------

C'est parfait, exactement ce qu'on souhaite !
Mais ça ne marche pas toujours...

2) Le cas de défaite

Nous étions dans le cas d'une relation 1-1.
Considérons à présent une relation maître-esclave.

SQL> CREATE TABLE acct (acct_num primary key, some_amount, acct_desc) AS
2 SELECT level, 0, lpad('x', mod(level, 10), 'x')
3 FROM DUAL
4 CONNECT BY level <= 100
5 /

Table created.

SQL> CREATE TABLE ecr(acct_num, ecr_num, ecr_amt, some_desc) AS
2 SELECT a.acct_num, b.ecr_num, b.ecr_amt, ' '
3 FROM acct a
4    CROSS JOIN (SELECT level as ecr_num, level * 100 as ecr_amt
5                FROM DUAL
6                CONNECT BY level <= 10) b
7 /

Table created.

SQL> ALTER TABLE ecr ADD CONSTRAINT pk_ecr PRIMARY KEY(acct_num, ecr_num)
2 /

Table altered.

SQL> exec dbms_stats.gather_table_stats('PACMAN', 'ACCT', cascade => true)

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('PACMAN', 'ECR', cascade => true)

PL/SQL procedure successfully completed.

On passera sur le fait que cet exemple doit forcément piétiner au moins une des formes normales...

Bref, faisons un premier essai, relativement idiot : mettre à jour la colonne some_amount de la table acct avec le montant associé dans ecr(ecr_amt) (ma formulation est volontairement floue, la punition sera immédiate).

SQL> UPDATE
2    (SELECT some_amount, ecr_amt
3     FROM acct a
4       JOIN ecr b ON a.acct_num = b.acct_num)
5 SET some_amount = ecr_amt;
SET some_amount = ecr_amt
    *
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Bah ouais, ça plante... voyons pourquoi.

3) Les limitations de l'UPDATE d'in-line views

Lorsqu'on met à jour une vue, qu'elle soit explicitement nommée, ou 'in-line' c'est à dire sous forme de "table dérivée" :
- on ne peut mettre à jour qu'une table à la fois

- la table mise à jour doit être "key-preserved"
Cela signifie que pour une ligne de la table à mettre à jour, il doit y avoir une ligne au plus dans le résultat de la vue.

Le terme "Key-preserved" s'applique bien à une table au sein d'une vue. Essayons par exemple de mettre à jour la table ecr avec les valeurs de acct en utilisant la même jointure :

SQL> ALTER TABLE ecr MODIFY some_desc VARCHAR2(4000) --ptit ajustement à cause du CTAS raté :)
2 /

Table altered.
SQL> UPDATE
2    (SELECT some_desc, acct_desc
3     FROM acct a
4       JOIN ecr b ON a.acct_num = b.acct_num)
5 SET some_desc= acct_desc;

1000 rows updated.

Ca marche, bien sûr, car une seule ligne est associée à chaque ligne de ecr.

La raison pour laquelle il faut que la table mise à jour doit être key-preserved se comprend aisément :
Si on a plusieurs lignes dans la jointure pour une même clef, on va sensément faire plusieurs mises à jour... et c'est la dernière qui gagne !

Mais la notion de dernière mise à jour dépend du plan d'exécution, ce qui est inadmissible.

Rassurez-vous, une telle aberration n'est pas non plus permise avec le MERGE :

SQL> MERGE INTO acct a
2 USING ecr b
3 ON (a.acct_num = b.acct_num)
4 WHEN MATCHED THEN UPDATE SET a.some_amount = b.ecr_amt;
USING ecr b
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables

Jusque là, pas d'avantage concret pour le MERGE.

Sauf que la définition d'une key-preserved table est assez stricte !

Je trouve la doc Oracle un peu floue sur ce point, mais on note quand même :

The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the emp table there was at most one employee in each department, then deptno would be unique in the result of a join of emp and dept, but dept would still not be a key-preserved table.

Par contre, l'Oncle Tom est un peu plus précis sur asktom :
You NEED a primary key/unique constraint on
object_id in T1 to ensure that each row in T joins to AT MOST 1 row in T1.

Et là, on a un problème : avoir besoin d'une contrainte d'unicité, ça limite les possibilités.
Par exemple, il est assez courant de vouloir mettre à jour une table avec un résultat d'aggrégation d'une autre table !

Voyons voir ce que ça donne...

4) After all, MERGE is good for you !

Supposons à présent que l'on veuille affecter pour chaque compte de la table acct, la somme des montants associés dans la table ecr :

SQL> UPDATE
2    (SELECT a.some_amount, b.amt
3     FROM acct a
4       JOIN (SELECT acct_num, sum(ecr_amt) amt
5             FROM ecr
6             GROUP BY acct_num) b ON a.acct_num = b.acct_num
7    )
8 SET some_amount= amt;
SET some_amount= amt
    *
ERROR at line 8:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Non key-preserved !

Et pourtant, en faisant la jointure avec un GROUP BY, on est sûr de n'avoir qu'une ligne !

Par contre avec le MERGE :

SQL> MERGE INTO acct a
2 USING (SELECT acct_num, sum(ecr_amt) sumecr
3 FROM ecr
4 GROUP BY acct_num) b
5 ON (a.acct_num = b.acct_num)
6 WHEN MATCHED THEN UPDATE SET a.some_amount = b.sumecr;

100 rows merged.

Ca marche !

Un grand merci quoi qu'il en soit à Mnitu de m'emêcher de dire des conneries impunément...

Partager cet article

Repost0

Published by Pacman - dans SQL
commenter cet article …

27 novembre 2009 5 27 /11 /novembre /2009 10:14

Bind variables and skewed data distribution

Heute handelt es sich um die Bind-Variablen.
Ob PL/SQL, Java, oder irgend welche Programmierungsprache gilt das selbe Motto : Bind-Variablen nutzen, oder den Zorn des AmokDBAs erleiden...
Aber wieso ? Und was zum Teufel sind dann diese "bind variable peeking" und "shared cursor" ?

Dieser Artikel bietet eine illustrierte Zusammefassung :
1) Frühere Ausführungspläne verwenden
2) Erstellung des Ausführungsplans für Unregelmässige Aufteilungen
3) Wie man die (wieder)Erstellung des Ausführungsplan zwingt

1) Frühere Ausführungspläne verwenden

Bevor die Ausgeführung eines SQL-Kommandos, wird es von Oracle geparst.
Da wird unter anderen der Ausführungsplan erstellt : Oracle entscheidet welches Index, welches JOIN Verfahren gebraucht wird.
Das ganze kostet selbstverständlich Zeit. Zwar gering wenn es sich um eine lange Anfrag handelt, aber dagegen schon ziemlich bedeutend für kurze Anfrage (zum Beispiel in OLTP Anwendungen).
In solche Anwendungen werden meistens die selben Queries ausgeführt : die Daten des Kunden X, Y, Z, der heutige, gestrige Umsatz, ... den Ausführungsplan jedes Mal zu erstellen wäre reine verschwendung !

Deshalb werden die Abfragen und die entsprechenden Ausführungspläne von Oracle im Library Cache gespeichert.
Diese Pläne können wiedergebraucht werden, NUR wenn die SQLString ganz genau einer in der Library Cache gespeicherten Anfrage gleicht. (Ausser mit den cursor_sharing optionen, die wir im dritten Teil besprechen werden)

Deshalb sollen Bindvariablen benutzt werden : die SQL Strings im Library Cache sollen unabhängig von der Literalen Werten sein.

Falls ihr glaubt, es geht nur um geringe Steigerungen, messen wir jetzt den Unterschied mit einem kleinen Beispiel :

Die Tabelle der 1000000 erste Zahlen, ein Index, und die Statistiken :

CREATE TABLE TEST_LIB_CACHE AS
SELECT level AS n FROM DUAL
CONNECT BY LEVEL <= 1000000
/

CREATE INDEX TEST_LIB_CACHE_I1 ON TEST_LIB_CACHE(n)
/

exec dbms_stats.gather_table_stats('PACMAN', 'TEST_LIB_CACHE', cascade => true)

Der Test besteht aus einem einfachen Durchlauf der Tabellezeilen (ohne Cursor), mit oder ohne Bindvariablen.

Erste Variante (MIT) :

SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
i number;
j number;
BEGIN
FOR i IN 1..1000000
LOOP
SELECT n INTO j FROM TEST_LIB_CACHE WHERE n = i;
END LOOP;
dbms_output.put_line('Fertig !');
END;
/

Fertig !

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.90

Zweite Variante (MIT) :

ALTER SYSTEM FLUSH BUFFER_CACHE;
DECLARE
i number;
j number;
BEGIN
FOR i IN 1..1000000
LOOP
EXECUTE IMMEDIATE 'SELECT n INTO :j FROM TEST_LIB_CACHE WHERE n =' || to_char(i);
END LOOP;
dbms_output.put_line('Fertig !' || j);
END;
/

Fertig !

PL/SQL procedure successfully completed.

Elapsed: 00:07:39.41

Überzeugt ?

2) Wir haben jetzt ein Paar Ausführungspläne, die für die ganze Anwendungen reichen.

Aber ist der Bestmögliche Ausführungsplan wirklich unabhängisch der Literalen ?
Die antwort lautet : selbstverständlich nein !

Die Rechnungen des CBO (Cost Based Optimizer) sind extrem Kompliziert. Trotzdem werden wir hier versuchen zu verdeutlichen, warum die Selectivität eines Prädikates so wichtig ist.

Ihr Aufgabe lautet : "Alle Sätze abschreiben, die ein Stichwort enthält, das mit M beginnt."
Dafür gibt es den Index !
- Erstens die Buchstabe M im Index suchen
- Für jedes Wort, die Liste der Seiten durchgehen
- Jede Seite öffnen, die Sätze finden und abschreiben

Wieviele Seiten werdet ihr öffnen müssen ?
Falls es zu Viele davon gibt, ist es nünftiger das Buch direkt durchzublättern !

Die Selectivität einer Spalte wäre hier der durchschnittliche Zahl der Erscheinungen der Stichwörter.
Aber diese statistische Eingabe konnte leider nicht ausreichen, weil die Aufteilung unregelmässig ist : manche Stichwörter werden viel mehr gebraucht als andere...
Deswegen wurden die Histograms eingeführt.

Prüfen wir mal nach ob's klappt !

Eine tabelle mit verzerrten Einteilung (die Gruppe 1 besteht aus einem Mitglied, während die Anderen der 0 Gruppe gehören) :

CREATE TABLE test_histo AS
SELECT level AS id, CASE level WHEN 1 THEN 1 ELSE 0 END as grp
FROM DUAL
CONNECT BY level <= 1000000
/
CREATE INDEX test_histo_i ON test_histo(grp)
/

exec dbms_stats.gather_table_stats('PACMAN', 'TEST_HISTO', cascade => true, method_opt => 'FOR COLUMNS GRP 2 SKEWONLY')

Und jetzt die Ausführungspläne für beide Gruppen :

SET AUTOT TRACEONLY EXPLAIN
SELECT *
FROM test_histo
WHERE grp = 0
/

--------------------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            | 1003K| 5880K|   512 (10)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| TEST_HISTO | 1003K| 5880K|   512 (10)| 00:00:07 |
--------------------------------------------------------------------------------

SELECT *
FROM test_histo
WHERE grp = 1
/

--------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     6 |     4|
|   1 | TABLE ACCESS BY INDEX ROWID| TEST_HISTO   |     1 |     6 |     4|
|* 2 |   INDEX RANGE SCAN          | TEST_HISTO_I |     1 |       |     3|
---------------------------------------------------------------------------

Perfekter kann es nicht sein.

Aber kommen wir zum Hauptthema : wenn wir die Gruppenummer hinter der Bindevariable verstecken, wie soll der CBO die Gruppe erkennen (beziehungsweise den richtigen Ausführungsplan) ?

3) Eine Menge von Schlechten Lösungen...

a. Bind Variable Peeking
Seit Oracle 9i gibt es die "Bind variable peeking".
Das heisst, dass Oracle die Literalen prüft, bevor er den Plan erstellt, egal ob Bindevariablen oder nicht.
Hört sich gut an... aber dass gilt nur wenn die Anfrage zum ersten Mal geparst wird !

Und deswegen hassen viele DBA / Entwickler die Histograms : der erstgeparste plan wird dann für alle Anfragen benutzt.

b. Cursor sharing
Eigentlich ist es die Gegenproblematik.
Falls die Literalen "hardcoded" sind, kann der in der 8i version eingeführte cursor_sharing Parameter den CBO dazu bringen, diese Literalen mit Bindevariablen ersetzen. Genial ? Nein !

So einfach ist aber nicht...

Drei verschiedene Werten für diesen Parameter :
- EXACT : kein Ersetzen
Das vorgegebene Wert.

- FORCE : systematisches Ersetzen
Nein, um Gottes Willen ! Wir haben doch gerade erklärt, warum wir uns nicht leisten können, den gleichen Ausführungsplan zu nutzen...

- SIMILAR : Ersetzen, ausser wenn der Ausführungsplan sich ändern könnte
Schon besser. Aber falls es Histogramme gibt, wird jedes Mal einen neuen Ausführungsplan gebaut (und beziehungsweise die Anfrage hardparsed).

Dieser Parameter ist desshalb hauptsächlich hilfreich wenn man kein Zugriff zu den Abfragen hat (zum Beispiel in einem Progiciel).
Aber ansonsten entscheidet ihr am besten allein : Bindvariablen im Allgemeinen, hardcodierte literalen wenn unregelmässig aufgeteilte spalten benutzt werden.

c. Yes we can !
Aber bitte nicht verzweifeln : mit Oracle 11g kommt der Adaptative Cursor !
Die sensiblen (das heisst mit Bindvariablen auf die histograms-Spalten) Abfragen werden markiert, duppliziert für jedes Bucket des Histograms.

Oracle bringt Hoffnung für die Menscheit, aber bringt die 11g Migration zu Ende bevor 2012 ;)

Partager cet article

Repost0

Published by Pacman - dans SGBD
commenter cet article …

30 octobre 2009 5 30 /10 /octobre /2009 11:04

LEFT OUTER JOIN and single table predicate

La question du jour :
Est-il équivalent de placer son prédicat (mono table) de jointure externe dans la clause LEFT OUTER JOIN, ou dans le WHERE en permettant les NULL ?
(En fait, c'est même la question du mois vu que je n'ai pas été capable de finir un article depuis longtemps...)

Ben non !

Et il y a plusieurs raisons, que nous illustrerons par l'exemple.

Dans la suite de l'article, il y aura :

0) Mise en jambes, jeux de tests

1) Si la colonne que vous voulez filtrer sur le résultat est nullable (peut prendre la "valeur" NULL), alors il peut y avoir confusion entre le NULL de "il n'y a pas de lignes qui matchent la correspondance" et "la colonne de la ligne répondant au critère de jointure est NULL"

2) Si une et une seule ligne de la table de droite (dans une jointure LEFT) répond au critère de jointure mais prend une valeur non NULL pour la colonne filtrée : 0 lignes renvoyées au lieu d'une !

3) Ca n'a rien à voir, mais un petit plébiscite pour la syntaxe ANSI contre la version dépréciée Oracle

0) C'est parti !

Les jeux de tests : une table maître, une table détail. Une seule ligne de détail pour Marcel, 3 lignes de détail pour Robert, 0 pour Squelettor.

CREATE TABLE TEST_LJ_MAITRE AS
SELECT 1 AS idm, 'Robert' as nom FROM DUAL UNION ALL
SELECT 2 AS idm, 'Marcel' as nom FROM DUAL UNION ALL
SELECT 3 AS idm, 'Squelettor' as nom FROM DUAL;

CREATE TABLE TEST_LJ_DETAIL AS
SELECT 1 AS ids, 1 as idm, 'M' as s FROM DUAL UNION ALL
SELECT 2 AS ids, 1 as idm, 'F' as s FROM DUAL UNION ALL
SELECT 3 AS ids, 1 as idm, NULL as s FROM DUAL UNION ALL
SELECT 4 AS ids, 2 as idm, 'F' as s FROM DUAL;

La jointure externe, c'est comme une jointure normale, sauf que les lignes de gauche (pour LEFT, et droite pour RIGHT...) sont dans le résultat même si aucune ligne de la table de droite ne correspond au critères spécifiés dans la clause "ON".

SQL> SELECT *
2   FROM TEST_LJ_MAITRE a
3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4   /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              2          1 F
         1 Robert              3          1
         2 Marcel              4          2 F
         3 Squelettor

Au passage, on rappelle que :
- INNER JOIN est strictement équivalent à JOIN
- LEFT OUTER JOIN est strictement équivalent à LEFT JOIN

Personnellement, j'utilise JOIN et LEFT OUTER JOIN. Non pas par paresse, mais parce que dans la théorie des ensembles, une "relation" est naturellement "INNER"... alors que la jointure externe est plus difficile à rapprocher d'un concept mathématique simple.
Ainsi, je trouve que LEFT JOIN peut donner l'illusion pour les débutants que l'ordre des tables dans une jointure "INNER" à une signification, alors que ce n'est bien évidemment pas le cas. C'est pour cela que je précise le OUTER.

Après, c'est une histoire de goûts...

1) Il y a NULL et NULL !

On veut à présent récupérer les relations des MAITREs avec des DETAILs masculins (DETAIL.s = 'M'), et ceux qui ne sont pas en relation avec des DETAILs masculins (qui du coup auront les colonnes DETAIL à NULL)

Première tentative :

SQL> SELECT *
2 FROM TEST_LJ_MAITRE a
3 LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4 WHERE b.s = 'M'
5 /

IDM NOM IDS IDM S
---------- ---------- ---------- ---------- -
1 Robert 1 1 M

Mon dieu, tout a disparu !
Ben oui, pour les MAITRE sans détail, le prédicat b.s = 'M' est faux !

Deuxième tentative :

SQL> SELECT *
2   FROM TEST_LJ_MAITRE a
3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4   WHERE b.s <> 'F'
5 /

IDM NOM IDS IDM S
---------- ---------- ---------- ---------- -
1 Robert 1 1 M

Ca ne marche pas mieux. Ben ouais, NULL, ce n'est pas vraiment une valeur.
Quand b.s IS NULL, la question b.s <> 'F' a pour réponse : indéterminé !

Troisième tentative :

SQL> SELECT *
2   FROM TEST_LJ_MAITRE a
3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4   WHERE b.s = 'M' OR b.s IS NULL
5 /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              3          1
         3 Squelettor

On a certes ramené Squelettor... mais on ramené une deuxième ligne pour Robert, avec une relation de s. indéterminé !

2) Mon dieu, ils ont encore tué ma jointure externe !

Quatrième tentative :
On se dit que ce qui caractérise le côté ouvert, c'est que l'identifiant de la table externe soit NOT NULL. Et donc, on va compléter la condition précédente :

SQL> SELECT *
2    FROM TEST_LJ_MAITRE a
3      LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4    WHERE b.s = 'M' OR (b.s IS NULL AND b.ids IS NULL)
5 /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         3 Squelettor

Presque !
Mais le pauvre Marcel est passé à la trappe. Et je trouve que c'est le plus gros piège, car on pense souvent à inclure le NULL dans la clause WHERE... mais c'est vraiment insuffisant.
Ici, Marcel est en relation avec un seul DETAIL, mais de s = F.
Et donc, il passe la jointure
"LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm"
Mais est refoulé dans le WHERE, quelle que soit la manière dont on le fasse !

Et du coup, quand un critère est vraiment "externe", il faut le mettre dans la clause ON qui est faite pour cela !

SQL> SELECT *
2   FROM TEST_LJ_MAITRE a
3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
4                      AND b.s = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Pas de discrimination, quelle que soit l'orientation !

3) Suivez la norme, tout le monde le dit !

Pour finir, la syntaxe "old school" :
Mettre un (+) dans les prédicats du WHERE.

SQL> SELECT *
2   FROM TEST_LJ_MAITRE a, TEST_LJ_DETAIL b
3   WHERE a.idm = b.idm(+)
4     AND b.s(+) = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Et effectivement, ça marche aussi.

Mais il faut rappeler que si cette syntaxe est disponible sous Oracle, ce n'est pas juste pour se démarquer des autres... mais simplement parce qu'elle a été mise en place avant la norme SQL 92 qui a standardisé les JOIN. (Attention, je n'ai pas dit légalisé, ni dépénalisé)

La preuve, c'est que même Oracle recommande la syntaxe normalisée :

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause join syntax:[...]

(Ok, c'est un peu agressif le rouge, mais c'est pour bien insister !)

Tiré d'ici :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625

Voilà, Voilà !

Partager cet article

Repost0

Published by Pacman - dans SQL
commenter cet article …

22 septembre 2009 2 22 /09 /septembre /2009 13:18

ORA-01555 from delayed block cleanout : let's do it !

Cet article s'adresse à ceux qui ont subi au moins une fois dans leur vie le "mystérieux" ORA-01555 snapshot too old, et à ceux que les histoires de lecture consistente font rigoler !

ORA-01555 est essentiellement due au mécanisme de lecture consistente (on dira aussi cohérente, vu que c'est l'idée) : cette erreur se déclenche principalement lorsqu'on essaie d'accéder à une version des données "trop vielle".

Cependant, il y a un cas un peu particulier de l'ORA-01555, liée à un mécanisme appelé le delayed block cleanout. Le mécanisme est sympa, poussé par une idée d'optimisation marrante. Cela dit, l'erreur qu'elle peut provoquee la rend quelque peu aberrante à mon goût.
Je me suis mis en tête de me construire un cas où l'erreur se produit. Et donc je vais en profiter pour parler un peu du sujet !

1) Le contexte : lecture consistente et rollback
2) COMMITer n'importe comment, c'est naze
3) Le match du jour : ORA-01555 1 - Delayed Block Cleanout 0

1) Le contexte : lecture consistente et rollback

a. Quand vous exécutez une requête assez longue par exemple, vous souhaiteriez quand même que les données lues en début et en fin de requête correspondent au même "état de l'univers", non ?
Par exemple : votre requête commence. Pendant ce temps, un autre utilisateur valide (COMMIT) un lot de modifications cohérentes.
Seulement voilà, même si ses modifications sont cohérentes, vous avez commencé à lire les données non modifiées, et vous voulez finir ainsi !
Il faut donc que le SGBD puisse garantir une image de l'univers à un instant donné.

b. Lorsque vous modifiez les données à travers une transaction, vous voulez pouvoir les annuler. Pour ce faire, il faut sauvegarder les données avant modification.
Une fois validées (COMMIT), il ne semble plus nécessaire de garder la sauvegarde des anciennes données.

Tout le problème vient du fait que dans Oracle, un mécanisme commun assure les deux fonctionnalités : la gestion de l'UNDO !
La version courte (pour les mécanismes détaillés, vous trouverez sûrement de bons sites pour expliquer tout ça) :
- Lorsqu'un bloc de données est modifié, celui-ci est copié dans l'UNDO TABLESPACE (ou ROLLBACK SEGMENTS dans les anciennes versions d'Oracle)
- Lorsqu'une requête ayant débuté avant la transaction qui modifie ces données cherche à lire le bloc, elle est redirigée vers cet UNDO TABLESPACE, afin d'y trouver la version qui correspond à son contexte
- Cet UNDO TABLESPACE est un espace restreint, comprenant donc un nombre de "slots" limité. A chaque fois qu'une modification est COMMITée, le slot est libéré. Mais lorsque ce slot est réutilisé, l'information qui devait assurer la cohérence de la lecture des requêtes ayant débuté avant la dite transaction est perdue.
Et paf, ORA-01555 snapshot too old !

2) Le mythe

Certaines personnes font l'amalgame entre les deux fonctionnalités sus-citées... et recommandent donc d'augmenter le nombre de COMMIT !
Par exemple, au lieu de faire un update global en une requête, ils recommandent de parcourir la sélection dans un curseur, et COMMITer régulièrement.
Eh bien, par rapport à ce qu'on disait, ça donne l'effet inverse : vous COMMITez plus souvent, donc vous libérez plus rapidement vos sauvegardes qui servent à la cohérence de la lecture, qui peuvent donc être plus facilement écrasés.

C'est ce qu'on appelle le 'fetch across commit'.
Petit exemple :

On veut que le curseur parcourre la table dans un ordre différent de l'ordre physique. Ainsi, il est fort probable que le curseur cherche à fetcher un bloc déjà COMMITé.

SQL> create table test_1555 as
2 select * from (
3     select level as id, lpad('x', 250, 'x') as c
4     from dual
5     connect by level <= 10000)
6 order by dbms_random.random;

Table créée.

SQL> create index test_1555_i1 on test_1555(id);

Index créé.

Il faut obliger Oracle à réutiliser les slots d'UNDO, afin d'écraser les "sauvegardes des blocs". Pour cela, on prend un petit tablespace 500K.
Sincèrement, j'aurais aimé prendre plus petit... mais lorsque j'ai tenté le coup, aucun rollback segment ne se créait.

SQL> CREATE UNDO TABLESPACE smallundo datafile 'C:\ORACLEXE\ORADATA\XE\SMALLUNDO' SIZE 500K AUTOEXTEND OFF;

Tablespace créé.

SQL> ALTER SYSTEM SET undo_tablespace=SMALLUNDO;

L'objectif est d'obliger la requête à utiliser l'index. On pourrait forcer avec un hint. Ici, j'ai pompé l'exemple de l'Oncle Tom qui prend un prédicat bidon sur la colonne indexée. Mais pour que ça marche, faut quand même empêcher l'optimiseur d'échantilloner la table et de se rendre compte de la supercherie !

SQL> alter session set optimizer_dynamic_sampling = 0;

Session modifiée.

Et le résultat :

SQL> begin
2 for c in (select * from test_1555 where id > 0)
3 loop
4 update test_1555 set c = 'a' where id = c.id;
5 commit;
6 end loop;
7 end;
8 /
begin
*
ERREUR Ó la ligne 1 :
ORA-01555: clichés trop vieux : rollback segment no , nommé "", trop petit
ORA-06512: ligne 2

3) La spéciale Delayed Block Cleanout !

Ici, la cause de l'erreur est différente. Pour cela, une rapide explication s'impose.
Lorsqu'on lit les blocs de la table, il faut pouvoir déterminer si celui-ci est en cours de modification (non COMMITée) ou non.
Et dans le premier cas, il faut pouvoir retrouver la version précédente du bloc.
Pour cela, l'entête du bloc contient un pointeur vers l'entrée d'UNDO.
Lors du commit, il faut nettoyer le bloc de toutes ces informations, et apposer le numéro de version du bloc (COMMIT SCN).

D'autre part, comme nous le disions en 2), il faut pouvoir déterminer si un bloc de l'UNDO est commité ou non, afin de libérer le slot pour les transactions suivantes. Ainsi, il y a également un flag au niveau de l'entrée d'UNDO indiquant s'il est COMMITé ou non, ainsi que le timestamp du COMMIT.

Et voilà l'astuce :
Si la mise à jour a été longue, et que le bloc de données n'est plus présent dans le buffer cache, il faudrait aller rechercher le bloc sur le disque pour le nettoyer. Mais quand on y réfléchit, comme l'info est redondante entre le bloc et l'UNDO slot, on peut imaginer que la requête lisant le bloc sera redirigée vers l'UNDO, où elle s'apercevra que finalement celui-ci était bel et bien COMMITé.
C'est le principe du delayed block cleanout : utiliser la redondance de l'information de COMMIT pour s'épargner des accès disque et laisser le soin à la prochaine requête de faire le nettoyage.

Mais que se passe-t-il lorsque le slot a été réutilisé ?
Par chance, le système ne claque pas tout de suite. Si le slot d'UNDO a été réutilisé, on peut sensément conclure que la transaction s'est terminée.
Ensuite, il s'agit surtout de déterminer si le COMMIT SCN du bloc à lire est inférieur ou non au SCN courant de la requête.
Ainsi, on recherche le plus ancien SCN dans l'UNDO, qui sera sensément plus récent que celui du bloc cherché : en effet, le mécanisme d'allocation commence par réutiliser les plus vieux (le monde est injuste, disons le).
Mais si ce SCN "borne supérieur" (upper bound SCN en VO) est plus récent que le SCN courant de la requête (snapshot SCN), re-paf : snapshot too old.

Venons en à l'exemple.

Objectif :
- COMMITer des mises à jour assez volumineuses pour que les blocs modifiés ne soient plus dans le buffer cache.
- Commencer une longue requête de lecture
- En parallèle, réutiliser l'intégralité de l'undo, afin que le plus vieil SCN de l'UNDO soit supérieur à celui de la requête.

La table principale qui subira le delayed block cleanout :

create table test2_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 100000;
create index test2_1555_i1 on test2_1555(id);

Les deux tables, dont la mise à jour réaffectera l'ensemble de l'UNDO :

create table test3_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 30000;

create table test4_1555 as
   select level as id, lpad('x', 250, 'x') as c
   from dual
   connect by level <= 30000;

On réduit la taille du buffer cache :

alter system set sga_target=0;
alter system set db_cache_size=8M;

Un UNDO tablespace special pour l'occasion (assez nettement plus gros que le buffer cache pour pouvoir en faire plusieurs fois le tour avant commit, et assez petit pour pouvoir recycler tout l'UNDO sans trop d'efforts...)

CREATE UNDO TABLESPACE smallundo2 datafile 'C:\ORACLEXE\ORADATA\XE\SMALLUNDO2.DBF' size 16M AUTOEXTEND OFF;
alter system set undo_tablespace=smallundo2

C'est parti pour préparer le delayed block cleanout :

update test2_1555 set c = 'a' where id <= 35000;
commit;
update test2_1555 set c = 'a' where id between 35000 and 70000;
commit;
update test2_1555 set c = 'a' where id between 70000 and 100000;
commit;
set autotrace traceonly statistics

Il faut maintenant une requête assez longue pour pouvoir lancer tranquilement les UPDATE parallèles.
On force la matérialisation des cross joins pour être sûr que test2_1555 ne sera pas accédé en premier :

with t as (select /*+materialize*/ count(*) cnt from dba_objects a cross join dba_objects b cross join dba_objects c)
select * from test2_1555 a cross join t

Et pendant ce temps, sur une autre session, la réallocation de l'undo sur des tables totalement indépendantes :

UPDATE test3_1555 set c = 'a';
COMMIT;

UPDATE test4_1555 set c = 'a';
COMMIT;

Et au bout de 9 heures (j'ai peut être un peu bourriné en faisant trois cross joins...) sur la première session, la récompense :

ERREUR Ó la ligne 1 :
ORA-01555: clichés trop vieux : rollback segment no 14, nommé "_SYSSMU14$", trop petit

Ecoulé : 09 :10 :43.06

Ah, ce qu'on se marre...

Partager cet article

Repost0

Published by Pacman - dans SGBD
commenter cet article …

1 septembre 2009 2 01 /09 /septembre /2009 14:17

We do not need this CASE instruction !

Je ne sais pas trop pourquoi, mais je me sens toujours très fier quand j'arrive à éliminer tous mes CASE ... WHEN ... THEN ... END de mes expressions.
Surtout quand mon expression n'implique que des entiers relatifs !
Pour les petits malins, il ne s'agit pas de les remplacer par des DECODE, mais de se restreindre aux opérateurs "arithmétiques" usuels :
+, -, *, /, mod, partie entière, valeur absolue
Bien entendu, on voit tout de suite que "partie entière" et "valeur absolue" contiennent un CASE ... WHEN .. END, en quelques sortes : et c'est bien ce qu'on va tenter d'exploiter...
Avant de commencer, réglons la question de l'utilité : aucune. J'irais même plus loin :
N'APPLIQUEZ PAS CES METHODES AU TRAVAIL, VOUS SERIEZ LICENCIES POUR FAUTE PROFESSIONNELLE !

Afin d'arriver à une méthode générique de transformation du CASE, nous suivrons les étapes suivantes :
0) Périmètre et jeux de données
1) Le sélecteur simple : CASE x WHEN a THEN b ELSE 0 END
2) La condition non exacte : CASE WHEN x >= a THEN y ELSE 0 END
3) Composition des conditions unitaires : CASE WHEN x > a AND y > b THEN z ELSE 0 END
4) Branchement de diverses conditions : CASE WHEN Cond1 THEN y WHEN Cond2 THEN z ... END

0) Périmètre et jeux de données
Les valeurs "élémentaires" sont entières, éventuellement négatives
Les opérateurs permis autorisés sont : +, -, *, /, mod (modulo), trunc noté aussi [x], abs noté aussi |x|)
Les expressions de CASE que l'on transformera seront :
>=, <=, =, BETWEEN, IN, NOT IN. Avec les connecteurs AND, OR et NOT

Pour les jeux de données, nous créons la table contenant toutes les quadruplets formés de nombres entiers dans l'intervalle [-10, 10]
CREATE TABLE ME_MANQUE_UNE_CASE AS
WITH t AS (
SELECT 10 - level + 1 as nb FROM DUAL
CONNECT BY level <= 21)
select a.nb as a, b.nb as b, c.nb as c, d.nb as d
FROM t a
CROSS JOIN t b
CROSS JOIN t c
CROSS JOIN t d

1) Le sélecteur simple : CASE x WHEN a THEN b ELSE 0 END
C'est la base de tout !
Comment distinguer la valeur "a" d'une variable "x" parmi toutes les autres valeurs ?
D'instinct, on remarque que l'on peut généraliser le problème par la translation :
x -> x - a
... ce qui revient à poser la question : quelle expression renvoit 1 quand x vaut 0, 0 sinon ?
(soit CASE x WHEN 0 THEN 1 ELSE 0 END)

La première idée, c'est de se dire que 1 / x est strictement compris entre 0 et 1, et on aimerait en prendre la partie entière supérieure.
Mais la division par 0, c'est généralement interdit.

Tout en restant dans cet esprit (ramener sur l'intervalle et prendre la partie entière pour générer une "discontinuité" en 0), on a envie de tenter :
x / (x + 1)

Car on cherche l'expression qui ne dépend que de x est qui lui est strictement supérieure. Cela ne marche pas très bien pour les nombres négatifs...
Et on en vient tout naturellement à :
f(x) = [1 - |x| / (|x| + 1)], qui renvoit 1 quand x vaut 0, 0 sinon

Et donc on réécrit :
f(x) = CASE x WHEN a THEN b ELSE 0 END
En :
f(x) = [1 - |x - a| / (|x - a| + 1)] * b

Petit test sur la table :
SQL> SELECT trunc(1 - abs(a - 5) / (abs(a - 5) + 1)) * 2 as nw,
2     CASE a WHEN 5 THEN 2 ELSE 0 END as old,
3     a.a
4 FROM me_manque_une_case a
5 WHERE a = 5 and b = 1 and c = 3 and d = 4
6 /

        NW        OLD          A
---------- ---------- ----------
         2          2          5

2) La condition non exacte : CASE WHEN x >= a THEN y ELSE 0 END

A partir de maintenant, nous allons principalement nous ramener au cas basique étudié en 1)
La fonction créée se décompose tout naturellement en une fonction de sélection qui renvoit 1 ou 0, et une constante multiplicative.
Toujours la même démarche : essayer de ramener au cas le plus général en composant / substituant / translatant... puis résoudre !
x > a <=> x - a >= 0
Comment caractérise-t-on les nombres positifs ?
Ce sont tout ceux qui sont égaux à leur valeur absolue !
x >= 0 <=> x - |x| = 0

D'où la transformation finale :
f(x) = CASE WHEN x >= a THEN y ELSE 0 END
f(x) = x - a - |x-a|
f(x) = y * [1 - |x - a - |x-a|| / (|x - a - |x-a|| + 1)]

Petit exemple :
SQL> select * from (
2 select 5 * trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1)) as reecr,
3 CASE WHEN a >= 2 THEN 5 ELSE 0 END as orig
4 , a.*, row_number() over (partition by a order by null) rk
5 from me_manque_une_case a
6 where a in (1, 3, -1)
7 )
8 where rk = 1
9 /

     REECR       ORIG          A          B          C          D         RK
---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          0         -1         10         10         10          1
         0          0          1         10         10         10          1
         5          5          3         10         10         10          1

Nous savons maintenant écrire pour une condition élémentaire la fonction de sélection f :
f(x) = 1 si cond
       0 sinon
Tout naturellement, la fonction g associée à NOT(cond) est :
g(x) = 1 - f(x)
(Je ne vous fais pas l'affront de vous expliquer pourquoi)

3) Composition des conditions unitaires : CASE WHEN x > a AND y > b THEN z ELSE 0 END

L'ensemble des conditions logiques, prédicats est "structuré", "algébrique".
(Vous noterez que j'utilise tout le temps des doubles quotes pour pallier mon manque de précision dans le vocabulaire :))
Je veux dire par là que la composition de prédicats par l'intermédiaire d'opérateurs logiques constitue également des prédicats, et que réciproquement, d'une certaine manière, tout prédicat peut être décomposé en prédicats simples reliés par ces opérateurs.
Ansi, si nous arrivons maintenant à formaliser ces opérations (c'est à dire écrire leur fonction Fop(c1, c2), nous pourrons considérer que l'objectif est atteint.

Soit f(x) et g(x) deux fonctions de sélection, telles que :
f1(x) = f(x) * y = CASE WHEN cond1(x) THEN y ELSE 0 END
g1(x) = g(x) * z = CASE WHEN cond2(x) THEN z ELSE 0 END

La fonction h de sélection pour les opérattions logiques basiques :
cond1 OR cond2 : h(x) = f(x) + g(x) - f(x) * g(x)
cond1 AND cond2 : h(x) = f(x) * g(x)
NOT cond1 : h(x) = 1 - f(x)

On peut étendre a présent les tests de base à IN, NOT IN et BETWEEN :
x IN (a1, a2, ..., an) <=> x = a1 OR x = a2 OR ...
x BETWEEN a AND b <=> x >= a AND x <= b

Allez, on applique tout ça !

select * from (
select trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1)) *
(trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)) - trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))) *
(1 - trunc(1 - abs(d - 5) / (abs(d - 5) + 1))) * 5 as reecr,
CASE WHEN a >= 2 AND (b = 6 OR c = -1) AND d <> 5 THEN 5 ELSE 0 END as orig,
a.*
from me_manque_une_case a
)
where reecr <> orig
/

Aucun résultat : les deux écritures sont strictement équivalentes.

4) Branchement de diverses conditions : CASE WHEN Cond1 THEN y WHEN Cond2 THEN z ... END

Jusqu'à présent, nous n'avons réalisé qu'un seul branchement.
Allez, un dernier petit effort !
CASE WHEN cond1 THEN a WHEN cond2 THEN b ELSE c END
Pour implémenter une succession de branchements, nous devons retranscrire de la manière suivante :
Soient f et g les fonctions de sélectios de cond1 et cond2.
cond1
NOT cond1 AND cond2
NOT cond1 AND not cond2
Donc :
f(x) * a + (1 - f(x)) * g(x) * b + (1 - f(x)) * (1 - g(x)) * c

Et donc, la dernière illustration :
SQL> select * from (
2 select trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))
3 +
4 (1 - trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))) *
5 (trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))
6      - trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1))) * 2
7 +
8 (1 - trunc(1 - abs(a - 2 - abs(a-2)) / (abs(a - 2 - abs(a-2)) + 1))) *
9 (1-(trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) + trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)) -
10        trunc(1 - abs(b - 6) / (abs(b - 6) + 1)) * trunc(1 - abs(c + 1) / (abs(c + 1 ) + 1)))) * 3
11 as reecr,
12 CASE WHEN a >= 2 THEN 1 WHEN (b = 6 OR c = -1) THEN 2 ELSE 3 END as orig,
13 a.*
14 from me_manque_une_case a
15 order by dbms_random.random
16 )
17 --where reecr <> orig
18 where rownum <= 20;

     REECR       ORIG          A          B          C          D
---------- ---------- ---------- ---------- ---------- ----------
         2          2         -7         -8         -1         -5
         1          1          9          4         -6          7
         1          1          8          7          7         -2
         3          3          0         -5         -7          1
         3          3         -2         -5          6         -4
         3          3         -8          5         -4         -6
         1          1          9         -7         -6         -8
         3          3          1        -10          6         -2
         3          3         -4         -8         -2         -9
         1          1          7         10         -9         -5
         1          1         10          6         -6         -3
         3          3         -7          3         -8         -4
         1          1          7          9          3         -8
         3          3          1          5         -2          2
         3          3         -8          8          2          1
         3          3         -1          3         -4          1
         3          3         -3         -2          5         -2
         3          3          0         -7          4          8
         1          1          2         -8          6         -8
         3          3         -3        -10        -10         -6

20 ligne(s) sÚlectionnÚe(s).

=> Sous performant, absolument illisible, inutile... Vive le Cosmetic SQL !

Partager cet article

Repost0

Published by Pacman - dans SQL cosmétique
commenter cet article …

18 août 2009 2 18 /08 /août /2009 16:19

Oracle CBO : Group by / Left Outer Join Cardinality

Suppose you simply LEFT OUTER JOIN a master table to its detail table, and then aggregate the result on the master's primary key.
You would expect the output cardinality to be exactly the number of lines in the master table, wouldn't you ?
Of course, the result of such a query in Oracle 9iR2 (and i hope in the other versions too) is correct... but the cardinality estimated by the CBO is not !
In fact, it is not the only strange behaviour : if you materialize the query using the WITH clause, you get two different computations of the cardinality.
This article's aim is to illustrate it (without solving the CBO's mysteries...)

First create a master table as the 10 first integers, define the primary key, and compute the statistics.
CREATE TABLE tmp1 AS
SELECT level as id
FROM DUAL
CONNECT BY level <= 10
/
ALTER TABLE tmp1 ADD CONSTRAINT tmp1_pk PRIMARY KEY (id);

exec dbms_stats.gather_table_stats(ownname => 'PACMAN', tabname => 'TMP1', cascade => true)

Then, create detail table as the association of 10 numbers to each of the 10 lines of the master table, define PK and FK :
CREATE TABLE tmp2 AS
SELECT (a.id - 1) * 10 + b.id as id, a.id as fk
FROM tmp1 a CROSS JOIN tmp1 b
/

ALTER TABLE tmp2 ADD CONSTRAINT tmp2_pk PRIMARY KEY (id)
/

ALTER TABLE tmp2 ADD CONSTRAINT tmp2_ifk FOREIGN KEY (fk) REFERENCES tmp1(id)
/

Reassign some lines to another master line and compute statistics :
UPDATE tmp2
SET fk = 10
WHERE fk BETWEEN 7 AND 9;

exec dbms_stats.gather_table_stats(ownname => 'PACMAN', tabname => 'TMP2', cascade => true)

Now explain the query that counts for each master line (in tmp1) the associated detail lines (in tmp2) :

EXPLAIN PLAN FOR
SELECT a.id, count(b.fk) as cnt
FROM tmp1 a
LEFT OUTER JOIN tmp2 b ON a.id = b.fk
GROUP BY a.id
/

SELECT * FROM TABLE(dbms_xplan.display)
/
--------------------------------------------------------------------
| Id | Operation            | Name       | Rows | Bytes | Cost |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     7 |    42 |     4 |
|   1 | SORT GROUP BY NOSORT|             |     7 |    42 |     4 |
|   2 |   MERGE JOIN OUTER   |             |   100 |   600 |     4 |
|   3 |    INDEX FULL SCAN   | TMP1_PK     |    10 |    30 |     1 |
|* 4 |    SORT JOIN         |             |   100 |   300 |     3 |
|   5 |     TABLE ACCESS FULL| TMP2        |   100 |   300 |     1 |
--------------------------------------------------------------------

=> The cardinality of tmp1 is estimated to 10 (which is the num_distinct value for the column ID in dba_tab_cols
=> The estimated cardinality of the whole query is 7, which is the number of distinct values of fk in tmp2
This result would have been accurate if we had performed an INNER JOIN, since only the matching entries would have been outputted...

I ran the 10053 event trace to analyze the CBO's computation... and as usual, i could not understand much of it !
Though, i noticed the following statement (that appears several times) in the Join sections :
"Grouping column cardinality [ ID] 7"

Is it a CBO "bug" ?
Anyway, i can understand that the cardinality of outer joins is not quite a simple matter...

At this step, i have to tell why and how i got into this :
In a rather big query, i had to count this kind of master / detail relation entries, and reuse the result several times.
That's why i factored the subquery using the WITH clause, in order to let the CBO materialize the result (and as a consequence compute it only once).
But as i displayed the EXPLAIN PLAN, the cardinalities computed for the materialized temporary table was not the same as the one computed for the query itself !

Fix little EXPLAIN PLAN on WITH clause bug :
alter session set events='22829 trace name context forever'

Check the previous predicate ! (I've used the undocumented /*+materialize*/ hint to force materialization rather than joining the result with itself for example...)
explain plan for
with t as (
select /*+materialize*/a.id, count(b.fk) as cnt
from tmp1 a
left outer join tmp2 b on a.id = b.fk
GROUP BY a.id
)
select * from t
/

----------------------------------------------------------------------------------
| Id | Operation                  | Name                        | Rows | Bytes
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                              |    10 |   260
|   2 | TEMP TABLE TRANSFORMATION |                              |       |
|   1 |   RECURSIVE EXECUTION      | SYS_LE_2_0                   |       |
|   0 |    INSERT STATEMENT        |                              |     7 |    42
|   1 |     LOAD AS SELECT         |                              |       |
|   2 |      SORT GROUP BY NOSORT |                              |     7 |    42
|   3 |       MERGE JOIN OUTER     |                              |   100 |   600
|   4 |        INDEX FULL SCAN     | TMP1_PK                      |    10 |    30
|* 5 |        SORT JOIN           |                              |   100 |   300
|   6 |         TABLE ACCESS FULL | TMP2                         |   100 |   300
|   3 |   VIEW                     |                              |    10 |   260
|   4 |    TABLE ACCESS FULL       | SYS_TEMP_0FD9D6644_24ADF79B |    10 |   160
----------------------------------------------------------------------------------

=> cardinality for the temp table transformation : 7
=> cardinality of SYS_TEMP_0FD9D6644_24ADF79B : 10

Once more (after flushing the shared pool), i generated the 10053 trace... and got nothing else headaches !
... and as usual i'll try to interprete it anyway :
for some obscure reason, the CBO seems to alias the TMP2 selection with from$subquery$_005.
After evaluation the NL join outer with the master table TMP1, it aliases the result as from$subquery$_003, and considers it as not analyzed.
And eventually outputs :
Grouping column cardinality [ID] 10 !

Ok, i admit it : i do not have the slightest clue of how i should read this d**n 10053...

(If you know anything about what i described, please leave a comment ;))

Partager cet article

Repost0

Published by Pacman - dans SGBD
commenter cet article …

14 août 2009 5 14 /08 /août /2009 16:58

String aggregation in Oracle : Emulate GROUP_CONCAT

The following article will be written in german.
Why ? No reason at all, i just hope that there are german people who are interested in SQL queries...

Na dann, los geht's.
Das heutige Thema handelt ein rekurrenter Bedürfnis :
Wie kann man Strings in einer grouppierung konkatinieren ?
Zum Beispliel, nehmen wir an, es gibt eine Tabelle der Fussballmannschaftenspieler : jede Zeile steht also für einen Spieler.
Team_mitglieder (Club, Spielername, Nummer)
Aus irgend einenem Grund, soll für jede Mannschaft eine einzige Zeile alle Namen der Mitglieder auslisten (getrennt mit komma zum Beispiel).

MySQL bietet die Funktion GROUP_CONCAT an. Und dadurch ist die Query ganz einfach :

SELECT Club, GROUP_CONCAT(Spielername, ',') ORDER BY Nummer
FROM Team_mitglieder
GROUP BY Club

MySQL ist leider das einzige DBMS, das diese Funktion direkt anbietet.
Im folgendem Artikel werden desshalb zwei verschiedene Wege (eigentlich gibt es noch einige anderen) präsentiert, das selbe in Oracle zu erreichen :
- durch die Verwendung der Baumstrukture Funktionen (CONNECT BY)
- mit den Oracle XML Funktionen

1) CONNECT BY Methode

Ihr wisst bestimmt schon, wofür mann dies gebrauchen kann :
Stellt euch vor, die Hierarchie in euren Firma wird von der Folgende Tabelle verkörpert :
CREATE TABLE Angestellte (
Id NUMBER,
Name VARCHAR(20),
LeiterId NUMBER) ;

-- die Oberleiterin
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (1, 'Angela', NULL);
-- Angela's erster Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (2, 'Franz', 1);
-- Angela's zweiter Sklave
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (3, 'Ulrich', 1);
-- Ulrich's Helfer
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (4, 'Klöden', 3);
-- Und heidi klum, nur so...
INSERT INTO Angestellte(Id, Name, LeiterId) VALUES (5, 'Heidi', 3);

Versuchen wir mal jetzt die ganze Hierarchie zu bilden :

SELECT path
FROM (
SELECT sys_connect_by_path(name, '->') AS path, CONNECT_BY_ISLEAF AS flg, CONNECT_BY_ROOT id rid
FROM Angestellte
CONNECT BY PRIOR id = leiterid
)
WHERE rid = 1

PATH
--------------------------------
->Angela
->Angela->Franz
->Angela->Ulrich
->Angela->Ulrich->Klöden
->Angela->Ulrich->Heidi

Jedes Wert wird rekursiv mit dem Vorgängern verknüpft, und die ganze Kette steht zur verfügung durch die Funktion SYS_CONNECT_BY_PATH.

Zurück zum Hauptthema : man braucht nur eine Einkantenbaumstruktur bestimmen, dessen Knoten die Spielernamen sind.
Erste Stufe : Nummerierung der Spieler
Zweite Stufe : Bildung des Baumes

CREATE TABLE test AS
SELECT   'Bayern' as team, 'Kahn' as name FROM DUAL UNION ALL
SELECT   'Bayern','Lucio'       FROM DUAL UNION ALL
SELECT   'Bayern','Traore'       FROM DUAL UNION ALL
SELECT   'Bayern','Philipp Lahm'       FROM DUAL UNION ALL
SELECT   'Bayern','Valerien Ismael'   FROM DUAL UNION ALL
SELECT   'Bayern','Michael Ballack'   FROM DUAL UNION ALL
SELECT   'Bayern','Jens Jeremies'   FROM DUAL UNION ALL
SELECT   'Bayern','Andreas Ottl'       FROM DUAL UNION ALL
SELECT   'Bayern','Johan Micoud'       FROM DUAL UNION ALL
SELECT   'Bayern','Ronaldo'       FROM DUAL UNION ALL
SELECT   'Bayern','Guerrero'       FROM DUAL UNION ALL
SELECT   'Bayern','Robbie Fowler'   FROM DUAL UNION ALL
SELECT   'Dortmund','Roman Weidenfeller'   FROM DUAL UNION ALL
SELECT   'Dortmund','Ashley Cole'   FROM DUAL UNION ALL
SELECT   'Dortmund','John Terry'       FROM DUAL UNION ALL
SELECT   'Dortmund','Ricardo Carvalho'   FROM DUAL UNION ALL
SELECT   'Dortmund','Phillip Lahm'   FROM DUAL UNION ALL
SELECT   'Dortmund','Sergio Ramos'   FROM DUAL UNION ALL
SELECT   'Dortmund','Patrick Vieira'   FROM DUAL UNION ALL
SELECT   'Dortmund','Bastian Schweinsteiger'   FROM DUAL UNION ALL
SELECT   'Dortmund','Steven Gerrard'   FROM DUAL UNION ALL
SELECT   'Dortmund','Cristiano Ronaldo'   FROM DUAL UNION ALL
SELECT   'Dortmund','Thierry Henry' FROM DUAL

SELECT team, ltrim(path, ',') as path
FROM (
   SELECT team, SYS_CONNECT_BY_PATH(name, ',') as path, CONNECT_BY_ISLEAF flg
   FROM (
           SELECT team, name, ROW_NUMBER() OVER(PARTITION BY team ORDER BY NULL) rk
           FROM test
   )
   CONNECT BY PRIOR rk = rk - 1 AND PRIOR team = team
   START WITH rk = 1
)
WHERE flg = 1

TEAM
--------
PATH
------------------------------------------------------------------------------------------------------------------------------------
Bayern
Kahn,Lucio,Robbie Fowler,Guerrero,Ronaldo,Johan Micoud,Andreas Ottl,Jens Jeremies,Michael Ballack,Valerien Ismael,Philipp Lahm,Traor
e

Dortmund
Cristiano Ronaldo,Steven Gerrard,Bastian Schweinsteiger,Patrick Vieira,Sergio Ramos,Thierry Henry,Ricardo Carvalho,John Terry,Ashley
Cole,Roman Weidenfeller,Phillip Lahm

- ROW_NUMBER() nummeriert die Zeilen in Jeder Gruppe definiert in der PARTITION BY Klause
- CONNECT_BY_LEAF ergibt 1, wenn das Wert ein Endknoten ist

2) XML Methode :

Seit Oracle 9i gibt es den Datentyp XMLTYpe und verschiedene passende Funktionen.
Unter anderen wertvollen Möglichkeiten, kann man :
- XML Elemente schaffen : XMLElem(Wert, Knotenname)
- Eine Gruppe XML Elemente aggregieren : XMLAgg(Element ORDER BY Reihenfolge)

Natürlich muss man danach die XML Markierungen reinigen... aber trotzdem bleibt das ganze ziemlich einfach :
SELECT team, RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", name )), '<x>', ' '), '</x>', ','), ',') as Agg
FROM test
GROUP BY team

TEAM
--------
AGG
------------------------------------------------------------------------------------------------------------------------------------
Bayern
Kahn, Lucio, Traore, Valerien Ismael, Jens Jeremies, Johan Micoud, Guerrero, Robbie Fowler, Ronaldo, Andreas Ottl, Michael Ballack,
Philipp Lahm

Dortmund
Roman Weidenfeller, Sergio Ramos, Bastian Schweinsteiger, Cristiano Ronaldo, Thierry Henry, Steven Gerrard, Patrick Vieira, Phillip
Lahm, Ashley Cole, John Terry, Ricardo Carvalho

Diese XML Methode finde ich intuitiver als die Baummethode.
Ausserdem mag sie aus dem sichtpunkt der Leistungen viel besser abschneiden, wenigstens bevor 10g.

Das war's !

Partager cet article

Repost0

Published by Pacman - dans SQL
commenter cet article …

13 août 2009 4 13 /08 /août /2009 22:57

Using SQL blog to improve my foreign languages

Je suis sur que pour les quelques personnes qui ont lu un des articles, une insulte a du leur venir spontannément à l'esprit :
Mais pourquoi les titres sont toujours en anglais, alors que le contenu est rédigé dans un français puisé des campagnes profondes ?

A vrai dire, je voulais vraiment écrire intégralement en anglais, mais je me suis aperçu que ma réserve de motivation ne suffirait pas à la fois pour me pencher sérieusement sur une problématique, et me cogner la tête contre les murs pour l'écrire dans une autre langue...

Mais je vais me reprendre.
Et pour faire crédible, je vais me fixer des objectifs :
- Un article en allemand au moins d'ici la fin du mois
- Un article en anglais au moins d'ici la mi-septembre
- D'ici la fin de l'année, j'apprends le japonais, et j'écris un article !
(faut peut être que je m'y mette tout de suite, en fait...)

Partager cet article

Repost0

Published by Pacman - dans Journal intime
commenter cet article …

12 août 2009 3 12 /08 /août /2009 10:33

SQL Geek's love declaration

Nous inaugurons aujourd'hui cette section, dont l'objectif est... hmmm... aucun.

Nous allons d'ailleurs le prouver tout de suite, car le sujet du jour est : dessiner un coeur avec une requête SQL.
C'est dédié à mon ami Vodiem, un grand spécialiste du cosmetic SQL.

Bien sûr, il serait possible de faire des select de caractères ligne par ligne... mais on va se compliquer un peu la tâche, car c'est aussi cela le cosmetic SQL.
Donc en généralisant, il s'agit de dessiner une courbe quelconque en une requête.

Pour cela, on commence par décomposer la courbe en portions de courbes représentant des fonctions usuelles (Droite, Cosinus, Sinus, ...)
Dans le cas précis du coeur, on peut considérer par exemple qu'il s'agit de deux arcs de sinus / cosinus, plus deux segments de droite.

Il faut donc formaliser la représentation d'une fonction y = f(x) dans un repère orthonormé. Une fois cela fait, il faudra coller ensemble les morceaux (plus facile à dire qu'à faire...)

1) Représentation d'une fonction
Faisons le parallèle avec notre repère Euclidien habituel :
La coordonnée "y" est gérée par le numéro de ligne décroissant.
La coordonnée x est gérée par le positionnement d'un caractère grâce à LPAD.

Cependant, une différence notable : on génère ligne par ligne les "x", donc l'équation entrée dans le LPAD correspond à :
x = f(y)

Exemple :

select x
from (
select lpad('o', 2 * level) as x, level as y
from dual
connect by level <= 10
)
order by y desc
/

X
-----------------------
                   o
                 o
               o
             o
           o
         o
       o
     o
   o
o

10 rows selected.

En somme, pour afficher la courbe correspond à l'équation y = f(x), il faut que f soit inversible.
Cependant, toutes les fonctions ne sont pas inversibles. Par contre, une grande partie d'entre elles sont décomposables en intervalles sur lesquels elles sont strictement monotones.
Par exemple, f: x-> sinus(x) entre [0, pi/2] et [pi/2, pi].

with p as (
            select 'o' as pt, 20 as sc from dual)
,intv as (
            select 1 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 2 as id, 5 as ymaxi, 4 as ymini from dual
            )
,intvsc as (
            select pt, sc, sc * ymaxi as ymax, sc * ymini as ymin, ymini, ymaxi, id
            from p cross join intv)
, intvm as (
            select max(ymax) as ymax from intvsc)
,t as (
select ymax - level as y --, ymax, level
from intvm
connect by level <= ymax
union all select ymax from intvm
)
, res1 as (
select case   when id = 1 then asin((y /*+ ymini-4*/- ymin)/sc) * sc
               when id = 2 then (3.141592 - asin((y - ymin)/sc)) * sc
      end as x
            , y
            , pt
            , id
from t join intvsc on t.y between intvsc.ymin and intvsc.ymax
)
, res2 as (
select x, y, coalesce(lag(x, 1) over(partition by y order by x, id), 0) as xprev, pt, id
, row_number() over(partition by y, x order by id) as dedoubl
from res1
)
select substr(REPLACE(REPLACE(XMLAgg(XMLElement("x", coalesce(lpad(pt, x - xprev), pt)) order by x),'</x>', ' '),'<x>',' '),2) AS x
from res2
where dedoubl = 1
group by y
order by y desc
;

X
------------------------------------------------------------------
                              o o
                        o             o
                     o                   o
                   o                       o
                 o                          o
               o                             o
              o                                o
             o                                   o
           o                                      o
          o                                        o
         o                                          o
        o                                             o
       o                                               o
      o                                                 o
     o                                                   o
    o                                                     o
   o                                                       o
o                                                         o
o                                                           o
o                                                             o
o                                                               o

21 rows selected.

Les sections de la requête traitent les problématiques rencontrées :
- intv : décrit les intervalles sur lesquels les fonctions sont définies
- intvsc : il s'agit de recadrer l'intervalle.
   D'une part, on lève la difficulté de gérer des coordonnées négatives (soit en abscisse, soit en ordonnée) en réalisant une translation des bornes.
   D'autre, on applique un facteur d'échelle sc(ale) (dans cet exemple 20) : du fait que la granularité des coordonnées est forcément de "1" (soit le numéro de ligne en ordonnée, soit le décalage "LPAD" en abscisse), il faut pouvoir "zoomer".
- t : on traduit l'union des intervalles de définition par la génération "à la volée" de lignes par CONNECT BY LEVEL
- res1 : pour chaque intervalle et chaque fonction s'appliquant dessus, on génère la coordonnée x = f(y) correspondante
   A noter l'utilisation de arcsin, la fonction réciproque de sinus, ainsi que la composition x = arc(g(y)), qui matérialise les translations et homothéties décrites plus haut
- On arrive aux parties finales, les plus sensibles : recoller les morceaux ! (res2 et SELECT final)
   Pour cela, il faut aggréger les lignes qui correspondent à une même ordonnée y. Plusieurs méthodes sont possibles.
   Ici, la solution retenue (Merci Waldar qui me l'a fait découvrir !), est celle de l'aggrégation XML : on représente d'abord les différentes valeurs sous forme de feuilles XML (XMLElement), puis on les concatène (XMLAgg), puis on nettoie les balises dans le résultat.
   Deux problèmes supplémentaires sont traités par l'intermédiaire de res2 (et des fonctions analytiques) : le fait qu'il s'agit plus d'une juxtaposition que d'une aggrégation, dans la mesure où il faut retrancher les LPAD de l'élément précédent ; Et le fait que si deux points sont "confondus", l'aggrégation simple les affichera tous les deux...

Maintenant qu'on sait comment ça marche, il ne reste plus qu'à tracer le coeur en entier !

with p as (
            select chr(3) as pt, 10 as sc from dual)
,intv as (
            select 1 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 2 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 3 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 4 as id, 5 as ymaxi, 4 as ymini from dual union all
            select 5 as id, 4 as ymaxi, 0 as ymini from dual union all
            select 6 as id, 4 as ymaxi, 0 as ymini from dual
            )
,intvsc as (
            select pt, sc, sc * ymaxi as ymax, sc * ymini as ymin, ymini, ymaxi, id
            from p cross join intv)
, intvm as (
            select max(ymax) as ymax from intvsc)
,t as (
select ymax - level as y
from intvm
connect by level <= ymax
union all select ymax from intvm
)
, res1 as (
select case    when id = 1 then asin((y - ymin)/sc) * sc
               when id = 2 then (3.141592 - asin((y - ymin)/sc)) * sc
               when id = 3 then (3.141592 + asin((y - ymin)/sc)) * sc
               when id = 4 then (6.283185 - asin((y - ymin)/sc)) * sc
               when id = 5 then (-3.141592 * y / 4 /sc + 3.141592 ) * sc
               when id = 6 then (3.141592 * y / 4 /sc + 3.141592 ) * sc
      end as x
            , y
            , pt
            , id
from t join intvsc on t.y between intvsc.ymin and intvsc.ymax
)
, res2 as (
select x, y, coalesce(lag(x, 1) over(partition by y order by x, id), 0) as xprev, pt, id
, row_number() over(partition by y, x order by id) as dedoubl
from res1
)
select substr(REPLACE(REPLACE(XMLAgg(XMLElement("x", coalesce(lpad(pt, x - xprev), pt)) order by x),'</x>', ' '),'<x>',' '),2) AS x
from res2
where dedoubl = 1
group by y
order by y desc

X
----------------------------------------------------------------------
              ♥ ♥                                ♥ ♥
          ♥          ♥                       ♥          ♥
        ♥             ♥                   ♥             ♥
      ♥                ♥                ♥                ♥
     ♥                   ♥             ♥                   ♥
    ♥                     ♥           ♥                     ♥
   ♥                        ♥         ♥                        ♥
♥                          ♥       ♥                          ♥
♥                            ♥     ♥                            ♥
♥                              ♥   ♥                              ♥
♥                                ♥                                ♥ ♥
♥                                                              ♥
♥                                                            ♥
♥                                                           ♥
♥                                                         ♥
♥                                                       ♥
   ♥                                                      ♥
    ♥                                                    ♥
     ♥                                                   ♥
      ♥                                                 ♥
      ♥                                                ♥
       ♥                                              ♥
        ♥                                            ♥
         ♥                                           ♥
         ♥                                         ♥
          ♥                                        ♥
           ♥                                      ♥
            ♥                                     ♥
             ♥                                   ♥
             ♥                                 ♥
              ♥                                ♥
               ♥                              ♥
                ♥                             ♥
                 ♥                           ♥
                 ♥                          ♥
                  ♥                        ♥
                   ♥                      ♥
                    ♥                     ♥
                    ♥                   ♥
                     ♥                  ♥
                      ♥                ♥
                       ♥               ♥
                        ♥             ♥
                        ♥           ♥
                         ♥          ♥
                          ♥        ♥
                           ♥       ♥
                            ♥     ♥
                            ♥    ♥
                             ♥ ♥
                              ♥

Voilà !
C'est très imparfait, mais ça s'explique :
- Il faut soigner les intersections entre les intervalles de définition
- Il faut ajuster la fonction d'aggrégation : on remarque en effet que plus on colle de morceaux, plus la courbe devient irrégulière.
En fait, lpad('o', f(y)) correspond à x = f(y) + 1/ sc...

Je vous laisse paufiner ces détails.
Quoi qu'il en soit, à présent, vous pourrez faire tomber le coeur de votre jolie DBA en lui envoyant cette requête !
(Petit conseil : faites un script qui balance la requête en boucle afin de faire clignoter ses écrans de surveillance. Au mieux, vous lancez en EXECUTE IMMEDIATE en faisant varier le paramètre sc pour faire craquer le shared pool :))

Partager cet article

Repost0

Published by Pacman - dans SQL cosmétique
commenter cet article …

3 août 2009 1 03 /08 /août /2009 14:34

Top queries, indexes and analytic functions

Rechercher, en ordonnant sur une colonne indexée, les N premières lignes... un problème classique, et facile à résoudre !
Parmi les méthodes classique, il y a la limitation par ROWNUM bien sûr, ou son pendant "SQL Normé" : les fonctions analytiques.

On va s'intéresser ici à la méthode analytique et des petites difficultés qu'on peut rencontrer :
1) Utilisation de l'index :
a. La contrainte NOT NULL
b. Comportements 9i / 10g
c. Les tris sur colonnes alphanumériques
2) Cardinalité du TOP

Pour commencer, on va se créer un petit jeu de tests basé sur dba_objects :

SQL> CREATE TABLE test_top AS
2 SELECT *
3 FROM dba_objects
4 WHERE object_id IS NOT NULL
5 /
Table créée.

SQL> CREATE INDEX test_top_ii ON test_top(object_id) COMPUTE STATISTICS
2 /
Index créé.

SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_TOP')
Procédure PL/SQL terminée avec succès.

1) a. Les valeurs NULL ne sont pas stockées dans l'index. Ainsi, lorsqu'on demande toutes les données ordonnées par une colonne définie en NULL, l'index ne peut être utilisé : (Illustration avec la méthode ROWNUM)

SQL> set autotrace traceonly explain
SQL> SELECT * FROM
2     (SELECT *
3     FROM test_top
4     ORDER BY object_id)
5 WHERE rownum <= 5
6 /

---------------------------------------------------------------------------------
| Id | Operation               | Name     | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     5 |   885 |       |   313   (2)|
|* 1 | COUNT STOPKEY          |          |       |       |       |            |
|   2 |   VIEW                  |          | 12879 | 2226K|       |   313   (2)|
|* 3 |    SORT ORDER BY STOPKEY|          | 12879 | 1081K| 2952K|   313   (2)|
|   4 |     TABLE ACCESS FULL   | TEST_TOP | 12879 | 1081K|       |    51   (2)|
---------------------------------------------------------------------------------

Donc le premier pré-requis pour l'utilisation de l'index : avoir au moins une colonne NOT NULL !

SQL> ALTER TABLE test_top MODIFY object_id NOT NULL;
Table modifiée.

SQL> SELECT * FROM
2     (SELECT *
3     FROM test_top
4     ORDER BY object_id)
5 WHERE rownum <= 5
6 /

----------------------------------------------------------------------------------
| Id | Operation                     | Name        | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     5 |   885 |     3   (0)|
|* 1 | COUNT STOPKEY                |             |       |       |            |
|   2 |   VIEW                        |             |     5 |   885 |     3   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TOP    | 12879 | 1081K|     3   (0)|
|   4 |     INDEX FULL SCAN           | TEST_TOP_II |     5 |       |     2   (0)|
----------------------------------------------------------------------------------

On note au passage que le comportement recherché est bien INDEX FULL SCAN + STOPKEY : on lit l'index en commençant par la première feuille, et on limite la recherche. Pour une recherche de borne sur une colonne indexée non null, on aura de la même manière INDEX FULL SCAN (MIN/MAX)...
Autre remarque : pour contourner cette histoire de contrainte NOT NULL, j'ai tenté le ORDER BY NULL LAST... en vain (ce qui se comprend un peu, car même si les NULL sont en derniers, on ne peut affirmer avant exécution qu'ils ne sont pas dans le périmètre de la requête !)

1)b.
A présent, le vif du sujet, qui constitua pour moi une des surprises agréables de la 10g.
Voyons le résultat du la version analytique :

SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM
4     (SELECT a.*, row_number() over(order by object_id) rk
5     FROM test_top a) t
6 WHERE rk <= 5
7 /

Sous 9i :

SQL> select * from table(dbms_xplan.display)
2 /
------------------------------------------------------------------------------
| Id | Operation                     | Name        | Rows | Bytes | Cost |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              | 11974 | 2221K|   254 |
|* 1 | VIEW                         |              | 11974 | 2221K|   254 |
|   2 |   WINDOW NOSORT               |              | 11974 |   970K|   254 |
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TOP     | 11974 |   970K|   254 |
|   4 |     INDEX FULL SCAN           | TEST_TOP_II | 11974 |       |    54 |
------------------------------------------------------------------------------

Snif, ça ne marche pas... alors que sous 10g :

SQL> set autot on
SQL> /
---------------------------------------------------------------------------------------
| Id | Operation                     | Name        | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             | 12879 | 2389K|   231   (1)|
|* 1 | VIEW                         |             | 12879 | 2389K|   231   (1)|
|* 2 |   WINDOW NOSORT STOPKEY       |             | 12879 | 1081K|   231   (1)|
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TOP    | 12879 | 1081K|   231   (1)|
|   4 |     INDEX FULL SCAN           | TEST_TOP_II | 12879 |       |    30   (4)|
----------------------------------------------------------------------------------


Statistiques
----------------------------------------------------------
          1 recursive calls
          0 db block gets
          5 consistent gets
          0 physical reads
          0 redo size
       1410 bytes sent via SQL*Net to client
        380 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          5 rows processed

Le NOSORT STOPKEY ! (et on vérifie sur les consistent gets que ce n'est pas que de la poudre aux yeux :))
Remarquez ici la valeur de la colonne "Rows" du plan d'exécution : ça sera l'objet du point 2 !

1)c.
Ce point n'est pas vraiment lié au type de requête ni aux fonctions analytiques... mais juste à la clause ORDER BY de manière générale. Mais je le cite ici parce que je me suis fait piéger :)

Trier sur une colonne alphanumérique, c'est piégeux. Pourquoi ?
Ben parce que l'ordre des lettres (avec la casse, les caractères bizarres, ...) n'est pas aussi unanime que l'ordre des nombres !
Et du coup, l'ordre réel dans l'index...

Allez, on se refait un test avec la méthode rownum.
Pour cela, on va commencer par créer un index sur object_name :

SQL> ALTER TABLE test_top MODIFY object_name NOT NULL
2 /
Table modifiée.

SQL> CREATE INDEX test_top_iia ON test_top(object_name) COMPUTE STATISTICS
2 /
Index créé.

La requête TOP / ROWNUM donne :
SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM
3     (SELECT *
4     FROM test_top
5     ORDER BY object_name)
6 WHERE rownum <= 5
7 /

---------------------------------------------------------------------------------
| Id | Operation               | Name     | Rows | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     5 |   885 |       |   313   (2)|
|* 1 | COUNT STOPKEY          |          |       |       |       |            |
|   2 |   VIEW                  |          | 12879 | 2226K|       |   313   (2)|
|* 3 |    SORT ORDER BY STOPKEY|          | 12879 | 1081K| 2952K|   313   (2)|
|   4 |     TABLE ACCESS FULL   | TEST_TOP | 12879 | 1081K|       |    51   (2)|
---------------------------------------------------------------------------------

Ben ouais, ça chie...
La manière de trier les alphanumériques est définie par le paramètre NLS_SORT. (En fait, c'est un peu plus compliqué puisqu'il y a ausse d'autre NLS_ qui peuvent se mettre des batons dans les roues les uns les autres...)
Mais bien entendu, l'ordonnancement effectif physique des clefs de l'index ne dépend pas de ce paramètre : le tri est effectué en binaire.
Donc pour utiliser l'index, il faut également demander le tri en binaire :

SQL> alter session set nls_sort=binary
2 /
Session modifiée.

Et du coup, ça marche !

SQL> SELECT *
2 FROM
3     (SELECT *
4     FROM test_top
5     ORDER BY object_name)
6 WHERE rownum <= 5
7 /
-----------------------------------------------------------------------------------
| Id | Operation                     | Name         | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     5 |   885 |     5   (0)|
|* 1 | COUNT STOPKEY                |              |       |       |            |
|   2 |   VIEW                        |              |     5 |   885 |     5   (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| TEST_TOP     | 12879 | 1081K|     5   (0)|
|   4 |     INDEX FULL SCAN           | TEST_TOP_IIA |     5 |       |     2   (0)|
-----------------------------------------------------------------------------------


2) Dans la première partie de cette article, j'ai tenté d'attirer votre attention sur une différence notable entre les méthodes ROWNUM et ROW_NUMBER :
La cardinalité !
L'optimiseur estime que la requête renvoie : 5 lignes avec ROWNUM contre... 12879 avec ROW_NUMBER !

Cela pose bien évidemment un problème lorsqu'on réutilise ce morceau de requête.
Un exemple un peu bidon : on fait l'autojointure sur object_id.

SQL> SELECT *
2 FROM
3 (SELECT *
4     FROM (SELECT a.*, row_number() over(order by object_id) rk
5         from test_top a) where rk <= 5
6 ) t
7* JOIN test_top b on t.object_id = b.object_id
SQL> /
------------------------------------------------------------------------------------
| Id | Operation                      | Name        | Rows | Bytes |TempSpc| Cost
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             | 12879 | 3471K|       |   468
|* 1 | HASH JOIN                     |             | 12879 | 3471K| 1240K|   468
|   2 |   TABLE ACCESS FULL            | TEST_TOP    | 12879 | 1081K|       |    51
|* 3 |   VIEW                         |             | 12879 | 2389K|       |   231
|* 4 |    WINDOW NOSORT STOPKEY       |             | 12879 | 1081K|       |   231
|   5 |     TABLE ACCESS BY INDEX ROWID| TEST_TOP    | 12879 | 1081K|       |   231
|   6 |      INDEX FULL SCAN           | TEST_TOP_II | 12879 |       |       |    30
------------------------------------------------------------------------------------

Et bien entendu, ça finit par un FULL SCAN alors qu'on devait lire 5 lignes par un index...
D'ailleurs, pour le confirmer, on peut lancer la version ROWNUM :

-----------------------------------------------------------------------------
| Id | Operation                        | Name        | Rows | Bytes | Cost
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |             |     5 | 1275 |   241
|   1 | TABLE ACCESS BY INDEX ROWID     | TEST_TOP    |     1 |    86 |     2
|   2 |   NESTED LOOPS                   |             |     5 | 1275 |   241
|   3 |    VIEW                          |             |     5 |   845 |   231
|* 4 |     COUNT STOPKEY                |             |       |       |
|   5 |      VIEW                        |             | 12879 | 2125K|   231
|   6 |       TABLE ACCESS BY INDEX ROWID| TEST_TOP    | 12879 | 1081K|   231
|   7 |        INDEX FULL SCAN           | TEST_TOP_II | 12879 |       |    30
|* 8 |    INDEX RANGE SCAN              | TEST_TOP_II |     1 |       |     1
------------------------------------------------------------------------------

Et là, d'un seul coup, une hypothèse qui provoque la sueur froide : certes le WINDOW STOPKEY permet de limiter le scan... mais le coût associé est celui du scan complet de l'index ??
Petit test :

SQL> select *
2 from test_top
3 order by object_id
4 /

-------------------------------------------------------------------------
| Id | Operation                   | Name        | Rows | Bytes | Cost
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             | 12879 | 1081K|   231
|   1 | TABLE ACCESS BY INDEX ROWID| TEST_TOP    | 12879 | 1081K|   231
|   2 |   INDEX FULL SCAN           | TEST_TOP_II | 12879 |       |    30
-------------------------------------------------------------------------

Eh oui, le coût est exactement le même que celui de la requête top...

Pour ce convaincre, de l'absurdité de la chose, on passe le clustering factor de l'index de 200 à 300, et on retente :

SQL> exec dbms_stats.set_index_stats(NULL, 'TEST_TOP_II', clstfct => 300)
Procédure PL/SQL terminée avec succès.

SQL> set autotrace traceonly explain
SQL> SELECT *
2 FROM (
3      SELECT a.*, row_number() over(order by object_id) rk
4      from test_top a)
5 where rk <= 5
6 /


---------------------------------------------------------------------------
| Id | Operation                | Name     | Rows | Bytes |TempSpc| Cost
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          | 12879 | 2389K|       |   313
|* 1 | VIEW                    |          | 12879 | 2389K|       |   313
|* 2 |   WINDOW SORT PUSHED RANK|          | 12879 | 1081K| 2952K|   313
|   3 |    TABLE ACCESS FULL     | TEST_TOP | 12879 | 1081K|       |    51
---------------------------------------------------------------------------

J'ai essayé de déchiffrer la trace 10053, et comme d'habitude, je nage. J'ai simplement cru comprendre qu'avec ROWNUM l'optimiseur détecte le plan de type "First K rows", et dans le second cas, il considère rk <= 5 comme un filtre sur un resultset non analysé...

Enfin bref l'amère conclusion : il faut probablement préférer ROWNUM pour les top queries simples...

Partager cet article

Repost0

Published by Pacman - dans SGBD
commenter cet article …

Le blog de Pacman

Update JOIN : MERGE vs (in-line)view update !

Bind variables and skewed data distribution

LEFT OUTER JOIN and single table predicate

ORA-01555 from delayed block cleanout : let's do it !

We do not need this CASE instruction !

Oracle CBO : Group by / Left Outer Join Cardinality

String aggregation in Oracle : Emulate GROUP_CONCAT

Using SQL blog to improve my foreign languages

SQL Geek's love declaration

Top queries, indexes and analytic functions

Recherche

Recherche

Articles Récents

Mémos Pl

Catégories

Recherche

Recherche

Articles Récents

Mémos Pl

Catégories

Newsletter