Home » RDBMS Server » Performance Tuning » SQL hint between view & table (11.2.0.4 on Solaris 10)  () 1 Vote
SQL hint between view & table [message #663966] Wed, 28 June 2017 10:09 Go to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Hi Guys,

If we have a query running slow and we identified the cause say currently query is using Nested loop and performing slow and previously for the same operation it was going for hash join.

After identification we get to know that the issue is with the operation is having 2 tables and 1 view (view is based on 2 other tables).

Now question is:

1. Can a hash join be used between more than 2 objects.
2. How to use hint to use hash join when having view used in the query.

Appreciate for any help !

Thanks
Re: SQL hint between view & table [message #663967 is a reply to message #663966] Wed, 28 June 2017 10:14 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
All types of join can join only two row sources at once. This is why the join order is so critical.
You can certainly push a hint into a view, the syntax is
/*+ use_hash(<view_name>.<table_name>) */
Re: SQL hint between view & table [message #663968 is a reply to message #663967] Wed, 28 June 2017 10:18 Go to previous messageGo to next message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
Thanks John.

Is it DOT between <view_name> and <table_name> or comma between view & table used in result set?
Re: SQL hint between view & table [message #663969 is a reply to message #663966] Wed, 28 June 2017 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65194
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Appreciate for any help !

And we appreciate feedback something you seem to be unable to give.

[Updated on: Wed, 28 June 2017 11:31]

Report message to a moderator

Re: SQL hint between view & table [message #663993 is a reply to message #663969] Sat, 01 July 2017 14:29 Go to previous message
kamragulshan
Messages: 66
Registered: May 2005
Location: Delhi
Member
I will try to use the same and get back with inputs and feedback.

Previous Topic: create partitioning a table
Next Topic: insert time is drastically reduced
Goto Forum:
  


Current Time: Sat Nov 18 13:40:39 CST 2017

Total time taken to generate the page: 0.01284 seconds