Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: OPTIMIZER_HINT, FIRST_ROWS, and MAX
athaung_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 damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sat Nov 10 2007 - 01:54:27 CST