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

athaung (AT) gmail (DOT) com wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Nov 10, 2:54 am, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
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 --

On Nov 9, 10:43 pm, atha... (AT) gmail (DOT) com wrote:
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.

The Oracle version exhibiting the problem is 8.1.7.0.0.
The problem does NOT show up in 9.2.0.1.0.

Separating out the join of the concatenated values results in 0 rows
returned in both cases.

Is there a better way to do this query? The basic idea is this:
A table holds a list of Book Ids and Check-Out times.
I want to get a list of the records with the latest Check-Out times
before a given cutoff time, for each Book Id available in the table.

Thanks very much for taking a look,
-Aung

athaung (AT) gmail (DOT) com wrote:
Which is: No longer supported, and unpatched.

If you insist on using desupported versions, try to get
them at least at End-Of-Lifecycle versions.
For 8i, release 3, that would be 8.1.7.4.17 (or something
similar), for 9i Release 2, that would be 9.2.0.8.0.
If you bought extended support on 9i, it will get patched.

The only comment from Oracle support (after they stopped
laughing, I guess) will be "If the problem persists in
EOL versions, contact us again".

(and you know what the answer will be on 8i: "Upgrade")
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

athaung (AT) gmail (DOT) com wrote:
8.1.7.0.0? This is desupported paleolithic software to which
you have not applied a single patch in a decade. And now, it
appears, you are doing the same thing with 9.2.

Lesson 1 ... purchase a support contract and patch regularly
for reasons of security, stability, performance, and scalability.
Oracle is serious software, not a microwave oven, you must pay
attention if you want it to work properly.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org