Ayuda optimizacion de query, error tablespace temporal
Publicado por Estefania (1 intervención) el 27/09/2011 16:35:30
Buenas tardes, estoy ejecutando una query en Oracle, y al llevar dos group by peta el tablespace temporal, la query y el explain plan es el siguiente, si me pudierais ayudar os estaria muy agradecida.
SELECT a.family, a.type0, a.group0, b.proposito proposito,
a.isincode isincode, c.folder1 folder, a.sourcesystem sourcesystem,
MAX (nominalb) nominalb, MAX (nominals) nominals,
MAX (nominalb) - MAX (nominals) restanom, MAX (nomsecb) nomsecb,
MAX (nomsecs) nomsecs, MAX (nomsecb) - MAX (nomsecs) restasec,
TO_CHAR (MIN (TO_DATE (tradedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) tradedate,
TO_CHAR (MAX (TO_DATE (valuedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) maxvaluedate,
TO_CHAR (MIN (TO_DATE (valuedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) minvaluedate,
TO_CHAR (MAX (TO_DATE (expirydate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) expirydate,
TO_CHAR (MAX (TO_DATE (settlementdate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) maxsettlementdate,
SUM (marketvalue) summv1,
MAX ((DECODE (a.dirtyprice, 0, 100, a.dirtyprice))) dirtyprice,
a.isincode, cptyissuer, sectorissuer, nominalcurr, bsh
FROM hubowner.hubtmp_bra_mx_bon a,
hubowner.hub_folderkgl b,
(SELECT DECODE (b.proposito,
'Trading', 'BRATRAEMI',
b.folder
) folder,
folder folder1, isincode,
DECODE ((sellbuy),
'B', SUM (ABS (TO_NUMBER (nominal))),
0
) nominalb,
DECODE ((sellbuy),
'S', SUM (ABS (TO_NUMBER (nominal))),
0
) nominals,
DECODE ((sellbuy),
'B', SUM ( ABS (TO_NUMBER (nominal))
* TO_NUMBER (securitylotsize)
),
0
) nomsecb,
DECODE ((sellbuy),
'S', SUM ( ABS (TO_NUMBER (nominal))
* TO_NUMBER (securitylotsize)
),
0
) nomsecs
FROM hubowner.hubtmp_bra_mx_bon a, hubowner.hub_folderkgl b
WHERE bsh = 'KGL_MUREX_BRA_BONDS_GROUP'
AND ID = '$317130412555'
AND a.portfolio = b.portfolio
GROUP BY isincode,
cptyissuer,
sectorissuer,
nominalcurr,
dirtyprice,
sellbuy,
folder,
DECODE (b.proposito, 'Trading', 'BRATRAEMI', b.folder),
bsh) c
WHERE bsh = 'KGL_MUREX_BRA_BONDS_GROUP'
AND ID = '$317130412555'
AND a.portfolio = b.portfolio
AND c.folder1 = b.folder
AND c.folder = DECODE (b.proposito, 'Trading', 'BRATRAEMI', b.folder)
AND a.isincode = c.isincode
GROUP BY a.family,
a.type0,
a.group0,
a.isincode,
a.cptyissuer,
a.sectorissuer,
a.nominalcurr,
a.dirtyprice,
a.sourcesystem,
b.proposito,
b.folder,
c.folder1,
bsh
HAVING MAX (nominalb) - MAX (nominals) <> 0
Q_PLAN
--------------------------------------------------------------------------------
FILTER
HASH GROUP BY
VIEW VM_NWVW_1
HASH GROUP BY
HASH JOIN
HASH JOIN
HASH JOIN
TABLE ACCESS FULL HUBTMP_BRA_MX_BON
TABLE ACCESS FULL HUBTMP_BRA_MX_BON
TABLE ACCESS FULL HUB_FOLDERKGL
TABLE ACCESS FULL HUB_FOLDERKGL
SELECT a.family, a.type0, a.group0, b.proposito proposito,
a.isincode isincode, c.folder1 folder, a.sourcesystem sourcesystem,
MAX (nominalb) nominalb, MAX (nominals) nominals,
MAX (nominalb) - MAX (nominals) restanom, MAX (nomsecb) nomsecb,
MAX (nomsecs) nomsecs, MAX (nomsecb) - MAX (nomsecs) restasec,
TO_CHAR (MIN (TO_DATE (tradedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) tradedate,
TO_CHAR (MAX (TO_DATE (valuedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) maxvaluedate,
TO_CHAR (MIN (TO_DATE (valuedate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) minvaluedate,
TO_CHAR (MAX (TO_DATE (expirydate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) expirydate,
TO_CHAR (MAX (TO_DATE (settlementdate, 'DD/MM/YYYY')),
'DD/MM/YYYY'
) maxsettlementdate,
SUM (marketvalue) summv1,
MAX ((DECODE (a.dirtyprice, 0, 100, a.dirtyprice))) dirtyprice,
a.isincode, cptyissuer, sectorissuer, nominalcurr, bsh
FROM hubowner.hubtmp_bra_mx_bon a,
hubowner.hub_folderkgl b,
(SELECT DECODE (b.proposito,
'Trading', 'BRATRAEMI',
b.folder
) folder,
folder folder1, isincode,
DECODE ((sellbuy),
'B', SUM (ABS (TO_NUMBER (nominal))),
0
) nominalb,
DECODE ((sellbuy),
'S', SUM (ABS (TO_NUMBER (nominal))),
0
) nominals,
DECODE ((sellbuy),
'B', SUM ( ABS (TO_NUMBER (nominal))
* TO_NUMBER (securitylotsize)
),
0
) nomsecb,
DECODE ((sellbuy),
'S', SUM ( ABS (TO_NUMBER (nominal))
* TO_NUMBER (securitylotsize)
),
0
) nomsecs
FROM hubowner.hubtmp_bra_mx_bon a, hubowner.hub_folderkgl b
WHERE bsh = 'KGL_MUREX_BRA_BONDS_GROUP'
AND ID = '$317130412555'
AND a.portfolio = b.portfolio
GROUP BY isincode,
cptyissuer,
sectorissuer,
nominalcurr,
dirtyprice,
sellbuy,
folder,
DECODE (b.proposito, 'Trading', 'BRATRAEMI', b.folder),
bsh) c
WHERE bsh = 'KGL_MUREX_BRA_BONDS_GROUP'
AND ID = '$317130412555'
AND a.portfolio = b.portfolio
AND c.folder1 = b.folder
AND c.folder = DECODE (b.proposito, 'Trading', 'BRATRAEMI', b.folder)
AND a.isincode = c.isincode
GROUP BY a.family,
a.type0,
a.group0,
a.isincode,
a.cptyissuer,
a.sectorissuer,
a.nominalcurr,
a.dirtyprice,
a.sourcesystem,
b.proposito,
b.folder,
c.folder1,
bsh
HAVING MAX (nominalb) - MAX (nominals) <> 0
Q_PLAN
--------------------------------------------------------------------------------
FILTER
HASH GROUP BY
VIEW VM_NWVW_1
HASH GROUP BY
HASH JOIN
HASH JOIN
HASH JOIN
TABLE ACCESS FULL HUBTMP_BRA_MX_BON
TABLE ACCESS FULL HUBTMP_BRA_MX_BON
TABLE ACCESS FULL HUB_FOLDERKGL
TABLE ACCESS FULL HUB_FOLDERKGL
Valora esta pregunta
0