RE: HASH_JOIN on RBO?

From: Chuck Hamilton <chuck_hamilton_at_yahoo.com>
Date: Mon, 18 Sep 2000 06:01:27 -0700 (PDT)
Message-Id: <10623.117238@fatcity.com>

--0-35005211-969282087=:17026
Content-Type: text/plain; charset=us-ascii

Moving the hint didn't change the explain plan. I puti t right after the INSERT. I also tried removing the insert and just tuning the SELECT portion,putting the hint right after the select....

SELECT --+ RULE
A.*
FROM PS_QVC_PERS_SRCH_VW A,
PS_QVC_QUERY_USERS B
WHERE A.OPRID=B.OPRCLASS It still chooses a hash join. Only with a first_rows hint does it use all nested loops joins.

Steve Adams <steve.adams_at_ixora.com.au> wrote: Hi Chuck,

Your hint is in the wrong position. It should come after the INSERT, not the SELECT. Although the insert and the select are optimized separately, they are treated as a single statement block so far as determining an optimization approach is concerned. Therefore a RULE hint needs to be placed after the first keyword in that statement to be effective.

I have just checked the documentation on this, and unfortunately it is not very helpful. The Oracle8i Tuning guide even says, "A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword." which suggests that you cannot put a hint after an INSERT keyword at all. Of course, you can and you sometimes must.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
@
@ Going to OpenWorld?
@ Catch the Ixora performance tuning seminar too!
@ See http://www.ixora.com.au/seminars/ for details.

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Thursday, 14 September 2000 2:19
To: Steve Adams
Subject: RE: HASH_JOIN on RBO?

The really odd thing is that the init.ora file has optimizer_mode = RULE. Here's the SQL. It's pretty simple...

INSERT INTO PS_QVC_FAST_SEC
SELECT --+ RULE
A.*
FROM PS_QVC_PERS_SRCH_VW A,
PS_QVC_QUERY_USERS B
WHERE A.OPRID=B.OPRCLASS; The view it references (PA_QVC_PERS_SRCH_VW) has no hints at all in it's definition. I'm still perplexed by this one.

Steve Adams wrote:

Hi Chuck,

It sounds like there is some syntax error in the hint so that it just has the effect of forcing CBO to be used despite the absence of statistics.

-----Original Message-----
From: Chuck Hamilton [mailto:chuck_hamilton_at_yahoo.com] Sent: Wednesday, 13 September 2000 7:31
To: Multiple recipients of list ORACLE-L Subject: HASH_JOIN on RBO?

I have a query with a RULE hint, but the execution plan is show hash joins. Is this something new in the 8.1.5 optimizer? It used to be that you could only get HASH joins with CBO. Also, each of the operations is showing a cost and cardinality even though none of the tables have stats. The net result is I'm getting a horrible execution plan that includes Cartesian merges and runs for hours when previously this query ran in minutes. Any ideas?

Do You Yahoo!?
Yahoo! Mail - Free email you can access from anywhere! --0-35005211-969282087=:17026
Content-Type: text/html; charset=us-ascii

<P>Moving the hint didn't change the explain plan. I puti t right after the INSERT. I also tried&nbsp;removing the insert and just tuning the SELECT portion,putting the hint right&nbsp;after the select....</P>
<P><FONT face="Courier New">SELECT --+ RULE<BR>A.*<BR>FROM PS_QVC_PERS_SRCH_VW A, <BR>&nbsp;&nbsp;&nbsp;&nbsp; PS_QVC_QUERY_USERS B<BR>WHERE A.OPRID=B.OPRCLASS</FONT><BR>&nbsp;<BR>It still chooses a hash join. Only with a first_rows hint does it use all nested loops joins.</P>
<P>&nbsp; <B><I>Steve Adams &lt;steve.adams_at_ixora.com.au&gt;</I></B> wrote: <BR>
<BLOCKQUOTE style="BORDER-LEFT: #1010ff 2px solid; MARGIN-LEFT: 5px; PADDING-LEFT: 5px">Hi Chuck,<BR><BR>Your hint is in the wrong position. It should come after the INSERT, not the<BR>SELECT. Although the insert and the select are optimized separately, they are<BR>treated as a single statement block so far as determining an optimization<BR>approach is concerned. Therefore a RULE hint needs to be placed after the first<BR>keyword in that statement to be effective.<BR><BR>I have just checked the documentation on this, and unfortunately it is not very<BR>helpful. The Oracle8i Tuning guide even says, "A statement block can have only<BR>one comment containing hints. This comment can only follow the SELECT, UPDATE,<BR>or DELETE keyword." which suggests that you cannot put a hint after an INSERT<BR>keyword at all. Of course, you can and you sometimes must.<BR><BR>@ Regards,<BR>@ Steve Adams<BR>@ http://www.ixora.com.au/<BR>@ http://www.christianity.net.au/<BR>@<BR>@ Going to OpenWor!

ld? @ Catch the Ixora performance tuning seminar too! @ See http://www.ixora.com.au/seminars/ for details. -----Original Message----- From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com] Sent: Thursday, 14 September 2000 2:19 To: Steve Adams Subject: RE: HASH_JOIN on RBO? The really odd thing is that the init.ora file has optimizer_mode = RULE. Here's the SQL. It's pretty simple... INSERT INTO PS_QVC_FAST_SEC SELECT --+ RULE A.* FROM PS_QVC_PERS_SRCH_VW A, PS_QVC_QUERY_USERS B WHERE A.OPRID=B.OPRCLASS; The view it references (PA_QVC_PERS_SRCH_VW) has no hints at all in it's definition. I'm still perplexed by this one. Steve Adams <STEVE.ADAMS@IXORA.COM.AU>wrote: Hi Chuck, It sounds like there is some syntax error in the hint so that it just has the effect of forcing CBO to be used despite the absence of statistics. @ Regards, @ Steve Adams @ http://www.ixora.com.au! / @ http://www.christianity.net.au/ @ @ Going to OpenWorld? @ Catch the Ixora performance tuning seminar too! @ See http://www.ixora.com.au/seminars/ for details. -----Original Message----- From: Chuck Hamilton [mailto:chuck_hamilton@yahoo.com] Sent: Wednesday, 13 September 2000 7:31 To: Multiple recipients of list ORACLE-L Subject: HASH_JOIN on RBO? I have a query with a RULE hint, but the execution plan is show hash joins. Is this something new in the 8.1.5 optimizer? It used to be that you could only get HASH joins with CBO. Also, each of the operations is showing a cost and cardinality even though none of the tables have stats. The net result is I'm getting a horrible execution plan that includes Cartesian merges and runs for hours when previously this query ran in minutes. Any ideas? </BLOCKQUOTE> <hr size=1>Do You Yahoo!? <a href="http://mail.yahoo.com/">Yahoo! Mail</a> - Free email you can access from anywhere! Received on Mon Sep 18 2000 - 08:01:27 CDT