123

[crysu]

Hi,

I have the following UNION which it is returning rows ordered by the first column from select. I need to know why Oracle does this because the output should be other.
Currently I get something like
1
2
3
4
5
6
and I should get something like
6
5
4
1
2
3

((SELECT *
           FROM (SELECT TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID,
                        TAPE_MOVEMENT_ACTIONS_VW1.BOX_NUMBER,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_NAME,
                        TAPE_MOVEMENT_ACTIONS_VW1.LOCATION,
                        TAPE_MOVEMENT_ACTIONS_VW1.KIND,
                        TAPE_MOVEMENT_ACTIONS_VW1.DEVICE,
                        TAPE_MOVEMENT_ACTIONS_VW1.TAPE_ISSUE,
                        TAPE_MOVEMENT_ACTIONS_VW1.CONTENT_DESCRIPTION,
                        TAPE_MOVEMENT_ACTIONS_VW1.AVAILABLE_CODE,
                        TAPE_MOVEMENT_ACTIONS_VW1.LIBRARY_LOCATION,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_NOTES,
                        TAPE_MOVEMENT_ACTIONS_VW1.MEDIA_VERSION,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_STATUS,
                        TAPE_MOVEMENT_ACTIONS_VW1.RESPONSIBILITY_OF--,
                        --TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID F0
                   FROM TAPE_MOVEMENT_ACTIONS_VW TAPE_MOVEMENT_ACTIONS_VW1
                  WHERE ((((((TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID > :V_1) AND
                        (TAPE_MOVEMENT_ACTIONS_VW1.AVAILABLE_CODE = :V_2))) AND
                        (TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID < :V_3))))
                  ORDER BY TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID DESC)
          WHERE ROWNUM <= :V_ROW_NUM) UNION
        (SELECT *
           FROM (SELECT TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID,
                        TAPE_MOVEMENT_ACTIONS_VW1.BOX_NUMBER,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_NAME,
                        TAPE_MOVEMENT_ACTIONS_VW1.LOCATION,
                        TAPE_MOVEMENT_ACTIONS_VW1.KIND,
                        TAPE_MOVEMENT_ACTIONS_VW1.DEVICE,
                        TAPE_MOVEMENT_ACTIONS_VW1.TAPE_ISSUE,
                        TAPE_MOVEMENT_ACTIONS_VW1.CONTENT_DESCRIPTION,
                        TAPE_MOVEMENT_ACTIONS_VW1.AVAILABLE_CODE,
                        TAPE_MOVEMENT_ACTIONS_VW1.LIBRARY_LOCATION,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_NOTES,
                        TAPE_MOVEMENT_ACTIONS_VW1.MEDIA_VERSION,
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_STATUS,
                        TAPE_MOVEMENT_ACTIONS_VW1.RESPONSIBILITY_OF--,
                        --TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID F0
                   FROM TAPE_MOVEMENT_ACTIONS_VW TAPE_MOVEMENT_ACTIONS_VW1
                  WHERE ((((((TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID > :V_4) AND
                        (TAPE_MOVEMENT_ACTIONS_VW1.AVAILABLE_CODE = :V_5))) AND
                        ((TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID >= :V_6) OR
                        TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID IS NULL))))
                  ORDER BY TAPE_MOVEMENT_ACTIONS_VW1.PHYSICAL_MEDIA_ID ASC)
          WHERE ROWNUM <= :V_ROW_NUM))

Execution plan is:

Plan
SELECT STATEMENT  ALL_ROWSCost: 121,342  Bytes: 94,689  Cardinality: 21  Partition #: 0  						
	10 SORT UNIQUE  Cost: 121,342  Bytes: 94,689  Cardinality: 21  Partition #: 0  					
		9 UNION-ALL  Cost: 0  Bytes: 0  Cardinality: 0  Partition #: 0  				
			4 COUNT STOPKEY  Cost: 0  Bytes: 0  Cardinality: 0  Partition #: 0  			
				3 VIEW ONAIR. Cost: 1  Bytes: 4,509  Cardinality: 1  Partition #: 0  		
					2 TABLE ACCESS BY INDEX ROWID ONAIR.PHYSICAL_MEDIA Cost: 1  Bytes: 51  Cardinality: 1  Partition #: 0  	
						1 INDEX RANGE SCAN DESCENDING ONAIR.QQ_CRS1234 Cost: 1  Bytes: 0  Cardinality: 1  Partition #: 0  
			8 COUNT STOPKEY  Cost: 0  Bytes: 0  Cardinality: 0  Partition #: 0  			
				7 VIEW ONAIR. Cost: 107  Bytes: 582,188,553  Cardinality: 129,117  Partition #: 0  		
					6 TABLE ACCESS BY INDEX ROWID ONAIR.PHYSICAL_MEDIA Cost: 107  Bytes: 6,584,967  Cardinality: 129,117  Partition #: 0  	
						5 INDEX RANGE SCAN ONAIR.QQ_CRS1234 Cost: 7  Bytes: 0  Cardinality: 129,117  Partition #: 0

Thanks for your help
Sorry for the thread title I forgot to edit it should be "How rows are ordered into an union statement"

[PAVB]

I would take out order by in inner queries and put a single order by in outer query.

[crysu]

I can't take that order by out from the inner query because I need some specific records, I use that order by with rownum.
I need to know why oracle is displaying the rows ordered and if it supposed to display rows like this. I couldn't find any info related to union combined with order by clause -- I'm still searching

Thanks for your answer.

[PAVB]

I can't take that order by out from the inner query because I need some specific records, I use that order by with rownum.
I need to know why oracle is displaying the rows ordered and if it supposed to display rows like this. I couldn't find any info related to union combined with order by clause -- I'm still searching

The only way to ensure a specific order in the result set is by including an order by clause.

If you need the inner order by clauses so be it - no problem with me.

What you have to do is to add an order by clause to the outermost query specifying the desired order.

[crysu]

The only way to ensure a specific order in the result set is by including an order by clause.

If you need the inner order by clauses so be it - no problem with me.

What you have to do is to add an order by clause to the outermost query specifying the desired order.

From my test I have seen that union combined with order by in each query is the same thing as the case when you have a order by in the most outer query. I found some webpages on net regarding this behavior but till nothing "official". I just to be sure that I can rely on this behavior for other statements because most of the query are generated automatically from the system.

Best Regards