OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » hints on views

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

hints on views [message #148040]

Tue, 22 November 2005 07:43

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

Report message to a moderator

Re: hints on views [message #148043 is a reply to message #148040]

Tue, 22 November 2005 07:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

Is the data the same on both machines?

Report message to a moderator

Re: hints on views [message #148044 is a reply to message #148040]

Tue, 22 November 2005 08:00

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator