0

This query is based on 2 tables where some rows in PerfData1 have multiple child records. Rows are not duplicate but some ids are. I need to take only 1 of such rows and this is also a paging query, which needs to get total number or existing items and a final subset.

The query works fine. However, I am not sure if the order which is set in the inline view (inner ORDER BY) will be kept in the final output. So far, is did not see any deviations. But I remember that in the SQL Server world this is not guaranteed, hence I need 2 ORDER BYclauses.

    SELECT 
        All_Results_Table.*
    FROM
    (
        SELECT 
            Rownum_Table.*,
            COUNT(*) OVER() as sys___countAllFoundRecords,
            ROWNUM sys___outputRownum
        FROM 
        (
            SELECT 
                ROW_NUMBER() OVER(PARTITION BY PerfData1.id ORDER BY PerfData1.id) AS sys___multivalueRowSequence, -- This is OPTIONAL when we DON'T want dupe IDs
                ROW_NUMBER() OVER(order by 1) AS sys___innerRowNumber,
                PerfData1.id,
                PerfData1.c1 item,
                PerfData2.c1 subitem
            FROM 
                PerfData1 inner join 
                PerfData2 on PerfData1.id = PerfData2.fkId
            WHERE (1 = 1) 
            ORDER BY id, subitem desc --<<-- WILL THIS ORDER STAY in the FINAL OUTPUT 
        ) Rownum_Table
        WHERE sys___multivalueRowSequence = 1 -- This is OPTIONAL when we DON'T want dupe IDs
    ) All_Results_Table
    WHERE 
        sys___outputRownum BETWEEN 200 AND 230

Will order set in the ORDER BY in the above SQL be kept in the final output?

3 Answers 3

5

With just about any SQL implementation, there is no guarantee at all on the ordering of the final output unless you state explicitly what you want that order to be.

In some cases a particular other may be very likely, but is never guaranteed. SQL is a declarative language, anything you don't declare, the database is free to not relocate in future runs. You might get the same order in every conceivable test now, but later when the data grows and/changes balance, or someone changes the indexing choices for the affected tables, or one or more of the tables become views, or the database is upgraded to newer version with a massively tweeted query planner, the way the data is worked on could change significantly and that ordering is lost.

If you want to guarantee a specific order out of the final layer of a complex query, or a simple query for that matter, you need to explicitly declare that you want that ordering with an ORDER BY clause at that level.


tl;dr: no.


From comments:

In this case, may there is some hint I can use to tell ORACLE to keep original order? Because you know, in these multi-layered queries, I believe, Order by eats up the performance

ORDER BY almost always¹ has some performance cost, but if you need the data in that order then you need it in that order² so need to pay the price. As Paul says, the price may be practically nothing: if the data needed to be order by that anyway due to the way the query planner decides is the best method of getting what you have asked for then there is no need to re-sort, and the only cost is the fraction of a second the planner takes to notice this³.

To order the output, the ORDER BY must apply to the outermost part of the query, in this case moving ORDER BY id, subitem desc out to after your final WHERE clause. There is no other way to guarantee the final ordering, anything else that seems to work is relying on undefined behaviour that may change at any time.

Furthermore: with regard to having an ORDER BY in a sub-query like that; the query planner may completely ignore it. Some databases will in fact consider its existence to be a syntax error. If you are relying on it to give a specific ordering to the output of ROW_NUMBER() OVER(order by 1) then I would instead use ROW_NUMBER() OVER(order by id, subitem desc).

[1] cases where it doesn't include simple queries that can seek/scan an index and don't reference anything not covered or included by that index, but if you assume requesting a specific ordering always costs then you'll never be wrong in a way that leads to an unpleasant surprise

[2] though there are times when it is beneficial to let the DB throw things out in any old order and sort in the calling application, to move the sort load away from the DB because the application layer is often easiest to scale up

[3] or it may have two plan options that look to perform identically otherwise, but one will naturally produce a sort by what you want without extra work so it picks that plan over the other.

1
  • ROW_NUMBER() OVER(order by id, subitem desc) solved my whole debate problem, besides everything else. Now I have a valid query both ways. Thanks
    – T.S.
    Oct 20, 2022 at 15:09
2

It is only guaranteed that the result is sorted if there is an order by clause at the end of the query. The Oracle manuals are absolutely clear about this. In "Oracle Database, SQL Language Reference, 12c Release 1 (12.1), E41329-25, July 2017" you can read:

order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

For some keywords it is mentioned that they do not guarantee ordering, e.g. here

group_by_clause

The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

You also mentioned in a comment that you found a similar note for the UNION keyword. I think the reason is that traditional implementations of these features need to sort the data and so the result was already sorted. Often programmers relied on this. But now there are implementations that use other algorithm and then the result isn't sorted anymore.

0
1

An ORDER BY in a subselect will not be respected in the final output. What makes you think otherwise?

Now, this being Oracle, it may lead you to believe it will maintain the order, only to stab you in the back, multiple times, when you least expect it. So, don't.

1
  • Documentation for group by and union says - "Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists", or similar. But there is nothing about inline view.
    – T.S.
    Oct 18, 2022 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.