| URGENT:-Full table scan [message #65366] |
Wed, 25 August 2004 01:06  |
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  |
Frank Naude
Messages: 4587
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
