Today I have been reported an incidence indicating a Siebel report does not return the output in a specific order, in this case sorted by date. To know which is the statement that is behind the report, I had to trace the user, several methods are possible, from seeing the current SQL executing by the user to put a 10046 trace.
In this case it is a select that has the following form;
In this case the problem is clear, the SELECT statement does not perform ordination using the “order by” clause. Usually in these cases the ordering is based on the projection (COLUMN_1, COLUMN_2, … COLUMN_n) or in an index which is what solves the select. In this case the ordering is based on an index. The problem is that the execution plan has changed so it no longer works ordination.
The solution is to change the SQL, including the “order by” clause or maybe change the execution plan using a outline.