Home » RDBMS Server » Performance Tuning » hints on views
hints on views [message #148040] Tue, 22 November 2005 07:43 Go to next message
alka205
Messages: 18
Registered: August 2005
Junior Member
All
I have a query which is select statement

SELECT A.c1, TO_CHAR(A.c2,'YYYY-MM-DD'), A.c3, A.c4, A.c5, B.c1, A.c6, A.c7
FROM table1 A, view1 A1, table2 B, view2 B1

view1 : select ....... from table3,table4,table5

view2 : select ....... from table6, table7, table8

If I do explain plan of my query on test box,

there is a hash join between table3 and table6 (which makes query run faster).

same query on other box run very slow, because there is a nested loop, no hash join.

How do i give a hint to use hash join in views.

Please help
Re: hints on views [message #148043 is a reply to message #148040] Tue, 22 November 2005 07:54 Go to previous messageGo to next message
Frank
Messages: 7897
Registered: March 2000
Senior Member
Is the data the same on both machines?
Re: hints on views [message #148044 is a reply to message #148040] Tue, 22 November 2005 08:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You can use USE_HASH hint.
But why? Hints should be your last resort.

Did you check anything.
>>same query on other box run very slow, because there is a nested loop, no hash join.

Are the datasets same? are statistics/parameters/configurations same?

http://oraclesvca2.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#76074
Re: hints on views [message #148045 is a reply to message #148044] Tue, 22 November 2005 08:02 Go to previous messageGo to next message
alka205
Messages: 18
Registered: August 2005
Junior Member
yeap everything is same
Re: hints on views [message #149194 is a reply to message #148040] Wed, 30 November 2005 08:53 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I would think that nope, by definition, everything is not the same. Look for some difference, as it must be out there.
Previous Topic: Incorrect system statistics
Next Topic: priority query
Goto Forum:
  


Current Time: Tue Dec 06 20:22:22 CST 2022