Help optimize statement

From: <adm-unix_at_eramet-sln.nc>
Date: Tue, 13 Feb 2001 16:45:25 -0800
Message-ID: <F001.002B33F9.20010213163527@fatcity.com>

Hello,

     I have this user statement below which is very long in a 8.1.6.2 database.
Any ideas to optimize it is wellcome.

Best Regards
Philippe

---

SELECT
  PLT_LISTE_DES_AGENTS_PL97.SEXE,

  PLT_FORMATION_AGENT_PL74.PL0PL009_COLLEGE,
  count(PLT_FORMATION_AGENT_PL74.PL97_MATRICULE),
  PLT_FORMATION_AGENT_PL74.DUR_TOT

FROM

  PLT_LISTE_DES_AGENTS_PL97,
  PLT_FORMATION_AGENT_PL74,
  PLT_FORMATION_ACTION_PL73

WHERE
  (PLT_LISTE_DES_AGENTS_PL97.MATRICULE = PLT_FORMATION_AGENT_PL74.PL97_MATRICULE)   AND (PLT_FORMATION_ACTION_PL73.ACTION = PLT_FORMATION_AGENT_PL74.PL73_ACTION and   PLT_FORMATION_ACTION_PL73.INDICE = PLT_FORMATION_AGENT_PL74.PL73_INDICE)   AND (
  PLT_LISTE_DES_AGENTS_PL97.SEXE = 'M'   AND PLT_FORMATION_AGENT_PL74.COD_STAGIAIRE = 'O' ((INDEX CREATED)   AND PLT_FORMATION_ACTION_PL73.NO_ACTION LIKE 'F0%' ((PRIMARY KEY°   )
GROUP BY

  PLT_LISTE_DES_AGENTS_PL97.SEXE,
  PLT_FORMATION_AGENT_PL74.PL0PL009_COLLEGE,
  PLT_FORMATION_AGENT_PL74.DUR_TOT

ORDER BY
  PLT_LISTE_DES_AGENTS_PL97.SEXE,
  PLT_FORMATION_AGENT_PL74.PL0PL009_COLLEGE /

explain plan :**************************************
STATEMENT=MRrepFor                      TIMESTAMP= 12/02/01
OPERATION=SORT                          OPTION=  ORDER BY



                      ID=  1       parentID=   0        POSITION=  1

MRrepFor                       12/02/01

SORT                           GROUP BY



                        2          1          1


MRrepFor                       12/02/01

NESTED LOOPS

                        3          2          1

MRrepFor                       12/02/01

NESTED LOOPS

                        4          3          1


MRrepFor                       12/02/01
TABLE ACCESS                   FULL

OBJECTOWNER=BOCONFID                      OBJECTNAME= PLT_FORMATION_AGENT_PL74         
            2


                        5          4          1

MRrepFor                       12/02/01
TABLE ACCESS                   BY INDEX ROWID

BOCONFID                       PLT_LISTE_DES_AGENTS_PL97                    1


                        6          4          2



MRrepFor                       12/02/01
INDEX                          UNIQUE SCAN

BOCONFID                       PL97_PK                                        UNIQUE

                        7          6          1



MRrepFor                       12/02/01
TABLE ACCESS                   BY INDEX ROWID

BOCONFID                       PLT_FORMATION_ACTION_PL73                    3

                        8          3          2


MRrepFor                       12/02/01
INDEX                          RANGE SCAN

BOCONFID                       PL73_PK                                        UNIQUE

                        9          8          1



MRrepFor                       12/02/01

SELECT STATEMENT CHOOSE

                        0

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: adm-unix_at_eramet-sln.nc

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Feb 13 2001 - 18:45:25 CST