Temps d'exécution requête Oracle sql trop Longue

SELECT to_char(b.DATEACTION,'HH24:MI:SS') as HEURE, b.* FROM ( SELECT a.*, ROWNUM AS NUMERO FROM ( SELECT l.* FROM LOG l WHERE to_char(DATEACTION,'YYYYMM') >= to_char(to_date(date1,'MM/YYYY'),'YYYYMM') AND to_char(DATEACTION,'YYYYMM') <= to_char(to_date(date2,'MM/YYYY'),'YYYYMM') ORDER BY ID DESC,DATEACTION DESC ) a )b WHERE NUMERO BETWEEN rowMIN and rowMAX;

date1 et date2, ce sont des chaînes de caractères ou des dates ?

Si ce sont des chaînes de caractères (représentant un mois au format MM/YYYY) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT to_char(b.DATEACTION,'HH24:MI:SS') AS HEURE
     , b.*  
  FROM ( SELECT a.*
              , ROWNUM AS NUMERO 
           FROM ( SELECT *  
                    FROM LOG  
                   WHERE DATEACTION >= to_date(date1,'MM/YYYY')
                     AND DATEACTION <= to_date(date2,'MM/YYYY')
                ORDER BY ID DESC
                ) a
          WHERE ROWNUM <= rowMAX
       ) b
 WHERE NUMERO >= rowMIN;

Si ce sont des dates :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT to_char(b.DATEACTION,'HH24:MI:SS') AS HEURE
     , b.*  
  FROM ( SELECT a.*
              , ROWNUM AS NUMERO 
           FROM ( SELECT *  
                    FROM LOG  
                   WHERE DATEACTION >= trunc(date1,'MM')
                     AND DATEACTION <  add_months(trunc(date2,'MM')-1, 1)
                ORDER BY ID DESC
                ) a
          WHERE ROWNUM <= rowMAX
       ) b
 WHERE NUMERO >= rowMIN;