OraFAQ Forum: Performance Tuning » Tuning a small query

Home » RDBMS Server » Performance Tuning » Tuning a small query (Oracle 10g)

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

Tuning a small query [message #360224]

Thu, 20 November 2008 02:02

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

Report message to a moderator

Re: Tuning a small query [message #360228 is a reply to message #360224]

Thu, 20 November 2008 02:10

Michel Cadot
Messages: 68665
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

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.

Report message to a moderator

Re: Tuning a small query [message #360246 is a reply to message #360235]

Thu, 20 November 2008 03:23

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Tuning a small query [message #360255 is a reply to message #360246]

Thu, 20 November 2008 03:51

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 ?

Attachment: driving site PT.csv
(Size: 14.50KB, Downloaded 1213 times)

[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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: Tuning a small query [message #360285 is a reply to message #360280]

Thu, 20 November 2008 05:02

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.

Report message to a moderator

Previous Topic:	Querry performance
Next Topic:	Generic data models - academic questions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 29 04:10:25 CDT 2024