Génération d'identifiant dans une vue [Résolu]

infameus · 07/09/2011, 13h44

Bonjour,
je recupère des informations via une vue qui ne dispose pas de clé primaire.
Il n'existe aucune données dans cette vue qui puisse etre pertinante pour composer un identifiant unique (potentiellement 2 lignes peuvent avoir les memes données).
J'ai essayé de créer un identifiant en utilisant rownum, mais les perfs sont minable (on passe de 00.3s à 40 sec).
Existe t'il un moyen de générer un identifiant unique en oracle (type uniqid php) ?
Merci.

laurentschneider · 07/09/2011, 14h15

oui, sys_guid(), mais rownum ne devrait pas dégrader -autant- la performance

infameus · 07/09/2011, 14h22

Merci, c'est nickel !
pour le rownum, je ne sais pas pourquoi, ma vue est construite sur un UNION ALL avec des from dblink.
C'est pas ma tasse de thé oracle en fait ;-)

StringBuilder · 07/09/2011, 14h28

Quand tu dis que tu passes de 0,03 secondes à 40 secondes, est-ce que t'as essayé de fetcher l'ensemble des lignes dans le premier cas ?

En effet, pour attribuer un rownum sur l'ensemble des lignes de la requête, Oracle doit fetcher l'intégralité des données, alors que s'il se contente d'exécuter une vue sans mettre de rownum, ton client ne fetch qu'un petit buffer (30 ou 40 lignes).

Je pense que la différence de performances vient de là.

Si tu fait ça, ça dure toujours 40 secondes ?

Code :

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

SELECT rownu, v.* FROM mavue v WHERE <des critères qui filtrent 10 lignes>"

Rams7s · 07/09/2011, 16h55

Citation:

Envoyé par StringBuilder

En effet, pour attribuer un rownum sur l'ensemble des lignes de la requête, Oracle doit fetcher l'intégralité des données, alors que s'il se contente d'exécuter une vue sans mettre de rownum, ton client ne fetch qu'un petit buffer (30 ou 40 lignes).

Je suis pas certain de bien comprendre.

Quand on utilise rownum, oracle recupere tout d'abord, histoire de pouvoir poser les rownums tranquillement?

Parce que quand on ajoute WHERE rownum<10, au contraire ca va bien plus vite.

Merci de m'eclairer!

StringBuilder · 07/09/2011, 19h19

Citation:

Envoyé par Rams7s

Je suis pas certain de bien comprendre.

Quand on utilise rownum, oracle recupere tout d'abord, histoire de pouvoir poser les rownums tranquillement?

Parce que quand on ajoute WHERE rownum<10, au contraire ca va bien plus vite.

Merci de m'eclairer!

Vu les résultats, c'est pourtant ce que j'ai l'impression qu'il fait.

Waldar · 07/09/2011, 19h49

Un simple explain plan répond pourtant à la question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
EXPLAIN PLAN FOR
SELECT *
  FROM sys.dba_tables
 WHERE rownum <= 10;
 
SELECT * FROM TABLE(dbms_xplan.display);
 
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
| Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                                                        
|   0 | SELECT STATEMENT                   |                |    10 | 11120 |    40   (3)| 00:00:01 |                                                                                                                                                                                                        
|*  1 |  COUNT STOPKEY                     |                |       |       |            |          |    
 
***

Rams7s · 12/09/2011, 12h24

Je suis pas ultra convaincu par les explications.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT count(*) FROM pio_test;
  COUNT(*)
----------
   1000000
 
>CREATE VIEW tmp_view AS SELECT * FROM pio_test;
>CREATE VIEW tmp_view2 AS SELECT rownum n1, pio_test.* FROM pio_test;
>SET termout off
>SELECT * FROM tmp_view;
>SELECT * FROM tmp_view2;
>SELECT * FROM tmp_view2 WHERE rownum<100;
>SET termout ON

Et la trace n'indique pas vraiment que oracle va tout recuperer pour poser les rownums. Il s'arrete apres 99 tuples dans le 3eme cas.

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
SELECT *
FROM
 tmp_view
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
   1000000    1000000    1000000  TABLE ACCESS FULL PIO_TEST (cr=75931 pr=9902 pw=0 time=2942908 us cost=2735 size=66000000 card=1000000)
 
 
SELECT *
FROM
 tmp_view2
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
   1000000    1000000    1000000  VIEW  TMP_VIEW2 (cr=75931 pr=9902 pw=0 time=3208604 us cost=2735 size=58000000 card=1000000)
   1000000    1000000    1000000   COUNT  (cr=75931 pr=9902 pw=0 time=2772008 us)
   1000000    1000000    1000000    TABLE ACCESS FULL PIO_TEST (cr=75931 pr=9902 pw=0 time=2550021 us cost=2735 size=66000000 card=1000000)
 
 
 
SELECT *
FROM
 tmp_view2 WHERE rownum<100
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        99         99         99  COUNT STOPKEY (cr=10 pr=28 pw=0 time=8449 us)
        99         99         99   VIEW  TMP_VIEW2 (cr=10 pr=28 pw=0 time=8447 us cost=2 size=5742 card=99)
        99         99         99    COUNT  (cr=10 pr=28 pw=0 time=8440 us)
        99         99         99     TABLE ACCESS FULL PIO_TEST (cr=10 pr=28 pw=0 time=8437 us cost=2 size=6534 card=99)

Que ce soit plus long a cause du count, ok. Mais pas pour une histoire de recuperer l'ensemble des donnees afin de poser le rownum.
Ou alors, j'ai rate une etape.

Mohamed.Houri · 12/09/2011, 12h56

Citation:

Envoyé par StringBuilder

En effet, pour attribuer un rownum sur l'ensemble des lignes de la requête, Oracle doit fetcher l'intégralité des données, alors que s'il se contente d'exécuter une vue sans mettre de rownum, ton client ne fetch qu'un petit buffer (30 ou 40 lignes).
lignes>"[/code]

Code :

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

1
2
3
4
5
6
 
mhouri > SELECT count(1) FROM emp;
 
  COUNT(1)                                                                      
----------                                                                      
        14

Notez bien que la table emp contient 14 lignes

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
 
mhouri > SET linesize 120
mhouri > SELECT /*+ gather_plan_statistics */
  2  * FROM emp
  3  WHERE rownum <= 10;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                   
      7369 allen      clerk           7902 30-MAR-10        815                    20                                   
      7499 allen      salesman        7698 20-FEB-81       1600        300         30                                   
      7521 ward       salesman        7698 22-FEB-81       1250        500         30                                   
      7566 jones      manager         7839 02-APR-81       2975                    20                                   
      7654 martin     salesman        7698 28-SEP-81       1250       1400         30                                   
      7698 blake      manager         7839 01-MAY-81       2850                    30                                   
      7782 clark      manager         7839 09-JUN-81       2450                    10                                   
      7788 scott      analyst         7566 09-DEC-82       3000                    20                                   
      7839 king       president            17-NOV-81       5000                    10                                   
      7844 turner     salesman        7698 08-SEP-81       1500          0         30                                   
 
10 rows selected.
 
mhouri > SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                       
--------------------------------------------------------------------------------------------
SQL_ID  b6ccd7j9nqqcm, child number 0                                                                                   
-------------------------------------                                                                                   
SELECT /*+ gather_plan_statistics */ * FROM emp WHERE rownum <= 10                                                      
 
Plan hash value: 4269703525                                                                                             
 
-------------------------------------------------------------------------------------                                   
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                   
-------------------------------------------------------------------------------------                                   
|*  1 |  COUNT STOPKEY     |      |      1 |        |     10 |00:00:00.01 |       4 |                                   
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     10 |     10 |00:00:00.01 |       4 |                                   
-------------------------------------------------------------------------------------                                   
 
Predicate Information (IDENTIFIED BY operation id):                                                                     
---------------------------------------------------                                                                     
 
   1 - filter(ROWNUM<=10)

Et notez bien maintenant dans la partie A-Rows de l'explain plan qu'Oracle n'a effectivement sélectionné que 10 lignes, malgré un FULL TABLE SCAN sur une table contenant 14 lignes. C'est l'opération 1 COUNT STOPKEY qui arrête oracle dans son opération FULL TABLE SCAN lorsque le "count" voulu est atteint

Par contre, il faut faire attention avant de passer à la généralisation

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
 
mhouri > SELECT * FROM (SELECT /*+ gather_plan_statistics */ * FROM emp ORDER BY sal DESC) WHERE rownum <= 10;
 
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                   
      7839 king       president            17-NOV-81       5000                    10                                   
      7788 scott      analyst         7566 09-DEC-82       3000                    20                                   
      7902 ford       analyst         7566 03-DEC-81       3000                    20                                   
      7566 jones      manager         7839 02-APR-81       2975                    20                                   
      7698 blake      manager         7839 01-MAY-81       2850                    30                                   
      7782 clark      manager         7839 09-JUN-81       2450                    10                                   
      7499 allen      salesman        7698 20-FEB-81       1600        300         30                                   
      7844 turner     salesman        7698 08-SEP-81       1500          0         30                                   
      7934 miller     clerk           7782 23-JAN-82       1300                    10                                   
      7521 ward       salesman        7698 22-FEB-81       1250        500         30                                   
 
10 rows selected.
 
mhouri > SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                       
--------------------------------------------------------------------------------------------------------------------
SQL_ID  cwxnmwrsvsaqb, child number 0                                                                                   
-------------------------------------                                                                                   
SELECT * FROM (SELECT /*+ gather_plan_statistics */ * FROM emp ORDER BY sal DESC) WHERE rownum <= 10                    
 
Plan hash value: 2900113198                                                                                             
 
---------------------------------------------------------------------------------------------------------------------   
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |   
---------------------------------------------------------------------------------------------------------------------   
|*  1 |  COUNT STOPKEY          |      |      1 |        |     10 |00:00:00.01 |      15 |       |       |          |   
|   2 |   VIEW                  |      |      1 |     14 |     10 |00:00:00.01 |      15 |       |       |          |   
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |     14 |     10 |00:00:00.01 |      15 |  2048 |  2048 | 2048  (0)|   
|   4 |     TABLE ACCESS FULL   | EMP  |      1 |     14 |     14 |00:00:00.01 |      15 |       |       |          |   
---------------------------------------------------------------------------------------------------------------------   
 
Predicate Information (IDENTIFIED BY operation id):                                                                     
---------------------------------------------------                                                                     
 
   1 - filter(ROWNUM<=10)                                                                                               
   3 - filter(ROWNUM<=10)

Ici, toutes les lignes ont été lues.

Rams7s · 12/09/2011, 14h31

Citation:

Envoyé par Mohamed.Houri

Par contre, il faut faire attention avant de passer à la généralisation

Code :

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

1
2
3
 
mhouri > SELECT * FROM (SELECT /*+ gather_plan_statistics */ * FROM emp ORDER BY sal DESC) WHERE rownum <= 10;
[..]

Ici, toutes les lignes ont été lues.

Il y a un order by, ce serait quand même un gros paris que l'ensemble des lignes ne soient pas lues.

Et je ne vois pas hyper-bien le rapport avec la phrase que vous citez.

Par contre, on est d'accord. La phrase de stringbuilder est pour le moins etrange.

Je suis desole, je reviens de week-end, pas facile de se remettre dans le bain.

07/09/2011, 13h44	#1
infameus Nouveau Membre du Club laurent chavignon Développeur Web Inscription : juin 2002 Messages : 81 Détails du profil Informations personnelles : Nom : laurent chavignon Âge : 32 Localisation : France, Rhône (Rhône Alpes) Informations professionnelles : Activité : Développeur Web Informations forums : Inscription : juin 2002 Messages : 81 Points : 31 Points : 31	Génération d'identifiant dans une vue Bonjour, je recupère des informations via une vue qui ne dispose pas de clé primaire. Il n'existe aucune données dans cette vue qui puisse etre pertinante pour composer un identifiant unique (potentiellement 2 lignes peuvent avoir les memes données). J'ai essayé de créer un identifiant en utilisant rownum, mais les perfs sont minable (on passe de 00.3s à 40 sec). Existe t'il un moyen de générer un identifiant unique en oracle (type uniqid php) ? Merci.
	00

07/09/2011, 14h15	#2
laurentschneider Expert Confirmé Sénior Laurent Schneider Administrateur de base de données Inscription : décembre 2005 Messages : 2 926 Détails du profil Informations personnelles : Nom : Laurent Schneider Localisation : Suisse Informations professionnelles : Activité : Administrateur de base de données Secteur : Finance Informations forums : Inscription : décembre 2005 Messages : 2 926 Points : 4 547 Points : 4 547	oui, sys_guid(), mais rownum ne devrait pas dégrader -autant- la performance __________________ Mon blog : laurentschneider.com Mon livre : Advanced Oracle SQL Programming
	00

07/09/2011, 14h22	#3
infameus Nouveau Membre du Club laurent chavignon Développeur Web Inscription : juin 2002 Messages : 81 Détails du profil Informations personnelles : Nom : laurent chavignon Âge : 32 Localisation : France, Rhône (Rhône Alpes) Informations professionnelles : Activité : Développeur Web Informations forums : Inscription : juin 2002 Messages : 81 Points : 31 Points : 31	Merci, c'est nickel ! pour le rownum, je ne sais pas pourquoi, ma vue est construite sur un UNION ALL avec des from dblink. C'est pas ma tasse de thé oracle en fait ;-)
	00

07/09/2011, 14h28	#4
StringBuilder Membre Expert Sylvain Devidal Chef de projets Générix Inscription : février 2010 Messages : 1 062 Détails du profil Informations personnelles : Nom : Sylvain Devidal Âge : 33 Localisation : France, Rhône (Rhône Alpes) Informations professionnelles : Activité : Chef de projets Générix Secteur : High Tech - Éditeur de logiciels Informations forums : Inscription : février 2010 Messages : 1 062 Points : 1 515 Points : 1 515	Quand tu dis que tu passes de 0,03 secondes à 40 secondes, est-ce que t'as essayé de fetcher l'ensemble des lignes dans le premier cas ? En effet, pour attribuer un rownum sur l'ensemble des lignes de la requête, Oracle doit fetcher l'intégralité des données, alors que s'il se contente d'exécuter une vue sans mettre de rownum, ton client ne fetch qu'un petit buffer (30 ou 40 lignes). Je pense que la différence de performances vient de là. Si tu fait ça, ça dure toujours 40 secondes ? Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT rownu, v.* FROM mavue v WHERE <des critères qui filtrent 10 lignes>"
	01

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email