[Factoring_Clause]Problème dans une requête

**media** · 19/08/2006, 09h03

Bonjour,

SGBDR : ORACLE v9.2.0.6.0 et V9.2.0.1.0 (même comportement)

Le problème qui se pose à moi est le suivant :

Le système gère des tâches qui peuvent être exécutées sur différentes machines.
Une tâche dure un certain temps.
Plusieurs tâches peuvent avoir lieu en même temps sur la même machine.

Pour simplifier le problème, je me ramène à une seule table :

CREATE TABLE Planning (IdMachine INTEGER, Debut DATE, Fin DATE);

IdMachine identifie la machine.
Debut est la date à laquelle commence la tâche
Fin est la date à laquelle se termine la tâche

(je n'inclus pas l'identification des tâches qui n'a aucune importance ici).

Voici un exemple de remplissage de cette table

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
INSERT INTO Planning VALUES (1, to_date('01/01/2005', 'DD/MM/YYYY'), to_date('01/03/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/02/2005', 'DD/MM/YYYY'), to_date('01/05/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/04/2005', 'DD/MM/YYYY'), to_date('01/08/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/06/2005', 'DD/MM/YYYY'), to_date('01/09/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/07/2005', 'DD/MM/YYYY'), to_date('01/10/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/11/2005', 'DD/MM/YYYY'), to_date('01/03/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/12/2005', 'DD/MM/YYYY'), to_date('01/01/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (1, to_date('01/02/2006', 'DD/MM/YYYY'), to_date('01/04/2006', 'DD/MM/YYYY'));
 
INSERT INTO Planning VALUES (2, to_date('01/02/2005', 'DD/MM/YYYY'), to_date('01/02/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/03/2005', 'DD/MM/YYYY'), to_date('01/07/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/05/2005', 'DD/MM/YYYY'), to_date('01/08/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/09/2005', 'DD/MM/YYYY'), to_date('01/10/2005', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/11/2005', 'DD/MM/YYYY'), to_date('01/01/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/12/2005', 'DD/MM/YYYY'), to_date('01/03/2006', 'DD/MM/YYYY'));
INSERT INTO Planning VALUES (2, to_date('01/04/2006', 'DD/MM/YYYY'), to_date('01/05/2006', 'DD/MM/YYYY'));

cf. le fichier joint (en Jaune, Bleu et Orange les périodes de trois tâches ; en Violet les périodes libres).

Le question que je dois résoudre est de déterminer quelles sont les périodes pendant lesquelles les machines ne sont pas utilisées.
J'ai mis au point une solution qui devrait fonctionner, et que je vais expliquer en deux parties :

Requête N° 1

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
WITH ax AS (SELECT IdMachine, debut, fin
            FROM Planning
        UNION
            SELECT DISTINCT IdMachine, to_date('15/02/2005',    'DD/MM/YYYY'), to_date('15/02/2005',    'DD/MM/YYYY')
            FROM Planning
        UNION
            SELECT DISTINCT IdMachine, to_date('15/03/2006',    'DD/MM/YYYY'), to_date('15/03/2006',    'DD/MM/YYYY')
            FROM Planning),
     px AS (SELECT IdMachine, debut, fin, sys_connect_by_path(to_char(debut,    'DD/MM/YYYY'),    ' ') AS Chemin
            FROM ax 
            START WITH(IdMachine, debut) IN (SELECT IdMachine,  debut
                                      FROM ax b
                                      WHERE fin >= to_date('15/02/2005',    'DD/MM/YYYY')
                                        AND debut <= to_date('15/03/2006',    'DD/MM/YYYY')
                                        AND NOT EXISTS (SELECT NULL
                                                        FROM ax c
                                                        WHERE b.IdMachine = c.IdMachine
                                                          AND b.debut > c.debut
                                                          AND b.debut <= c.fin))
            CONNECT BY IdMachine = PRIOR IdMachine
                   AND debut BETWEEN PRIOR debut AND PRIOR fin
                   AND fin > PRIOR fin),
     bx AS (SELECT IdMachine, 
                   greatest(to_date(SUBSTR(chemin, 1, 11), 'DD/MM/YYYY'),    to_date('15/02/2005', 'DD/MM/YYYY')) AS  debut,
                   least(MAX(fin),    to_date('15/03/2006',    'DD/MM/YYYY')) AS fin
            FROM px
            GROUP BY IdMachine, SUBSTR(chemin, 1, 11))
SELECT * FROM bx;

La requête précédente me donne les périodes où les machines sont utilisées, à titre d'exemple, pendant l'intervalle de temps ['15/02/2005'; '15/03/2006'].

Cette requête fonctionne parfaitement et me donne le résultat attendu

Code :

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

1
2
3
4
5
6
7
8
IDMACHINE	DEBUT		FIN
1		15/02/05	01/10/05
1		01/11/05	15/03/06
2		15/02/05	15/02/05
2		01/03/05	01/08/05
2		01/09/05	01/10/05
2		01/11/05	01/03/06
2		15/03/06	15/03/06

Les lignes (2; 15/02/05; 15/02/05) et (2; 15/03/06; 15/03/06) permettent de calculer les périodes au début et à la fin de l'intervalle de recherche

Pour obtenir les périodes où les machines ne sont pas utilisées, je remplace simplement la dernière ligne de la requête précédente (SELECT * FROM bx) par :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT a.IdMachine, a.fin, b.debut
FROM bx a INNER JOIN bx b 
                  ON a.IdMachine = b.IdMachine
                 AND b.debut = (SELECT MIN(debut)
                                FROM bx c
                                WHERE c.IdMachine = a.IdMachine
                                  AND c.debut > a.fin)
WHERE a.fin < to_date('15/03/2006',   'DD/MM/YYYY')
  AND a.debut >= to_date('15/02/2005',   'DD/MM/YYYY');

Mais là, au lieu d'obtenir le résultat attendu, j'obtiens :

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
IDMACHINE	FIN		DEBUT
1		01/01/06	01/02/06
1		15/02/05	01/04/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
1		01/10/05	01/11/05
2		01/02/05	15/02/05
2		15/02/05	01/03/05
2		01/08/05	01/09/05
2		01/08/05	01/09/05
2		01/10/05	01/11/05
2		01/02/05	15/02/05
2		01/03/06	15/03/06
2		01/03/06	15/03/06

Résultat où on peut voir des données qui n'existent pas dans la vue bx !

Si je crée une table à partir de la requête N° 1 :
Create table bx as
...
select * from bx

Puis que j'utilise le petit bout de requête suivant (le même que le précédent, bien sur)

Code :

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

1
2
3
4
5
6
7
8
9
SELECT a.IdMachine, a.fin, b.debut
FROM bx a INNER JOIN bx b 
                  ON a.IdMachine = b.IdMachine
                 AND b.debut = (SELECT MIN(debut)
                                FROM bx c
                                WHERE c.IdMachine = a.IdMachine
                                  AND c.debut > a.fin)
WHERE a.fin < to_date('15/03/2006',   'DD/MM/YYYY')
  AND a.debut >= to_date('15/02/2005',   'DD/MM/YYYY');

Alors j'obtiens bien le bon résultat :

Code :

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

1
2
3
4
5
6
IDMACHINE	FIN		DEBUT
1		01/10/05	01/11/05
2		15/02/05	01/03/05
2		01/08/05	01/09/05
2		01/10/05	01/11/05
2		01/03/06	15/03/06

Je trouve bizarre que mes deux requêtes celle avec la factoring_clause et celle avec une table ne donne pas le même résultat.
Me suis-je planté quelque part ?

Je voudrais ajouter deux remarques :

1) je ne me préoccupe pas de l'inclusion ou non des bornes (je verrai plus tard)
2) je ne cherche pas une solution alternative, mais à comprendre pourquoi la solution précédente avec factoring_clause ne fonctionne pas.

Merci d'être arrivé jusque là.

**media** · 21/08/2006, 22h09

Je n'ai pas de chance avec ma première question sur ce forum

.

Est-ce que j'ai fait quelque chose de mal ; il m'a semblé que je suivais toutes les règles de ce forum pourtant ?

Est que les explications ne sont pas claires ?

Est-ce que la question est trop complexe ?

**remi4444** · 22/08/2006, 23h00

Vu la complexité des requêtes que tu soumets, il faut prendre beaucoup de temps pour tout decortiquer, tout le monde est bénévole ici, il ne faut pas l'oublier... Peut-etre pourrais tu simplifier pas à pas tes requêtes afin de trouver le cas le plus simple possible ou ton problème se reproduit ?

Remarque:

Quand j'ai des des problèmatiques de ce type à coder, j'emploie une méthode différente:

Je met sur une meme colonne les date de début et les dates de fin en associant "+1" pour une date de début et "-1" pour une date de fin. Je fait ensuite un tri général puis des sommes cumulatives de mes indicateurs. A chaque fois que ma somme est à 0, c'est que je suis dans une période d'innactivité.

**media** · 23/08/2006, 10h34

Envoyé par remi4444

Vu la complexité des requêtes que tu soumets, il faut prendre beaucoup de temps pour tout decortiquer, tout le monde est bénévole ici, il ne faut pas l'oublier...

Je ne l'oublie pas, mais en l'absence de toute réaction (même pas de question) je m'inquiétais, en particulier sur la clarté de mes explications.

Envoyé par remi4444

Peut-etre pourrais tu simplifier pas à pas tes requêtes afin de trouver le cas le plus simple possible ou ton problème se reproduit ?

Malheureusement c'est déjà ce que j'ai fait, en présentant une seule table ne contenant que les données nécessaires, et en utilisant les factoring_clause qui reviennent à travailler pas à pas (au départ j'ai adopté cette technique pour faciliter le travail de ceux qui feront la maintenance derrière moi).

Pour tes remarques :
1) je ne peux pas changer la modélisation (et simuler ta modélisation avec une factoring_clause ne rendrait guère les choses plus lisibles

).
2) ce qui m'intéresse c'est de trouver la raison de l'erreur, non de trouver une autre solution (cela marche très bien en déclarant les factoring_clause comme des vues, mais elles ne seront utilisées que là, d'où l'idée d'utiliser les factoring_clause plutôt que des vues).

**media** · 30/08/2006, 13h47

Après une semaine sur le forum ORACLE sans réponse constructive, ma conclusion est qu'il s'agit d'un bug ORACLE... J'aurais préféré avoir fait une erreur.

[Factoring_Clause]Problème dans une requête

Oracle

Discussions similaires

Partager

Partager