Discussion :

[Gaadek]

Bonjour à tous,

J'ai un soucis de performance avec une de mes requêtes SQL.

Cette requête exécute une recherche dans une table d'audit contenant une date de validité afin de construire une vue.

Les données sources sont stockées dans une table:

Code :

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

1
2
3
4
5
id       val01        end_ts
1        1            01-JAN-2011
2        3            03-MAR-2011
3        5            15-AUG-2050
4        3            15-AUG-2050

Dans l'exemple ci-dessus, end_ts représente la date de validité.
Les données courantes sont celles ayant end_ts = 15-AUG-2050 (une date future qui est la même pour tous les records courants - me demandez pas pourquoi cette date...), soit les records 3 & 4.

La vue ne doit afficher que les données courantes et leur appliquer une fonction d'aggrégation (une somme).

Afin de ne récupérer que les données courantes, je dois donc filtrer les données, ce que je fais comme cela:

Code :

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

1
2
3
Select sum(val01) 
from ma_table 
where end_ts = to_date('15-AUG-2050', 'DD-MMM-YYYY');

Le soucis, c'est que l'utilisation de la fonction to_date réduit de manière trop importante les performances de ma requête.
Quand j'exécute la requête sans where clause, quelques secondes suffisent.
Par contre, avec where clause, plusieurs dizaines de minutes sont nécessaires (j'ai environ 400 000 records dans ma_table)

Je commence donc à envisager de ne plus faire de vue, mais une seconde table, rafraichie avec une procédure SQL (qui me permettrait de stocker le résultat de to_date('15-AUG-2050', 'DD-MMM-YYYY') dans une variable, puis d'utiliser cette variable dans la where clause)

Cependant, me gène de ne plus avoir de vue, je serais obligé de forcer un rafraichissement (job schedulé), et surtout, je me demande s'il n'y a pas de possibilité pour faire autrement!

Merci par avance pour votre aide

[pacmann]

Salut !

Plusieurs dizaines de minutes pour 400K lignes, ça parait monstrueux...

Par contre, to_date en soi ne ralentit pas de requêtes (essaie par exemple de faire la même sans clause where).

Y a-t-il beaucoup de lignes courante par rapport au total des lignes ? Serait-il peut être souhaitable de poser un index sur la date ? Pourrais-tu donner le plan d'exécution de ta requête ?

[skuatamad]

Franchement la même que Pacmann, il faut au moins un plan (set autot trace dans sqlplus) voir un tkprof

Sinon c'était juste pour préciser que :

Je commence donc à envisager de ne plus faire de vue, mais une seconde table, rafraichie avec une procédure SQL (qui me permettrait de stocker le résultat de to_date('15-AUG-2050', 'DD-MMM-YYYY') dans une variable, puis d'utiliser cette variable dans la where clause)

Cependant, me gène de ne plus avoir de vue, je serais obligé de forcer un rafraichissement (job schedulé), et surtout, je me demande s'il n'y a pas de possibilité pour faire autrement!

Ca s'appelle une vue matérialisée (mais c'est plus pour ta culture... il n'y a aucune raison pour que la requête présentée sur 400K lignes prennent 10 minutes...)

[Gaadek]

Bonjour,

Merci pour vos réponses!

Alors, je vais d'abord répondre à Pacman:

Par contre, to_date en soi ne ralentit pas de requêtes (essaie par exemple de faire la même sans clause where).

Justement, c'est exactement le problème: j'ai identifié le to_date comme facteur limitant dans ma requête, cf mon post original:

Quand j'exécute la requête sans where clause, quelques secondes suffisent.

Pour répondre à skuatamad:
Merci, je sais ce qu'est une vue matérialisée (ou snapshot) . Ceci dit, je ne suis pas certains qu'on puisse utiliser une variable.

Je m'explique: une vue matérialisée exécute une requête SQL a une fréquence donnée (tous les jours,...) Bref, je vais pas expliquer plus dans les détails.

En gros, ma vue matérialisée va exécuter le même script que ma vue Oracle, sauf que je vais le faire à un horaire prédéfini. Ma vue matérialisée sera beaucoup plus rapide en accès, par contre, il va me falloir toujours autant de temps pour la mettre à jour...

Par contre, si je créé une table (non matérialisée), rafraichie par un script PL/SQL, ça me semble plus efficace, mais pas très propre sur la méthode:

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
declare
  max_ts date := to_date('15-AUG-2050', 'DD-MON-YYYY');
begin
  begin
    drop table mon_autre_table;
  exception
    when OTHERS then
      null;
  end;
 
  create table mon_autre_table as 
    select pt, sum(val01) 
    from ma_table 
    where end_ts = max_ts
    group by pt
  ;
end;

Quoiqu'il en soit, je vais poster le plan d'exécution dès demain!

[startout]

Bonjour,

Et la création d'un index sur la fonction to_date(end_ts) vous l'avez peut-être déjà fait ?

[Gaadek]

Bonjour,

Et la création d'un index sur la fonction to_date(end_ts) vous l'avez peut-être déjà fait ?

Bonjour,

Je ne comprends pas votre remarque.

La table d'origine contient la variable "end_ts", je ne vois pas comment je paux mettre un index sur "to_date(end_ts)"

Si vous pouviez m'aider sur ce point!