Discussion :

[Scrouik]

Bonjour,

Je voulais savoir s'il était possible de remonter des enregistrements par groupes.
C'est a dire, faire par exemple un "select" sur une table qui me remonterait d'abord les 1000 premiers enregistrements puis les 10000 suivant etc ...
De la même façon est il possible de "delete" les enregistrements d'une table par block de 1000 (ou autre) ? Ceci afin de pouvoir "Commit" régulièrement.

Tout ceci dans le cadre d'un programme en PL SQL.
Il faut savoir que les requêtes select, insert et delete sont générées automatiquement à partir d'une table (la table des tables à traiter) et que je ne peux donc pas (si j'ai bien tout saisie) utiliser le "FORALL"

Merci

P.S. j'ai cherché dans le forum et je n'ai rien trouvé...

[mnitu]

Bonjour,

Je voulais savoir s'il était possible de remonter des enregistrements par groupes.
C'est a dire, faire par exemple un "select" sur une table qui me remonterait d'abord les 1000 premiers enregistrements puis les 10000 suivant etc ...
...

Ca c'est des requêtes de pagination (voir les FAQ sur ce site).

...
De la même façon est il possible de "delete" les enregistrements d'une table par block de 1000 (ou autre) ? Ceci afin de pouvoir "Commit" régulièrement.
...

C'est possible mais en général c'est eronné de le faire

Code :

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

1
2
3
 
delete ...
Where Rownum <= 1000

...
Tout ceci dans le cadre d'un programme en PL SQL.
Il faut savoir que les requêtes select, insert et delete sont générées automatiquement à partir d'une table (la table des tables à traiter) et que je ne peux donc pas (si j'ai bien tout saisie) utiliser le "FORALL"

Merci

P.S. j'ai cherché dans le forum et je n'ai rien trouvé...

[Scrouik]

J'ai trouvé la FAQ et j'ai fais une requête du genre :

Code :

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

SELECT * FROM (SELECT ROWNUM AS r, T.* FROM MA_TABLE T) WHERE r BETWEEN X AND Y

Mais celle-ci ne fonctionne malheureusement plus avec un insert car maintenant je récupère un nouveau champs "r".

Code :

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

1
2
3
INSERT INTO LA_TABLE(
     SELECT * FROM (SELECT ROWNUM AS r, T.* FROM MA_TABLE T) 
WHERE r BETWEEN X AND Y)

Sachant que la structure de "la_table' est la même que "ma_table"

Je ne suis pas loin ... je vais trouver.

[mnitu]

Petit astuce, examine le SELECT *
(et essaie d'utiliser les balises [code] [/code])

[Scrouik]

Petit astuce, examine le SELECT *
(et essaie d'utiliser les balises [code] [/code])

Ca rend le code beaucoup moins lisible quand il ne s'agit de ne mettre qu'une phrase et de plus ça ne répond pas vraiment à ma question :s pour ne pas dire que
je trooooooouuuuve pas

[mnitu]

Ca rend le code beaucoup moins lisible quand il ne s'agit de ne mettre qu'une phrase et de plus ça ne répond pas vraiment à ma question :s pour ne pas dire que
je trooooooouuuuve pas

Astuce

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SQL> insert into temp
  2       SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
  3         FROM (SELECT ROWNUM AS r, T.* FROM EMP T)
  4                               WHERE r BETWEEN 1 AND 3
  5  ;
 
3 rows inserted
 
SQL>

Mais franchement je ne pense pas que ce code qui a l'air de fonctionner comme disait quelque un récemment est correct. De tout façon moi je ne l’emploierai pas.

[skuatamad]

Salut mnitu,

Mais franchement je ne pense pas que ce code qui a l'air de fonctionner comme disait quelque un récemment est correct. De tout façon moi je ne l’emploierai pas.

Surtout sans ORDER BY, non?
Ou tu veux dire même avec, quelles seraient alors les subtilités potentielles?

[Scrouik]

Le problème c'est que je ne connais pas la structure de la table. Comme je le disais, les requêtes d'insertions sont créés dynamiquement à partir des nom de table que je prends dans une tables.
Je pourrais effectivement recréer la liste des champs a partir de la table des columns mais ça deviendrait vraiment une usine a gaz.

Pour être clair, le programme PL/SQL est un programme qui permet d'archiver les données d'une table vers une copie de cette table dans une autre instance.
Je récupère le nom des tables a archiver dans une table, je fais mes insertions puis je supprime de la table sources les données existant (exists) maintenant dans la table archive.
Le problème étant qu'il y a parfois tellement de données à archiver que les traitements mettent un temps fou. Le cahier des charges me demande donc de commit tout les X enregistrements afin de, en autre, réduire les temps de traitement.

[mnitu]

Salut mnitu,

Surtout sans ORDER BY, non?

Oui parce que sinon c'est n'importe quoi
Mais examine aussi ça

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
 
SQL> set autotrace traceonly explain
SQL> SELECT *
           FROM (SELECT ROWNUM AS r, T.* FROM EMP T)
                                WHERE r BETWEEN 1 AND 3  2    3  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes=
          1400)
 
   1    0   VIEW (Cost=2 Card=14 Bytes=1400)
   2    1     COUNT
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=518
          )
 
SQL> select *
  from ( select a.*, rownum rnum
           from ( select * from emp order by empno ) a
          where rownum <= 3 )
 where rnum >= 1
  2    3    4    5    6  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=3 Bytes=3
          00)
 
   1    0   VIEW (Cost=2 Card=3 Bytes=300)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=2 Card=14 Bytes=1218)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=
          14 Bytes=518)
 
   5    4           INDEX (FULL SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Car
          d=14)

[mnitu]

Le problème c'est que je ne connais pas la structure de la table. Comme je le disais, les requêtes d'insertions sont créés dynamiquement à partir des nom de table que je prends dans une tables.
Je pourrais effectivement recréer la liste des champs a partir de la table des columns mais ça deviendrait vraiment une usine a gaz.
...

Et pourtant ce n’est pas une usine à gaz mais une solution.

...
Pour être clair, le programme PL/SQL est un programme qui permet d'archiver les données d'une table vers une copie de cette table dans une autre instance.
Je récupère le nom des tables a archiver dans une table, je fais mes insertions puis je supprime de la table sources les données existant (exists) maintenant dans la table archive.
Le problème étant qu'il y a parfois tellement de données à archiver que les traitements mettent un temps fou. Le cahier des charges me demande donc de commit tout les X enregistrements afin de, en autre, réduire les temps de traitement.

C’est erroné ! Ca serait intéressant d’apprendre comment faire des commit par lot pourrait réduire le temps de traitement (quoi que il y a une histoire de commit asynchrone)
La solution est d’éliminer les éventuelles manipulations qui alourdissent le traitement (indexes, contraints, etc. sur les tables d’archivages) réduire le redo log et à la limite faire le traitement en parallèle si c’est possible.
Comme d’habitude il faut commencer par apprendre ou le temps passe. Exécute donc le traitement telle qu’il est avec une trace sql.

[Scrouik]

Et pourtant ce n’est pas une usine à gaz mais une solution.

J'ai effectivement fait comme ça ! Cela règle mon problème ( j'avoue que j'ai eu un instant de flemme) ...

C’est erroné ! Ca serait intéressant d’apprendre comment faire des commit par lot pourrait réduire le temps de traitement (quoi que il y a une histoire de commit asynchrone)
La solution est d’éliminer les éventuelles manipulations qui alourdissent le traitement (indexes, contraints, etc. sur les tables d’archivages) réduire le redo log et à la limite faire le traitement en parallèle si c’est possible.
Comme d’habitude il faut commencer par apprendre ou le temps passe. Exécute donc le traitement telle qu’il est avec une trace sql.

J'ai appris le PL/SQL sur le tas. Je ne connais pas 10% de ce que je devrais savoir, même si j'ai toujours réussi a atteindre mes objectifs. Il est clair que le jour ou un vrai spécialiste passera après moi, il aura du travail d'optimisation.
Alors pour ce qui est du commit par lot, des traitements parallèles ou simplement de tracer... je suis totalement larguer.

[mnitu]

...
J'ai appris le PL/SQL sur le tas. Je ne connais pas 10% de ce que je devrais savoir, même si j'ai toujours réussi a atteindre mes objectifs. Il est clair que le jour ou un vrai spécialiste passera après moi, il aura du travail d'optimisation.
Alors pour ce qui est du commit par lot, des traitements parallèles ou simplement de tracer... je suis totalement larguer.

Ce n’est pas grave tout ça. Je récemment entendu quelqu'un en disant que « nous avons mis du cœur à l’ouvrage». Bref, ça se voit tout de suite quand c’est fait seulement avec « du cœur ».

Bon courage!

[skuatamad]

Salut mnitu,
Bon il m'a fallu un petit peu de temps pour examiner tes 2 plans mais j'ai fini par trouver cette discussion qui m'a bien aidé et dans laquelle Tom Kyte dit :

The optimizer recognizes "where rownum < N" and knows -- Hey, if at all possible, I should STOP processing.
The optimizer sees "where rno between min and max" and doesn't have that ability.

C'était bien là que tu voulais en venir ?

Par contre est ce que cela veut dire que tu n'utilises pas row_number() pour faire de la pagination car (à moins que mon test,sur une 10gXE, ne soit foireux) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SQL> select a.*, rownum rnum
           from ( select * from hr.employees order by employee_id ) a
          where rownum <= 50  2    3  ;

50 ligne(s) s�lectionn�e(s).


Plan d'ex�cution
----------------------------------------------------------
Plan hash value: 2981130767

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    50 |  6650 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   VIEW                        |               |    50 |  6650 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   107 |  7276 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | EMP_EMP_ID_PK |    50 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

SQL> select *
  from ( select a.*, row_number() over (order by employee_id) rnum
                        from hr.employees a
       )
where rnum <= 50;  2    3    4    5  

50 ligne(s) s�lectionn�e(s).


Plan d'ex�cution
----------------------------------------------------------
Plan hash value: 237645235

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |   107 | 15622 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                         |               |   107 | 15622 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |               |   107 |  7276 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |   107 |  7276 |     3   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | EMP_EMP_ID_PK |   107 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Sinon (pour être tout à fait sûr ) quand tu disais :

Mais franchement je ne pense pas que ce code qui a l'air de fonctionner comme disait quelque un récemment est correct.De tout façon moi je ne l’emploierai pas.

c'était bien uniquement lié à l'absence du ORDER BY ?, parce que sinon je ne vois toujours pas

PS : désolé Scrouik d'avoir skuater ton post, mais des précisions sur les perfs en pagination peuvent quand même t'intéresser

[mnitu]

Salut mnitu,
Bon il m'a fallu un petit peu de temps pour examiner tes 2 plans mais j'ai fini par trouver cette discussion qui m'a bien aidé et dans laquelle Tom Kyte dit :
...

Salut Skuatamad,
Regarde sur le même site et sur le même sujet

...

Sinon (pour être tout à fait sûr ) quand tu disais :

c'était bien uniquement lié à l'absence du ORDER BY ?, parce que sinon je ne vois toujours pas
...

Oui la principale raison c'est l'absence du ORDER BY.
J'emploierais la forme présente dans le lien ci dessus.

[Scrouik]

Mettre le ORDER BY sur quel champs ?

[mnitu]

Mettre le ORDER BY sur quel champs ?

Mais on s'en fiche d'ORDER BY t'a déjà "réussi a atteindre tes objectifs", non ?

[Scrouik]

Mais on s'en fiche d'ORDER BY t'a déjà "réussi a atteindre tes objectifs", non ?

Oui ! C'est juste que j'essayais de comprendre, histoire de faire le malin pendant le repas de famille de dimanche prochain.
Je suis sur qu'un "ORDER BY" ben placé devrait pouvoir calmer Mémé pendant le café pas assez fort, a défaut d'accélérer une quelconque requête mal faites que de toutes façons personne n'utiliserait parce que ... heu ... ben ... c'est éronné : il manque un ORDER BY.

[mnitu]

Allez ne te fâche pas tu l’a cherché.
La requête de pagination tu vas l’exécuter n fois pour obtenir le premier lot des enregistrements à traiter ensuite le deuxième, troisième, etc.
Or sans un ORDER BY explicite Oracle n’est pas tenu des te renvoyer les enregistrements dans la même ordre (triés de la même façon) quand tu re exécute ta requête. Donc tu risques de traiter 2 fois la même chose voir de ne pas traiter certaines choses.
A propos il sera bien que tu lock (verrouille) la table source (ou les enregistrements concernés de cette table) en mode exclusif avant de commencer ton traitement

[skuatamad]

ORDER BY n'est pas là pour améliorer les perfs (un tri est coûteux, sauf peut être si tu tries sur une colonne indexée et dans le sens de l'index).
ORDER BY n'est là que pour garantir l'ordre de restitution des données, ce qui a une importance certaine quand on fait de la pagination.
C'est le WHERE rownum <=y qui est une astuce permettant de booster les perfs de restitution, surtout pour les premières pages, potentiellement couplé au hint FIRST_ROWS dans chaque sous-requête.

Pour conclure, si j'ai bien compris, il faudrait écrire la requête comme ça :

Code :

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

1
2
3
4
5
6
7
INSERT INTO LA_TABLE (pk_id,c2,c3,...)
	SELECT pk_id,c2,c3,...
	FROM (SELECT ROWNUM AS r, t.* 
			FROM (select * from MA_TABLE order by pk_id) t
			WHERE rownum <=y
			)
	where r>=x;

Mais j'ai peut être pas encore bien compris toutes les subtilités.

[EDIT] désolé mnitu, pour la redondance, j'ai posté en même temps que toi

[Scrouik]

Merci à vous !