Re: What does LIKE %% actually do in a WHERE clause?
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 06 Oct 2011 20:45:51 +0200
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;
--
//www.freelists.org/webpage/oracle-l
Other related posts:
