How to sort rows with SQL: Databases for Developers #12

[@IncredibleAdventure1]

Hello Chris, I have one question regarding order by. Does Oracle Database sort results in ascending order based on the first column of the first query when using set operator Union implicitly(by default)?

[@TheMagicofSQL]

No. If you use UNION ALL the database just combines the data sets. If you use UNION it'll add a deduplication step. This *might* be implemented using a sort. A hash is more likely though.

[@strielok1158]

What about clustered indexes? If a table has such an index, select from that table will always return the records in the same order (of course if there is no order by clause), is that correct? As far as I know, in many RDBMS, the primary key index is going to be clustered by default.

[@TheMagicofSQL]

The only way to GUARANTEE a query returns rows in particular order is by adding an ORDER BY to it. Even with a clustered table (only the default in SOME databases), the query may return rows in a different order. A common reason is because it uses a different index to access the data.

[@SURESHRAJA-c5x]

Hi bro , I am having a top n query , taking long time ... the reason I found is the columns I am using in order by having no index. Could you help me?

[@TheMagicofSQL]

Sorting large tables can take a long time. Creating an index including columns in the where clause and sort can help. Filtering the data before the top-N so you process fewer rows should help too (if possible). If you're using Oracle Database and need more help, post details to asktom.oracle.com and we'll see if we can assist. Ensure you include your query and its execution plan.

[@juno7893]

Perfect

[@TheMagicofSQL]

Thanks! :)

[@grigorvardanyan]

Really appreciate for the videos, but I believe they could've been a bit shorter

[@TheMagicofSQL]

What would you cut?

[@Ashishkumar-qo4jn]

Do not understand

[@TheMagicofSQL]

What don't you understand? Please clarify!