RE: First_Rows hint hangs SQL Statement when using > 56 values present on an IN Clause

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Thu, 17 Mar 2005 10:19:43 +0100
Message-Id: <20050317092002.AE191B00070A@smtp-out2.tiscali.nl>

Chris,
why would you ever need so many values in an IN list? makes me wonder how the underlying data model looks like :-)

Question 2: is this a real hang, or is it "just" slow?

The behavior you see is probably *indirectly* caused by the hint. how does the execution plan look like?

By the way, talking about this hint, just curious: are you using the legacy one without specifying a number of rows? You should avoid that -- because it is based on heuristics rather than statistics.

kind regards,

Lex.

Visit my website at http://www.naturaljoin.nl

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of chris blais
Sent: Thursday, March 17, 2005 00:41
To: oracle-l_at_freelists.org
Subject: First_Rows hint hangs SQL Statement when using > 56 values present on an IN Clause

Hey all,

We can duplicate our 'hang' whenever we have > 56 values in a SQL statement IN Clause.

By removing the first_rows hint, the query works fine. We're running 9204 Solaris.

Question Anyone seen this before. Sounds like a limitation with First_rows.......

Much Abliged

"Chris" Blais Bank of America Vice President -- GCIB Database Technology & Solutions NC1-004-02-051 desk: 704-387-3010 cell: 720-982-5100

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 17 2005 - 04:23:33 CST