OraFAQ Forum: SQL & PL/SQL » Tune statement that uses Database LINK

Home » SQL & PL/SQL » SQL & PL/SQL » Tune statement that uses Database LINK

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tune statement that uses Database LINK [message #10732]

Thu, 12 February 2004 22:38

shad
Messages: 50
Registered: October 2000

Member

Hi all.

�

Hi have the following statement running that t I would like to tune.

select count(rowid)
from claims.post��
where employer_ref_no in (select ref_no��
from employers@dev.world
where� sic_code = '1000'�� and employ_end_date is not null)

�

Here is the explain plan for the statement

ID OPERATION�� OBJECT_NAME�� OPTIONS�� COST�� POSITION
------------------------------ ---------- ----------
0 SELECT STATEMENT�� 278311�� 278311
1 SORT��AGGREGATE�� 1
2 HASH JOIN�� 278311�� 1
3 REMOTE�� 8734�� 1
4 TABLE ACCESS�� POSt�� FULL�� 52870�� 2
�

--[>] There is an index on on the employer_ref_no field on dev database. As can be seen I am accessing the�employers table on dev �database from cclm database. the claims schema is in cclm database.

�

--[>] Is there any thing I can do to avoid full table scan on the post table as it is very big. It does not use an index that exists.

�

thanks

shad

�

Report message to a moderator

Re: Tune statement that uses Database LINK [message #10733 is a reply to message #10732]

Thu, 12 February 2004 23:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Why are you using a 'IN' instead of an equi join?

Select count(p.rowid) c
  From claims.post p
     , employers@dev.world e
 Where p.employer_ref_no = e.ref_no
   And e.sic_code = '1000'
   And e.employ_end_date Is Not Null

You could analyze the tables to make sure all indexes and stats are up to date. Then the CBO'll use the index if it's useful.

MHE

Report message to a moderator

Re: Tune statement that uses Database LINK [message #10735 is a reply to message #10733]

Fri, 13 February 2004 01:45

shad
Messages: 50
Registered: October 2000

Member

thanks.

It works very well.

Report message to a moderator

Re: Tune statement that uses Database LINK [message #10736 is a reply to message #10735]

Fri, 13 February 2004 03:42

pravin kadam
Messages: 1
Registered: February 2004

Junior Member

i would like to know about oracle database and DBA. Kindly forward me the list of institute which is provided training about DBA.

I expecting your positive reply.

Thank

Report message to a moderator

Previous Topic:	How to divide 2 select statement, both have multible rows
Next Topic:	Write SQL with SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 02:09:13 CDT 2024