Search This Blog

Don't assume!

We can not assume in same way we don't believe in rule of thumbs!

consider query:

select c1, sum(c2)
from t1
group by c1

this query returns result set in sorted order of c1 but it changes in 10g R2 as 10g used HASH GROUP BY Operation to implement grouping,rather than using SORT GROUP BY as it would do in earlier versions. So Here if sorting is desired there must be explicit order by query.

Similary There can be some join queries in which users might be getting sorted result set , but they can not rely on it always, may be if execution plan changes it can not sort the result set, so if sorting is required, developers need explicit specify order by clause in query.

I remember a case in which a junior developer wrote a query to dump the table data to asciii csv file, Here was obviously clear columns data in csv need in same order as in table. But as Developer came to know about view user_tab_columns I told him, he used query on this view to estimate the maximum record length of table in csv file(rather than manully summing the all columns widths of table) what he could have done alternate way is set large linesize along with trimspool on, but he wamted to cut short work of typing select c1||','|| c2||','||c3||','||... from table. So he generated this select query from user_tab_columns. But he assumed columns orders would be same as in table name. Result was wrong columns order in csv file. So please don't assume - it was view- so not guaranteed.


Follow by Email