Utilisation de rownum et rowid

rowid

Les données sont stockées dans des tables, mais le fonctionnement interne d'oracle utilise un identifiant unique par tuple, quelque soit la table : le rowid.
C'est ainsi que lors de jointures, on aperçoit parfois dans l'explain plan (plan d'exécution) access by rowid, celà signifie qu'Oracle a reperé le rowid d'un tuple à partir d'un index par exemple, et qu'il accède au tuple entier dans la table par son rowid. Car c'est la manière la plus rapide pour accéder aux données.
Cependant, il n'est pas viable de baser son applicatif sur cet identifiant qui est propre à oracle qui peut être modifié en cas de réorganisation de la base, mais il est possible, dans le cadre d'optimisation, de l'utilisé dans une procédure stockée ou une sous-requête par exemple, la détermination du rowid étant faite dans une même transaction.
En effet, lors de réorganisation de base par exemple, oracle réattribue ses rowid afin de mieux les ordonnancer au sein des tablespaces.

Exemple de code supprimant toutes les commandes du client 'Johns'

delete from
(select cmd.rowid
from commande cmd
where cmd.idclient in (select cl.id from client cl where cl.nom = 'Johns');




Détecter les doublons




Soit une table T (C1, C2, C3, C4) ne contenant pas d'index unique et de clé primaire, avant de créer une clé primaire sur les deux premières colonnes, on veut s'assurer qu'aucun doublon n'existe sur le couple (C1, C2), on peut alors utiliser une requête s'appuyant sur les rowids :

select * from T a where (C1, C2) in (select C1, C2 from T b where b.rowid <> a.rowid);


rownum

Le rownum (et non pas numrow) est quant à lui propre au résultat de chaque requête, il s'agit en quelque sorte du numéro de la ligne du résultat dans l'ordre de retour. Ainsi, lorsque l'information qui nous interesse ne dépend pas du nombre de lignes ramenées, il est utilisé afin qu'oracle ne satisfasse pas toute la requête mais s'arrête au premier enregistrement trouvé (un ordre hint first_row est alors utilisé).

BEGIN [ ... ]
select 1 into var from t1 where col3 = 56 and rownum=1;
exception
when no_data_found
begin
[mon_traitement_en_cas_d_absence_de_donnée]
end;
[ ... ]
END;
/


qui sera beaucoup plus rapide qu'un count(*) suivi d'un test (> 0)


Le rownum peut alors facilement servir de remplaçant à LIMIT de MySQL par exemple, en entourant la requête initiale.

select * from t1 limit 15;

select * from t1 order by col1 desc limit 15;

select * from t1
union select * from t2 limit 15;


devient, en Oracle :

select * from t1 where rownum <= 15;

select * from (select * from t1 order by col1 desc) where rownum <= 15;

select * from (select * from t1 union select * from t2 ) where rownum <= 15;



Récupérer le nième enregistrement d'une requête




Il arrive parfois que l'on connaisse, à l'avance le rownum qu'on veut ; voici un exemple pour récupérer la ligne correspondante au 14ème tuple :

select * from (
select rownum, c.* from table1 c
where cond= 'EXEMPLE' and rownum <= 14 order by rownum desc ) where rownum =1;


Dans le cas d'une jointure ou d'une vue, rownum s'applique également : il porte sur le résultat de la jointure et peut être utilisé de la même manière que sur les exemples précédents.

Lors de l'analyse du plan d'exécution, on peut remarquer l'utilisation du mot clé COUNT STOPKEY :
SQL> set autotrace traceonly
SQL> select * from hr.maka where rownum < 14;

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1564959916

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    13 |  1664 |  2866 (100)| 00:00:35 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| MAKA |    33M|  4112M|  2866 (100)| 00:00:35 |
---------------------------------------------------------------------------

On aperçoit ainsi que malgré le coût constant, le nombre d'octet ainsi que le nombre de lignes est fortement diminué (ce qui implique un nombre d'IO diminuée.

Voir également :
- la mise en place d'autoincrément.
- La jointure : sélection sur plusieurs tables
- Clés primaires et étrangères : mettre en place ses contraintes d'intégrité




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories