Is it possible to optimise this request without creating any more indexes.
table tsd_uc_aff : 11000 rows
primary key :
PK_TSD_UC_AFF ON (CODE_UC_AFF, VERSION_UC_AFF)~
~~~~~~~~~~~~~~~~
table tsd_fait_paye_detail : 939474639 rows
no indexes on columns:
code_cep ,
code_qualif,
code_classe_perso
Primary key :
PK_TSD_FAIT_PAYE_DETAIL ON (MOIS_COMPTABLE, EXERCICE_COMPTABLE, NUMERO_CP, CODE_COMPTE, CPLT_COMPTE, CODE_UC_EMET, VERSION_UC_EMET, CODE_UC_AFF_PAYE, CPLT_UC_AFF_PAYE, VERSION_UC_AFF_PAYE, CODE_JOURNAL)
/* Formatted on 2007/06/21 09:27 (Formatter Plus v4.8.8) */
SELECT DISTINCT tfpd.exercice_comptable
|| ';'
|| tfpd.mois_comptable
|| ';'
|| DECODE (SUBSTR (tua.code_domaine, 1, 2),
'EC', 'FT',
tua.code_domaine
)
|| ';'
|| tua.code_etab
|| ';'
|| TO_CHAR (SUM (tfpd.solde_euro), '0999999999999.99')
FROM (SELECT code_uc_aff, MAX (version_uc_aff) AS nomaxuc
FROM tsd_uc_aff
GROUP BY code_uc_aff) maxi,
tsd_fait_paye_detail tfpd,
tsd_uc_aff tua
WHERE tfpd.code_uc_aff_paye = tua.code_uc_aff
AND tfpd.code_uc_aff_paye = maxi.code_uc_aff
AND tfpd.version_uc_aff_paye = maxi.nomaxuc
AND tfpd.version_uc_aff_paye = tua.version_uc_aff
AND tfpd.code_cep = '64'
AND tfpd.code_qualif IN ('C', 'D_XS')
AND tfpd.code_classe_perso = '1'
AND ( tfpd.exercice_comptable = 2005
OR tfpd.exercice_comptable = 2006
OR tfpd.exercice_comptable = 2007
)
GROUP BY tfpd.exercice_comptable,
tfpd.mois_comptable,
DECODE (SUBSTR (tua.code_domaine, 1, 2),
'EC', 'FT',
tua.code_domaine
),
tua.code_etab
Explain plan :
Plan
SELECT STATEMENT CHOOSECost: 468 K Bytes: 3 M Cardinality: 89 K
11 SORT UNIQUE Cost: 468 K Bytes: 3 M Cardinality: 89 K
10 SORT GROUP BY Cost: 468 K Bytes: 3 M Cardinality: 89 K
9 VIEW SYS. Cost: 466 K Bytes: 7 M Cardinality: 246 K
8 FILTER
7 SORT GROUP BY Cost: 466 K Bytes: 15 M Cardinality: 246 K
6 HASH JOIN Cost: 450 K Bytes: 170 M Cardinality: 3 M
1 INDEX FAST FULL SCAN UNIQUE PROP_SICD.PK_TSD_UC_AFF Cost: 6 Bytes: 65 K Cardinality: 11 K
5 HASH JOIN Cost: 450 K Bytes: 41 M Cardinality: 712 K
2 TABLE ACCESS FULL PROP_SICD.TSD_UC_AFF Object Instance: 4 Cost: 93 Bytes: 248 K Cardinality: 11 K
4 PARTITION RANGE INLIST Partition #: 10 Partitions accessed #KEY(INLIST)
3 TABLE ACCESS FULL PROP_SICD.TSD_FAIT_PAYE_DE
TAIL Object Instance: 3 Cost: 449 K Bytes: 25 M Cardinality: 712 K Partition #: 10 Partitions accessed #KEY(INLIST)