Select par groupe d'enregistrement

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é...

Citation:

---

Envoyé par 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 ...
...

---

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

Citation:

---

Envoyé par Scrouik

...
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:

---

1 2 3

delete ... Where Rownum <= 1000

---

Citation:

---

Envoyé par Scrouik

...
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é...

---

:roll:

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

Code:

---

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:

---

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.

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

Citation:

---

Envoyé par mnitu

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 :aie:

Citation:

---

Envoyé par Scrouik

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 :aie:

---

Astuce

Code:

---

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.

Salut mnitu,

Citation:

---

Envoyé par 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?

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.

Citation:

---

Envoyé par skuatamad

Salut mnitu,

Surtout sans ORDER BY, non?

---

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

Code:

---

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)

---

Citation:

---

Envoyé par 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.
...

---

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

Citation:

---

Envoyé par Scrouik

...
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.

Citation:

---

Envoyé par mnitu

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) ...

Citation:

---

Envoyé par mnitu

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.

Citation:

---

Envoyé par Scrouik

...
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!

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 :

Citation:

---

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:

---

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 :

Citation:

---

Envoyé par 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.

---

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

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 :)

Citation:

---

Envoyé par 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 :
...

---

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

Citation:

---

Envoyé par skuatamad

...

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 :roll:
...

---

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

Mettre le ORDER BY sur quel champs ? 8O

Citation:

---

Envoyé par Scrouik

Mettre le ORDER BY sur quel champs ? 8O

---

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

Citation:

---

Envoyé par mnitu

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.

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

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:

---

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 :)

Merci à vous !

Dans le doutes j'ai récupérer les champs de l'index primaire afin de faire un ORDER BY dessus ! :king:


Mais j'ai un nouveau problème :(

Afin d'accélérer mes insertions j'ai désactivé les indexes de la table avec

Code:

---

ALTER INDEX L_INDEX UNUSABLE;

---

du coup oracle me dit que je ne peux pas insérer les données car :
ORA-01502: l'index 'LA_TABLE.L_INDEX' ou sa partition est inutilisable

Heu ... comment dire ... Help ?

drop les indexes puis recrées les en fin de traitement.
Tu peux aussi regarder cette discussion

Citation:

---

Envoyé par skuatamad

drop les indexes puis recrées les en fin de traitement.
Tu peux aussi regarder cette discussion

---

Le pire c'est que c'est justement de la que je venais !
Je suis en version 9 et l'option "skip_unusable_indexes" n'existe pas.
Je ne trouve pas l'option alternative dans la version 9.