Home » RDBMS Server » Performance Tuning » URGET:-Query not performing well
URGET:-Query not performing well [message #65379] Tue, 31 August 2004 02:36 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member


Dear Friends,

I have this query which is not performing well on the database.It is taking lot of time to execute. I have created indexes in order to avoid FUll Table Scans.

Actually, the query uses Views and the definition of the view contains the tables which are undergoing full table scan in the Explain Plan.

select

/*+ INDEX(GDS.TINDIVIDUAL_KEYS GDS.TIND_INDID_OWORID) */

/*+ INDEX(TADDRESS_KEYS JDEDTA.TADD_ADDID_ONWOID) */

--Select DIs other than SOP DI
--SOP DI is fetched seperately by CTA.CROW.DeliveryInstructionSOP
EDI.DI_ID DIID,
EDI.DI_TYPE_CD DITypeID,
DITYPE.DI_TYPE DIType,
DIPA.PARTICIPANT_ID ParticipantID,
IND.FIRST_NAME FirstName,
IND.MIDDLE_INITIAL MiddleInitial,
IND.LAST_NAME LastName,
IND.EMAIL_ADDRESS Email,
INDADDRESS.ADDRESS_LINE_1 Address1,
INDADDRESS.ADDRESS_LINE_2 Address2,
INDADDRESS.ADDRESS_LINE_3 Address3,
INDADDRESS.ADDRESS_LINE_4 Address4,
INDADDRESS.CITY City,
INDADDRESS.STATE State,
INDADDRESS.ZIP_CODE ZipCode
from
ARV_ENTITY_DI EDI,
ARV_DI_PARTICIPANT DIPA,
AV_INDIVIDUAL IND,
AV_INDIVIDUAL_ADDRESS INDADDRESS,
ARV_DI_TYPE DITYPE
where
EDI.ENTITY_ID = 8004065457 and
EDI.DI_TYPE_CD != 11004 and
EDI.DI_ID = DIPA.DI_ID and
EDI.DI_TYPE_CD = DITYPE.DI_TYPE_CD and
DIPA.PARTICIPANT_ID = IND.INDIVIDUAL_ID and
DIPA.PARTICIPANT_ID = INDADDRESS.INDIVIDUAL_ID

What should i do to Tune this query.

Please help.Thanks in Advance.

Milind

 
Re: URGET:-Query not performing well [message #65410 is a reply to message #65379] Sat, 11 September 2004 10:20 Go to previous message
Diego Muñoz
Messages: 4
Registered: September 2004
Junior Member
Firts You must review the definition of the views ... this views joint another tables? This joint is optimized?

Second, try to descompose the select , selecting the base tables (of the views) if you can ...

God luck

Diego
Previous Topic: SGA sizing
Next Topic: Statspack-Urgent
Goto Forum:
  


Current Time: Fri Mar 29 08:03:27 CDT 2024