I've had a hard time researching this and/or even proving it so I've come to the forum to ask for advice.
My question is how Oracle will handle the ordering of a query in which I do
SELECT *
FROM ( SELECT *
FROM ( SELECT LEVEL c1,
LEVEL
* TRUNC(DBMS_RANDOM.VALUE(1,
4))
val_rnd
FROM DUAL
CONNECT BY LEVEL <= 200)
ORDER BY c1 ASC) a,
( SELECT TRUNC( LEVEL
* DBMS_RANDOM.VALUE(1,
4))
val_rnd2
FROM DUAL
CONNECT BY LEVEL <= 200) b
WHERE a.c1 = b.val_rnd2(+)
In this case, my sub-select does the ordering of c1 ascending. This part I understand and will make my subquery "a" become ordered. My next step then is joining this sorted query to a second table ("b").
In this join, does Oracle still preserve my original sorting or do I need to add a second order by a.C1 asc after the join to guarantee it will return in sorted order.
My example above returns the query how I would want it but I cannot tell if this is just due to myself being lucky and Oracle returning it correctly or if it will 100% guarantee function this way.
I am currently running on Oracle 11.2.0.4