Can this sql be tuned?

[cleegt]

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

[slimdave]

It sounds like what you need to happen is for the small set of remote rows meeting the dm_ind = 'N' condition to be brought to the local database, then you want an index lookup against the local table, right?

So the hint Driving_Site(a) will make sure that the remore rows are brought to the local database for the join, not the other wayaround. You can also try the hinting with Leading(b) to make the remote table the first in the join order.

So, try ...

SELECT /*+ Driving_Site(a) Leading (b) */
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';

... and let us know how it goes.

[abhaysk]

Either one of the tables (remote or local) will go for FTS.
may be index on dm_ind (bit map) on remote with driving table local will avoid you FTS.

[slimdave]

You'd probably only be interested in indexing the "N" values-- including the "Y"'s in the index would be a waste of time.

You can do this with a function-based index, by ..

create index
   my_index
on dt_company_contact_person 
   (Decode(dm_ind,'N','N',Null));

You would then access the indexed "N" values by querying ..

Where
   Decode(dm_ind,'N','N',Null) = 'N'