- For individual users
- Instant access to solutions
- Ask your tech questions
- Start your 30-day Free Trial
Main Topics
Browse All TopicsIs 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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Business Accounts
Answer for Membership
by: jrb1Posted on 2007-06-21 at 11:32:35ID: 19335687
This might be a bit better, but why the distinct? Are you getting duplicates for some reason?
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 tsd_fait_paye_detail tfpd,
tsd_uc_aff tua
WHERE version_uc_aff = (select max(version_uc_aff) from tsd_uc_aff where code_uc_aff = tua.code_uc_aff)
AND tfpd.code_uc_aff_paye = tua.code_uc_aff
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