Bonjour,

Je travaille sur des bases ORACLE 10i.
Dans mon script d'extraction, j'ai rajouté cette requête SQL.
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
 
	SELECT DECODE(CLI.code_couverture,'BIEN',1,'TIER',2,' '),
		RPAD(NVL(SFAC.no_contrat_prestation,' '),10,' '),
		TO_CHAR(NVL(SFAC.mt_ht,0), '09999999999999999S'),
		TO_CHAR(NVL(SFAC.mt_ttc,0), '09999999999999999S'), 
		RPAD(NVL(SIMP.reference_externe,' '),20,' ')
	FROM (
		SELECT no_contrat_prestation, mt_ht, mt_ttc, tiers_prestataire, id_simpres, id_affaire, id_element,
			RANK() over (PARTITION BY id_element, id_clipres ORDER BY id_sfac_clipres DESC) rang	
		FROM SFAC_CLIPRES
		  ) sfac
	INNER JOIN CLIPRES cli ON (CLI.no_contrat_prestation = SFAC.no_contrat_prestation)
	INNER JOIN SIMPRES simp ON (SIMP.id_simpres = SFAC.id_simpres AND SIMP.code_contrat_prestation = '5')
	WHERE SFAC.rang = 1
		AND SFAC.id_element = idelement 
		AND SFAC.id_affaire = idaffaire
		AND ROWNUM <= 10
	ORDER BY SFAC.id_element, SFAC.id_simpres
	;
L'exécution me fait passer mes temps de traitements de 3minutes à 26 minutes.

Les indexs des tables sont :
SFAC_CLIPRES => ID_SFAC_CLIPRES
CLIPRES => ID_CLIPRES, NO_CONTRAT_PRESTATION
SIMPRES => ID_SIMPRES

Je ne peux effectuer aucune action sur la structure des tables.

Voici ce que j'ai récupéré en exécutant un explain plan de la requête sous sql/developer :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
OPERATION        OPTIONS          OBJECT_NAME  OBJECT_TYPE    OPTIMIZER   ID    COST    CPU_COST    IO COST ACCESS_PREDICATES                           FILTER_PREDICATES                                                                                                   TIME	PROJECTION
SELECT STATEMENT                                              ALL_ROWS    0   	2930 	245513949 	2915                                         																														36	
SORT             ORDER BY                                                 1   	2930 	245513949 	2915                                         																														36	(#keys=2) SFAC.ID_ELEMENT[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], DECODE(CLI.CODE_COUVERTURE,'BIEN',1,'TIER',2,TO_NUMBER(' '))[22], RPAD(NVL(SFAC.NO_CONTRAT_PRESTATION,' '),10,' ')[10], TO_CHAR(NVL(SFAC.MT_HT,0),'09999999999999999S')[18], TO_CHAR(NVL(SFAC.MT_TTC,0),'09999999999999999S')[18], RPAD(NVL(SIMP.REFERENCE_EXTERNE,' '),20,' ')[20]
COUNT            STOPKEY                                                  2   	     	          	                                                   	ROWNUM<=10																												SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4], SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
NESTED LOOPS                                                              3   	2929 	229561053 	2915                                         																														36	(#keys=0) SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4], SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
NESTED LOOPS                                                              4   	2928 	229556670 	2914                                         																														36	(#keys=0) SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4]
VIEW                                                                      5   	2927 	229552779 	2913                                             	SFAC.RANG=1																											36	SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], SFAC.ID_CLIPRES[NUMBER,22], SFAC.RANG[NUMBER,22]
WINDOW           SORT PUSHED RANK                                         6   	2927 	229552779 	2913                                             	RANK() OVER ( PARTITION BY ID_AFFAIRE,ID_ELEMENT,ID_CLIPRES ORDER BY INTERNAL_FUNCTION(ID_SFAC_CLIPRES) DESC )<=1	36	(#keys=4) ID_AFFAIRE[NUMBER,22], ID_ELEMENT[NUMBER,22], ID_CLIPRES[NUMBER,22], INTERNAL_FUNCTION(ID_SFAC_CLIPRES)[22], NO_CONTRAT_PRESTATION[VARCHAR2,10], MT_HT[NUMBER,22], ID_SIMPRES[NUMBER,22], MT_TTC[NUMBER,22], RANK() OVER ( PARTITION BY ID_AFFAIRE,ID_ELEMENT,ID_CLIPRES ORDER BY INTERNAL_FUNCTION(ID_SFAC_CLIPRES) DESC )[22]
TABLE ACCESS     FULL             SFAC_CLIPRES TABLE          ANALYZED    7   	2926 	213599884 	2913                                            	ID_AFFAIRE=1718201																									36	ID_SFAC_CLIPRES[NUMBER,22], ID_AFFAIRE[NUMBER,22], ID_ELEMENT[NUMBER,22], ID_CLIPRES[NUMBER,22], NO_CONTRAT_PRESTATION[VARCHAR2,10], MT_HT[NUMBER,22], MT_TTC[NUMBER,22], ID_SIMPRES[NUMBER,22]
TABLE ACCESS     BY INDEX ROWID   CLIPRES      TABLE          ANALYZED    8   	1    	88        	1                                            																														1	CLI.CODE_COUVERTURE[VARCHAR2,4]
INDEX            UNIQUE SCAN      UI_CLIPRES_1 INDEX (UNIQUE) ANALYZED    9   	1    	11        	1        CLI.ID_CLIPRES=SFAC.ID_CLIPRES  																																1	CLI.ROWID[ROWID,10]
TABLE ACCESS     BY INDEX ROWID   SIMPRES      TABLE          ANALYZED    10  	1    	100       	1                                               	SIMP.CODE_CONTRAT_PRESTATION='5'																					1	SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
INDEX            UNIQUE SCAN      UI_SIMPRES_1 INDEX (UNIQUE) ANALYZED    11  	1    	19        	1        SIMP.ID_SIMPRES=SFAC.ID_SIMPRES 																																1	SIMP.ROWID[ROWID,10]
Merci pour votre aide