Je cherche a optimiser ce petit script sql qui ne finit plus. La table contient environ 2 400 000 lignes. Le ficher résultant devait avoir un peu plus de 2 000 000 de ligne (avec les regroupements)

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
29
30
31
32
33
34
35
36
37
38
39
40
41
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET VERIFY OFF
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 32000
SET LONG 32000
SET LONGCHUNKSIZE 30000
set recsep off
COL ligne FORMAT A32000
set termout off
SET ECHO OFF
 
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT OSCODE
 
spool $HQ_TMPPATH/gp58u_sm_cirelationship.csv
 
alter session set nls_sort='BINARY';
alter session set nls_comp='BINARY';
 
select '"RELATIONSHIP_SUBTYPE","RELATIONSHIP_TYPE","OUTAGE_DEPENDENCY","OUTAGE_THRESHOLD","RELATIONSHIP_NAME","LOGICAL_NAME","DATASOURCE","RELATED_CIS"' from dual;
 
SELECT    '"' || RELATIONSHIP_SUBTYPE
          ||'","' || RELATIONSHIP_TYPE
          ||'","' || OUTAGE_DEPENDENCY
          ||'","' || OUTAGE_THRESHOLD
          ||'","' || RELATIONSHIP_NAME
          ||'","' || LOGICAL_NAME
          ||'","' || DATASOURCE
          ||'","' || rtrim(xmlagg(xmlelement(e,RELATED_CIS,CHR(13) ||  CHR(10)).extract('//text()') order by rowid).GetClobVal(),CHR(13) ||  CHR(10) )
||'"' AS LIGNE
FROM OUTIL.XCOM_CIRELATION
GROUP BY RELATIONSHIP_SUBTYPE, RELATIONSHIP_TYPE, OUTAGE_DEPENDENCY, OUTAGE_THRESHOLD, RELATIONSHIP_NAME, LOGICAL_NAME, DATASOURCE
ORDER BY RELATIONSHIP_NAME || '!' || LOGICAL_NAME || '!'  ;
 
spool off
 
quit