Discussion :

[LaVaZza]

Bonjour tout le monde !

La dernière fois je vous avais demandé comment limiter le nombre de lignes avec rownum.

Et la solution que vous m'aviez renvoyé était du genre :

Code :

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

select * from (select rownum r, t.* from table1 t) where r between x and y

Pour moi celà ne réduit pas le temps de la requête (comme dans le cas où on ajoute des critères de recherche), à cause de la sous-requête (select rownum r, t.* from table1 t) qui prend bcp de temps (genre 2 minutes).

Je voudrais renvoyer un nombre limité de résultats sans forcément utiliser de critère de recherche, (dans la clause where).

Je peux pas utiliser l'id parce que ya des plages entières de lignes qui ont été supprimées, donc le nombre de lignes renvoyés peut être égal à 0 dans certains cas.

Pour résumer, dans le cas où j'ai 10000 lignes de renvoyées, je ne voudrais que les 100 premières, afin d'éviter le temps de la requête prenne trop de temps.

Je suis en Oracle version 8.1.7.

Merci d'avance,
Anthony Brenelière

[nuke_y]

Si tu ne veux que les 100 premières tu fais ça :

Code :

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

 select t.* from table1 t where rownum <= 100

Tu n'as besoin d'une sous-requête que dans le cas où tu veux un nombre de ligne avec un min et un max. Exemple si tu ne veux pas les 1000 1eres lignes mais les 1000 suivantes :

Code :

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

1
2
select * from (select rownum r, t.* from table1 t where rownum <= 2001) where r between 1001 and 2001
--

[LaVaZza]

Ben le problème c'est que lorsque j'utilise la clause rownum, ça n'augmente pas mes temps de réponse.

Voilà mes tests:

Code :

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

1
2
3
4
5
6
7
8
9
select * from vue_personnel
30000 résultats renvoyés, durée : 4000 ms
 
select * from vue_personnel where rownum < 1000
1000 résultats renvoyés, durée : 4000 ms
 
select * from vue_personnel where upper(nom) like 'P%'
1990 résultats renvoyés, durée : 200 ms

Je voudrais empêcher les cas où les temps de requêtes mettent 4000 secs.

Développeurs expérimentés, merci d'avance pour vos contributions !

[nuke_y]

Excuse moi mais vue_personnel c'est une vue ?

Et dans ce cas est-ce que la définition de cette vue ne pourrait pas être à l'origine du problème de performances et surtout du fait que la limitation sur les rownum ne te fasse rien gagner ?

[LeoAnderson]

Il y a quelque chose qui me dérange (ou qui m'échappe) :
- soit vous avez besoin de toutes les lignes retournées et dans ce cas, il faut chercher une vraie optimisation et non un moyen capilotracté pour arriver à des temps de réponses convenables
- soit vous n'avez pas besoin de toutes les lignes et dans ce cas, il faut filtrer la requête par la clause where....

De toute façon, le rownum n'intervenant qu'après l'interrogation des données, vous n'économiserez qu'au niveau du volume ramené sur le client, pas du volume de données manipulées par le serveur (mémoire, lectures disques, tri, temp, ....)

[pifor]

On peut interprêter le besoin exprimé en imaginant que le code client affiche le résultat de la requête sur plusieurs pages et que l'utilisateur veut sans doute rapidement avoir les 100 premières lignes mais qu'il est peu probable qu'il va paginer les 100 pages suivantes pour voir la ligne 9999 du résultat ...
Dans ce cas là, c'est peut-être aussi un problème de programmation coté client, il y a sans doute des mécanismes pour ramener les résultats par bloc de 100 lignes et on peut utiliser le hint FIRST_ROWS:

The FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.

For example, the optimizer uses the query optimization approach to optimize the following statement for best response time:
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible.

Comme l'a souligné nuke_y, il faut absolument être sûr qu'il n'y pas de clause GROUP BY ou ORDER BY dans la requête complète qui oblige Oracle à traiter toutes les lignes retournées même avec une clause rownum quelque part.

[gregory.broissard]

le first_rows est une bonne idée mais je crois que c'est un hint de 9i non?

[pifor]

Ce hint doit être aussi valable en 8.1.7 car il est documenté dans le chapître 7 Using Optimizer Hints de Oracle8i Designing and Tuning for Performance Release 2 (8.1.6).

[LaVaZza]

Ça c'est une très bonne idée, mais je ne vois pas comment l'utiliser, il prend celà comment un commentaire, et me renvoie toutes les lignes.

Code :

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

1
2
3
select/*+ FIRST_ROWS(10) */*from vue_personnel;

Il n'y pas quelque chose à faire pour activer celà ?*

Pour ce qui est de la requête, c'est une requête avec 4 jointure externes, c'est pour ça que le select * prend 4000 ms.

Je fais les tests au niveau de la base oracle, pas au niveau de IIS, donc celà ne prend pas en compte le transfert du volume de données et le traitement des pages html.

[pifor]

Un hint est une indication pour l'optimiseur de générer un plan optimal dans votre cas pour l'obtention des 10 premières lignes de la requête; mais cela ne va pas générer un plan d'exécution qui va retourner seulement les 10 premières lignes.

Pour être sûr qu'il est pris en compte, il faut que cost based optimizer utilisé. Il faudrait afficher le plan généré. Pour cela utiliser la commande
sqlplus set autotrace:

Code :

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

1
2
 
set autotrace on explain

Et exécuter votre requête avec et sans le hint pour comparer les plans.

Il peut être nécessaire d'exécuter le script

Code :

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

<ORACLE_HOME>/rdbms/admin/utlxplan.sql

avant set autotrace pour créer la table PLAN_TABLE dans le schéma courant.

[nuke_y]

Je confirme, j'ai fait des tests et dans le cas d'un plan d'exécution simple (même pour une requête avec jointure), ce hint ne change pas le problème.

[aline]

Ben le problème c'est que lorsque j'utilise la clause rownum, ça n'augmente pas mes temps de réponse.

Voilà mes tests:

Code :

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

1
2
3
4
5
6
7
8
9
select * from vue_personnel
30000 résultats renvoyés, durée : 4000 ms
 
select * from vue_personnel where rownum < 1000
1000 résultats renvoyés, durée : 4000 ms
 
select * from vue_personnel where upper(nom) like 'P%'
1990 résultats renvoyés, durée : 200 ms

Je voudrais empêcher les cas où les temps de requêtes mettent 4000 secs.

Développeurs expérimentés, merci d'avance pour vos contributions !

Bonjour,

Pourrai-t'on avoir une trace (tkprof) avec les plans d'executions stp.

sans cela, pas d'aide possible!

[LaVaZza]

Voici mon plan d'exécution pour la requête suivant:

Code :

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

select * from vue_personnel_new

..qui prend 4000 ms pour l'exécution:

[aline]

Et bien, on peux reposer le problème à nouveau.
Il faudrai optimiser ta vue (qui n'est en fait q'une simple requete)

Tu peux retrouver le texte avec ce type de requete:

Code :

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

1
2
 
select test from user_view where view_name='VUE_PERSONNEL'

Ensuite, il faudra optimiser la requete correspondante.
Cela passera par la pose d'indexes sur tes tables (tu ne fais que des tables access full, il y a surement beaucoup à faire) et aussi par le calcul des stats sur ces tables s'il n'est pas fait.

[nuke_y]

Il me semble voir un UNION ALL. Si Oracle attend la fin de la requête pour n'en rapatrier que 1000 lignes, de toutes façons c'est mort pour les performances.

Donc effectivement il faudrait voir à optimiser la requête de la vue elle même.

[orafrance]

il n'y a pas 36000 solutions : créer une table avec le nombre restreint d'enregistrement ou partitionner la table... la mise en oeuvre sera couteuse mais pas les requêtes qui feront le SELECT

[LaVaZza]

Je ne vois pas trop comment je peux optimiser la requête, j'ai déjà des index sur les jointures des tables. Pour information ce sont des index de type 'non unique'.

Bon par contre il n'y a pas spécialement de contrainte de type clés étrangères sur les jointures entre les tables, mais est-ce que celà a une influence sur le temps de requête ?

Quant à mettre les résultats dans une table avec un nombre de résultats limités je vois pas trop l'intéret, parce que ça revient au même: il faut bien que je l'éexécute, cette requête!

[nuke_y]

Oui il faut que tu l'exécutes, mais si tu travailles sur des échantillons tu n'as peut-être pas besoin de données "fraîches" et donc tu peux l'exécuter à un autre moment. Par contre la requête de lecture sera immédiate. Sinon tu as aussi les vues matérialisées.

Et pour ce qui est d'optimiser ta requête, je laisse les pros te conseiller.

[orafrance]

je pensais que c'était pour des tests... mais apparemment c'est dans l'appli pour (j'imagine) limiter l'affichage. Dans ce cas, aucune chance d'améliorer les perfs sans clause WHERE, t'aura forcément un FULL SCAN. SAUF éventuellement avec un partitionnement mais là faut tester parce que je ne suis pas sûr du tout de ce que j'avance

[LaVaZza]

Ben la solution de mise à jour d'un snapshot comprenant TOUS LES ÉLÉMENTS de cette requête, exécutée automatiquement toutes les 5 minutes par exemples, me semble une bonne alternative.