Dear All,

I would like to find out if this sql can be tuned:

>>>>>
SELECT a.rowid, a.dm_ind
from dt_company_contact_person a, dt_company_contact_person@to_v3seq b
where a.code_office_created_by = b.code_office_created_by and
a.id_cont_person = b.id_cont_person and
a.dm_ind = 'Y' and
b.dm_ind = 'N';
<<<<<

For the local table, this is the count result:

>>>>>
SQL> select count(*), dm_ind from dt_company_contact_person group by dm_ind
2 ;

COUNT(*) D
---------- -
1062 N
2193119 Y

<<<<<


For the remote table, this is the count result:

>>>>>

COUNT(*) D
---------- -
1056 N
2194648 Y
<<<<<


For my sql statement, it is doing full table scan even though I have the index on code_office_created_by, id_cont_person.

>>>>>
ID pid OPTIMIZE OPERATION OPTIONS OBJECT_NAME
---- ---- -------- -------------------- -------------------- ---------------------------
0 CHOOSE SELECT STATEMENT
1 0 HASH JOIN
2 1 ANALYZED TABLE ACCESS FULL DT_COMPANY_CONTACT_PERSON
3 1 REMOTE <<<<<

Actually, the result set got from the remote table is small. Is there any way I can avoid the full table scan on the local table?

Thanks,

-- Chris