/*+ ORDERED LEADING() */ Destroys session.

Yeah, yeah, I know, using these two contradictory hints is stupid. None-the-less, read on.

OS: RedHat Linux (Kernel 2.2.12-20)
DB Version: 8.1.6

Oracle8i Designing and Tuning for Performance Release 2 (8.1.6)
Chapter 7, "Using Optimizer Hints"
Section: Using Hints
Sub-Section: Hints for Join Operations
Paragraph Title: LEADING
Text: If you specify the ORDERED hint, then it overrides all LEADING hints.

So, I dutifully tried this out in a number of ways. Here's an easy way of seeing the problem:

<<

SELECT /*+ ORDERED LEADING(Dual) */ * FROM Dual;
<<

SQL*PLUS responded with:

<<

SELECT /*+ ORDERED LEADING(Dual) */ * FROM Dual *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
<<

So, I tried:

<<

CONNECT <username>/<password>
<<

And SQL*PLUS responded with:

<<

ERROR:
ORA-24327: need explicit attach before authenticating a user

Warning: You are no longer connected to ORACLE.
<<

I tried this more than once. I must exit SQL*PLUS before trying to logon again, for it not to give me a 24327.

I also tried this from PL/SQL Developer (specifically in the EXPLAIN PLAN Window) and it returned some interesting errors. I had to close that window (which had its own session) to continue without errors.

Apparently, ORDERED does a bit more than just overriding the LEADING hint, it overrides your session too!

Any thoughts?

Brian Received on Sun Dec 23 2001 - 10:04:03 CST