Home » RDBMS Server » Performance Tuning » Tuning a small query (Oracle 10g)
Tuning a small query [message #360224] Thu, 20 November 2008 02:02 Go to next message
magesh_magi1
Messages: 4
Registered: November 2008
Junior Member
Hi All,

Please help me in Tuning a simple query

Select * from tab1@dblink
where tab1.col1 in ( selct col1 from tab2)


Tab2 is having very less number of records where as Tab1 is having millions of records.
Index is not there in col1 in both the tables where as index is there for some other columns

We cannot join the tables, we may have duplicates in tab2

Tried some Hints but with no Result.

Thanks,
Mag
Re: Tuning a small query [message #360228 is a reply to message #360224] Thu, 20 November 2008 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Select /*+ driving_site(tab1) */ * from tab1@dblink
where tab1.col1 in ( select col1 from tab2)

Regards
Michel

[Updated on: Thu, 20 November 2008 02:10]

Report message to a moderator

Re: Tuning a small query [message #360235 is a reply to message #360228] Thu, 20 November 2008 02:26 Go to previous messageGo to next message
magesh_magi1
Messages: 4
Registered: November 2008
Junior Member
Hi Michel,

Thanks for the reply.
So far, There is no drastic change has happened for the query .
In the Hint can we give the table name with DBLINK.
Re: Tuning a small query [message #360246 is a reply to message #360235] Thu, 20 November 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In the Hint can we give the table name with DBLINK.

You have to, this is what I posted.

Regards
Michel
Re: Tuning a small query [message #360255 is a reply to message #360246] Thu, 20 November 2008 03:51 Go to previous messageGo to next message
magesh_magi1
Messages: 4
Registered: November 2008
Junior Member
Hi michel,
Please find the attachment..
i shown the difference between two Queries
one with driving_site
and other without..
Can you make anything out of it ?

[Updated on: Thu, 20 November 2008 03:58]

Report message to a moderator

Re: Tuning a small query [message #360280 is a reply to message #360255] Thu, 20 November 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know as I don't know if you correctly used the hint.

Regards
Michel
Re: Tuning a small query [message #360285 is a reply to message #360280] Thu, 20 November 2008 05:02 Go to previous message
magesh_magi1
Messages: 4
Registered: November 2008
Junior Member
This is how i formed the query

SELECT /*+ driving_site(A) */ count(*) FROM CCS_file_note@EDCMFGP1 A
WHERE CCS_CLAIM_ID IN (SELECT CCS_CLAIM_ID FROM CCS_LOSS_CLAIM_EVENT_B B)

SELECT * FROM CCS_file_note@EDCMFGP1 A
WHERE CCS_CLAIM_ID IN (SELECT CCS_CLAIM_ID FROM CCS_LOSS_CLAIM_EVENT_B)

Hope this helps you.
Previous Topic: Querry performance
Next Topic: Generic data models - academic questions
Goto Forum:
  


Current Time: Sun Dec 04 14:56:10 CST 2016

Total time taken to generate the page: 0.28361 seconds