Re: Oracle Hints

["Peter R. Lewandowski" <peter.lewandowski@CSUN.EDU>]

I had to look this one up myself.

According to Oracle Press, Advanced Tuning and Administration
  "The ordered hint, when used with NESTED LOOPS joins,
influences the order in which tables are joined--that is, the structure
of the loops used by the join."

They do warn you that if you use this you should make certain that
the relative distribution of values within the joined table do not
change over time.  I guess why I never used this.  If it does change
you can run into performance problems.

Date sent:              Tue, 7 Sep 1999 17:50:20 -0400
Send reply to:          SQR-USERS@list.iex.net
From:                   Albert Howard <starbuckk@mailroom.com>
Subject:                Re: Oracle Hints
To:                     Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>

> Now for my question..what exactly does this parameter do?  Thanks!
>
> At 12:23 PM 9/7/99 -0400, you wrote:
> >Place your Oracle hint on the same line as the Begin-Select -- NOT on its
> >own line and especially not in column 1.
> >
> >> -----Original Message-----
> >> From: Larry Young
> >> Sent: Tuesday, September 07, 1999 11:54 AM
> >> To:   Multiple recipients of list SQR-USERS
> >> Subject:      Oracle Hints
> >>
> >> Hi, I am trying to run a SQR with the following in the select statement
> >>
> >> begin-select
> >>  /*+ ordered */
> >> B.BUSINESS_UNIT (0,0)        on-break   print=never  level=1
> >> after=bu-total
> >> L.DESCR         (0,0)        on-break   print=never  level=1
> >> CO.DST_ID_AR    (0,0)        on-break   print=never  level=2
> >> after=External-Total
> >> B.CUST_ID       (0,0)        on-break   print=never  level=3
> >> after=customer-total
> >> U.NAME1
> >> B.ITEM
> >> B.ITEM_LINE
> >> B.BAL_AMT_BASE
> >>
> >> etc...etc. etc......
> >>
> >> Can someone tell me if /*+ ordered */ is still supported under SQR 4
> >> because I am getting the following error message.
> >>
> >>
> >> Error on line 242:
> >>    (SQR 3721) Bad param found on 'BEGIN-SELECT' line; Format is:
> >>  BEGIN-SELECT [DISTINCT] [-Cnn] [-Bnn] [LOOPS=nn]
> >>               [ON-ERROR=procedure[(arg1[,argi]...)]]
> >>  /*+ ordered */
> >>
> >>
> >> The select works in SQR 3.
> >>
> >> Any suggestions would be appreciated.
> >>
> >> Thanks
> >>
> >> Larry


Pete Lewandowski
Analyst/Programmer
818-677-7539