Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: OPTIMIZER_HINT, FIRST_ROWS, and MAX

Re: OPTIMIZER_HINT, FIRST_ROWS, and MAX

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 10 Nov 2007 08:41:23 -0800
Message-ID: <1194712883.540721.177920@19g2000hsx.googlegroups.com>


On Nov 10, 2:54 am, DA Morgan <damor..._at_psoug.org> wrote:
> atha..._at_gmail.com wrote:
> > I am getting completely different results with the following query:
>
> > SELECT scanA.* FROM
> > SCHEDWIN.LOCSCAN scanA WHERE scanA.SCAN_TIME = ( SELECT
> > MAX(scanB.SCAN_TIME)
> > FROM SCHEDWIN.LOCSCAN scanB WHERE scanA.RESID||scanA.LIBENTRYID =
> > scanB.RESID||scanB.LIBENTRYID AND
> > (scanB.SCAN_TIME < ' 1194645483' ) );
>
> > ...depending on whether I have OPTIMIZER_HINT set to CHOOSE or
> > FIRST_ROWS!
> > (CHOOSE returns the correct result set, FIRST_ROWS returns nothing.)
>
> > Can anyone explain why this might be happening and how I can get my
> > desired result set with the CHOOSE option?
>
> > I am trying to select all the records (for a given RESID/LIBENTRYID
> > combination) that are the LATEST records (according to the SCAN_TIME
> > field) before a certain cutoff value.
>
> > Thank you!
> > -Aung
>
> If the CHOOSE option is of interest to you ... you have what antiquated
> model of 8i or before (4 decimal places please)?
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

In other words the full Oracle version and edition is always of interest on any post plus in the case of SQL performance the explain plan(s) for the SQL in question is also of interest.

HTH -- Mark D Powell -- Received on Sat Nov 10 2007 - 10:41:23 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US