Re: OPTIMIZER_HINT, FIRST_ROWS, and MAX

On Nov 9, 10:43 pm, 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

Queries of the form
WHERE a||b = x||y
an unreliable at best, and so is

scanB.SCAN_TIME < ' 1194645483'

although I am surprised to hear you get inconsistent results.

What is the datatype of locsan.scan_time?

What happens if you rationalise the query to

SELECT scanA.*
FROM schedwin.locscan scanA
WHERE scanA.scan_time =

       ( SELECT MAX(scanB.scan_time)
         FROM   schedwin.locscan scanB
	 WHERE  scanA.resid = scanB.resid
	 AND    scanA.libentryid = scanB.libentryid
	 AND    scanB.scan_time < ' 1194645483' );

I agree with the other comments though, none of this speculation means very much without the Oracle version and perhaps (I'm guessing here) Forms version. Received on Sun Nov 11 2007 - 10:27:49 CST