Re: What does LIKE %% actually do in a WHERE clause?

From: Randolf Geist <info_at_www.sqltools-plusplus.org>
Date: Thu, 06 Oct 2011 20:45:51 +0200
Message-ID: <4E8DF75F.nailOTJ112EPT_at_sqltools-plusplus.org>

I should have added that if you don't use an explicit ORDER BY clause with top N queries like below your result set isn't deterministic. Now it might appear to be deterministic due to the execution plan chosen that might be driven by an index operation that dictates some data retrieval order, but it should be pointed out that the optimizer might switch to other another plan or future versions might choose different approaches that make the result set really random.

There are cases where the order really doesn't matter but it is a common misconception therefore I mention it here. In order to have a deterministic result set the correct approach would be to enclose a query like that and use the ROWNUM in the outer query:

SELECT * FROM
(
SELECT A.*, B.*, C.*, D.*
FROM A, B, C, D
WHERE ...
ORDER BY ...
)
WHERE ROWNUM < 101;

Using the ROWNUM at the same level as the ORDER BY is again a common misconception because it will not do what it seems to express - it will first retrieve the first N random rows and order them afterwards.

Hope this helps,
Randolf

> SELECT A.*, B.*, C.*, D.*
> FROM A, B, C, D
> WHERE ...
> AND ROWNUM < 101;

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

Received on Thu Oct 06 2011 - 13:45:51 CDT