Home » RDBMS Server » Performance Tuning » Query optimization
Query optimization [message #359602] Mon, 17 November 2008 09:57 Go to next message
prem18
Messages: 9
Registered: July 2008
Location: United States
Junior Member
Can anyone suggest me to optimize the below query? Its taking around 20 sec for 3500 rows

                           SELECT * FROM (SELECT A.CDE_ECR_VEMS,
                                                 A.IND_ECR_VEMSS_EMSN,
                                                 A.IND_ECR_VEMSS_CARB,
                                                 A.IND_ECR_CHPP_DEF,
                                                 A.IND_ECR_LPP_DEF,
                                                 D. IND_ECR_CHHP AS IND_TRAN_CHHP,
                                                 D.IND_ECR_PHPP AS IND_TRAN_PHPP,
                                                 A.IND_ECR_PART_ASMB,
                                                 A.CDE_ECR_VEMSS,
                                                 B.DTE_MODL_YR,
                                                 E.DES_MODL,
                                                 B.CDE_BAUM,
                                                 C.NUM_PART,
                                                 B.CDE_ECR_VEH_TYP,
                                                 A.DES_ECR_VEMSS_EXT,
                                                 COALESCE(C.AMT_ECR_PART_RETL,0) AS AMT_ECR_PART_RETL,
                                                 COALESCE(C.TXT_ECR_PART_COM,'') AS TXT_ECR_PART_COM,
                                                 COALESCE(D.CDE_STME_DSGN_GRP,'')||COALESCE(D.CDE_STME_OPER,'') AS OPCODE,
                                                 replace(substr(char(CAST(D.QTY_ECR_LBR_TME_UN AS DECIMAL(7,2))/10) ,4,4),'00.','0.')
                                                                                                                AS QTY_ECR_LBR_TME_UN,
                                                 COALESCE(D.TXT_ECR_LBR_COM,'') AS TXT_ECR_LBR_COM,
                                                 COALESCE(D.CDE_ECR_PART_CST,'') AS CDE_ECR_PART_CST,
                                                 ROWNUMBER() OVER (ORDER BY B.DTE_MODL_YR DESC) AS RN
                           FROM  ECRUSER.ECR_VEH_GRP B, ECRUSER.ECR_VEMSS A, ECRUSER.ECR_MODL_YR_PART C, ECRUSER.ECR_MODL_VEMSS_PART D,
                           ECRUSER.VEH_BAUM_REF E
                           WHERE A.DTE_MODL_YR = B.DTE_MODL_YR AND B.DTE_MODL_YR = C.DTE_MODL_YR AND C.DTE_MODL_YR = D.DTE_MODL_YR AND
                           D.DTE_MODL_YR = A.DTE_MODL_YR AND A.DTE_MODL_YR = E.DTE_MDL_YR AND B.CDE_BAUM = D.CDE_BAUM AND
                           B.CDE_ECR_VEH_TYP = D.CDE_ECR_VEH_TYP AND A.CDE_ECR_VEMSS = D.CDE_ECR_VEMSS AND C.NUM_PART = D.NUM_PART
                           AND B.CDE_BAUM = E.CDE_MODL_SERIES || E.CDE_MODL_ENGINE
                           AND A.DTE_MODL_YR BETWEEN V_DTE_MODL_YR_FROM AND V_DTE_MODL_YR_TO
                           AND D.CDE_ECR_VEMSS BETWEEN V_CDE_ECR_VEMSS_FROM AND V_CDE_ECR_VEMSS_TO AND
                           B.CDE_BAUM BETWEEN V_CDE_BAUM_FROM AND V_CDE_BAUM_TO AND
                           C.NUM_PART BETWEEN V_NUM_PART_FROM AND V_NUM_PART_TO)
                           AS RES WHERE RN BETWEEN V_REC_START_RANGE AND V_REC_END_RANGE;




Re: Query optimization [message #359604 is a reply to message #359602] Mon, 17 November 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why are there 25 spaces before each line? I'm sorry but I can't see the end of the lines.

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Query optimization [message #359653 is a reply to message #359604] Mon, 17 November 2008 19:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why don't you post the Explain Plan to give us a fighting chance.

Ross Leishman
Previous Topic: Wait events
Next Topic: Performance tuning for a simple table with 2million records
Goto Forum:
  


Current Time: Fri Dec 02 16:43:25 CST 2016

Total time taken to generate the page: 0.08160 seconds