123
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: 123

  1. #1
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22

    123

    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

    Code:
    ((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:

    Code:
    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"

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would take out order by in inner queries and put a single order by in outer query.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    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.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by crysu View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2010
    Location
    Craiova, Romania
    Posts
    22
    Quote Originally Posted by PAVB View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width