SORT BASED ON SPECIFIC INDEX

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;

foto_index_sort

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.

Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s