Sort Using RDBMS (Magic xpa 3.x)

When using SQL databases, the RDBMS engine can sort the rows dynamically by using the ORDER BY clause without the need to define an index or an index as required in ISAM files. Sort usage in Magic xpa remains unchanged. When all columns are sorted as part of the main and joined tables, however, Magic xpa allows the RDBMS to sort. When it is not possible for the RDBMS to sort, Magic xpa sorts the rows by using a temporary file in the sort database defined in the Magic.ini file.

Sort usage in a task is the same as defining a virtual index for that table. In addition, it minimizes the need for you to define additional virtual indexes to anticipate the index needs of your end users. When defining a sort, Magic xpa reissues the Select statement for the task, and replaces the ORDER BY clause that was generated according to the Index with the column names used in the Sort as long as the following conditions are met:

• All sort segments are part of the main or linked tables (only with Link Inner Join or Link Left Outer Join).

• All sort segments are natural data types in the database. This means that they are mapped to data types to which the database can sort. For example, MS-SQL cannot sort according to a BIT data type, therefore sorting by a sort segment that is mapped to BIT in the database will not use the Sort Using RDBMS feature and will create a temporary sort table in the sort database defined in the Magic.ini file.

Magic xpa always requires a unique ORDER BY clause. If you do not specify that the ORDER BY clause is unique, and if Magic xpa does not determine that the sort segments in combination are unique, Magic xpa will add the position index segments to the ORDER BY clause. In addition, if any part of the columns selected in the sort were not part of the main and join tables, Magic xpa creates a temporary sort file.

When you define a sort that results in an ORDER BY clause, the RDBMS optimizer might not use an index to perform a Query, but might prefer to use a different access method to perform the query in a more efficient way.