IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2018-03-29T05:43:16Z by Jordi.Hoffmann
Jordi.Hoffmann
Jordi.Hoffmann
33 Posts

Pinned topic Order by order of <table>

‏2018-03-23T09:40:57Z |

Hello,

this is just a question out of curiosity. Maybe someone can explain why this is so.

When I run this statement:

with data (n) as (values (1),(3),(5),(3),(5),(3),(1)),
     prep (n,r) as                                    
(select n,row_number() over(order by order of data)   
 from   data)                                         
select * from prep                                   

this is my result, as expected:

N                           R
1                           1
3                           2
5                           3
3                           4
5                           5
3                           6
1                           7

When I then want to see the original r while ordering by n like this:

with data (n) as (values (1),(3),(5),(3),(5),(3),(1)),
     prep (n,r) as                                    
(select n,row_number() over(order by order of data)   
 from   data)                                         
select n,r                                            
from   prep                                           
order  by n                                          

I get:

 N                           R
 1                           1
 1                           2
 3                           3
 3                           4
 3                           5
 5                           6
 5                           7

That appears like the prep-statement was executed again in the final select. I understand why this could have been implemented like this but I am just a little worried about not being able to rely on the already calculated columns of prep in the final query.

Can somebody please help me understand?

Thanks and best regards

Jordi

 

  • Cliff Dowell
    Cliff Dowell
    6 Posts
    ACCEPTED ANSWER

    Re: Order by order of <table>

    ‏2018-03-28T21:43:14Z  

    Hi Jordi,

    I don't think I have the whole answer but this is what I found.

    If you look at the execution plan of your query in Visual Explain, you'll see that the "order by n" is actually negotiated before the row_number() function is considered. This may answer part of your question about whether the Row_Number() is run more than once. 

     

    For comparison, this is the execution plan for the same query but removing the "order by n" component. 

     

    CTEs are really good at it isolating an initial data set upon which to work. This may be the idea behind why the sort happens before other functions that the DBE may consider peripheral to the task. 

    Visual Explain has a lot of info about each step of the execution plan that may answer the rest of your question. Also, since a values list is a bit transient, there may be other ways to get the result you seek when working against a table that aren't available to a values clause. 

     

    Best of luck!

    Cliff

     

  • Cliff Dowell
    Cliff Dowell
    6 Posts

    Re: Order by order of <table>

    ‏2018-03-28T21:43:14Z  

    Hi Jordi,

    I don't think I have the whole answer but this is what I found.

    If you look at the execution plan of your query in Visual Explain, you'll see that the "order by n" is actually negotiated before the row_number() function is considered. This may answer part of your question about whether the Row_Number() is run more than once. 

     

    For comparison, this is the execution plan for the same query but removing the "order by n" component. 

     

    CTEs are really good at it isolating an initial data set upon which to work. This may be the idea behind why the sort happens before other functions that the DBE may consider peripheral to the task. 

    Visual Explain has a lot of info about each step of the execution plan that may answer the rest of your question. Also, since a values list is a bit transient, there may be other ways to get the result you seek when working against a table that aren't available to a values clause. 

     

    Best of luck!

    Cliff

     

  • Jordi.Hoffmann
    Jordi.Hoffmann
    33 Posts

    Re: Order by order of <table>

    ‏2018-03-29T05:43:16Z  

    Hi Jordi,

    I don't think I have the whole answer but this is what I found.

    If you look at the execution plan of your query in Visual Explain, you'll see that the "order by n" is actually negotiated before the row_number() function is considered. This may answer part of your question about whether the Row_Number() is run more than once. 

     

    For comparison, this is the execution plan for the same query but removing the "order by n" component. 

     

    CTEs are really good at it isolating an initial data set upon which to work. This may be the idea behind why the sort happens before other functions that the DBE may consider peripheral to the task. 

    Visual Explain has a lot of info about each step of the execution plan that may answer the rest of your question. Also, since a values list is a bit transient, there may be other ways to get the result you seek when working against a table that aren't available to a values clause. 

     

    Best of luck!

    Cliff

     

    Hello Cliff,

    Thank you for the information. I think I assumed too much instead of checking the traces.

    Best regards

    Jordi