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"