Home » RDBMS Server » Performance Tuning » URGENT:-Full table scan
URGENT:-Full table scan [message #65366] Wed, 25 August 2004 01:06 Go to next message
Milind Deshpande
Messages: 93
Registered: May 2004
Member
Dear Friends,

 I have an Sql Statement in which Two tables are undergoing Full Table scan. I dont know what is the reason for that as they are having indexes on them and I have provided the hint in the query.


SELECT

/*+ INDEX(TDI_PARTICIPANT TDI_PARTICIPANT_PK) */
/*+ INDEX(MV_INDIVIDUAL_ADDRESS MV_IND_ADDR_TYPE) */
/*+ INDEX(MV_CUSTOMER MV_CUST_ID_NAME) */

DISTINCT
IND.INDIVIDUAL_ID IndividualId ,
IND.FIRST_NAME FirstName ,
IND.LAST_NAME LastName ,
CUS.NAME CustomerName ,
INA.address_line_1 Addr1 ,
INA.address_line_2 Addr2 ,
INA.city City ,INA.state State ,
INA.zip_code Zip ,
INA.country Country ,
IND.EMAIL_ADDRESS Email ,
IND.PHONE_NUMBER Phone ,
IND.FAX_NUMBER Fax

FROM ARV_ENTITY_DI EDI ,
ARV_DI_PARTICIPANT DIP ,
AV_INDIVIDUAL IND ,
AV_INDIVIDUAL_ADDRESS INA ,
AV_CUSTOMER CUS

WHERE EDI.ENTITY_ID = 1800002 AND
EDI.DI_ID = DIP.DI_ID AND
DIP.PARTICIPANT_ID = IND.INDIVIDUAL_ID AND
IND.INDIVIDUAL_ID = INA.INDIVIDUAL_ID AND
IND.CUSTOMER_ID = CUS.CUSTOMER_ID AND
INA.address_type = 'Mailing'

Group By IND.INDIVIDUAL_ID ,
IND.FIRST_NAME ,
IND.LAST_NAME ,
CUS.NAME ,
INA.address_line_1 ,
INA.address_line_2 ,
INA.city ,
INA.state ,
INA.zip_code ,
INA.country ,
IND.EMAIL_ADDRESS ,
IND.PHONE_NUMBER ,
IND.FAX_NUMBER

In this query I think this condition is causing Full table scan for two tables as they reside in different schema.

DIP.PARTICIPANT_ID = IND.INDIVIDUAL_ID

Can anybody help me on this why the IND and DIP tables are undergoing Full table scans.

Thanks in Advance.

Milind.
Re: URGENT:-Full table scan [message #65367 is a reply to message #65366] Wed, 25 August 2004 02:43 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Your hints look invalid to me! Try:

/*+ INDEX(DIP index_name) INDEX(IND index_name) INDEX(...


For more details see chapter 5 of the "Oracle9i Database Performance Tuning Guide and Reference" Guide.

Best regards.

Frank
Previous Topic: BUFFER SORT
Next Topic: Hardware purchase decision
Goto Forum:
  


Current Time: Thu Mar 28 11:53:57 CDT 2024