![]() |
Internal Language Transformations
|
Reference Manual |
Sort AvoidanceSorting is an expensive process. Cloudscape tries to eliminate unnecessary sorting steps where possible.
DISTINCT Elimination Based on a Uniqueness ConditionA DISTINCT (and the corresponding sort) can be eliminated from a query if a uniqueness condition exists that ensures that no duplicate values will be returned. If no duplicate values are returned, the DISTINCT node is superfluous, and Cloudscape transforms the statement internally into one without the DISTINCT keyword.
CREATE TABLE tab1 (c1 INT,
CREATE TABLE tab2 (c1 INT,
INSERT INTO tab1 VALUES (1, 2, 3, 'WA'),
INSERT INTO tab2 VALUES (1, 2),
-- all the columns in the index on the only table appear
-- all the columns in the index on the only table appear
-- all the columns in the index on tab1 appear Combining ORDER BY and DISTINCTWithout a transformation, a statement that contains both DISTINCT and ORDER BY would require two separate sorting steps--one to satisfy DISTINCT and one to satisfy ORDER BY. (Currently, Cloudscape uses sorting to evaluate DISTINCT. There are, in theory, other ways to accomplish this.) In some situations, Cloudscape can transform the statement internally into one that contains only one of these keywords. The requirements are:
A unique index is not required.
SELECT DISTINCT miles, meal
SELECT DISTINCT miles, meal Combining ORDER BY and UNIONWithout a transformation, a statement that contains both ORDER BY and UNION would require two separate sorting steps--one to satisfy ORDER BY and one to satisfy UNION. (Currently Cloudscape uses sorting to eliminate duplicates from a UNION.) In some situations, Cloudscape can transform the statement internally into one that contains only one of these keywords (the ORDER BY is thrown out). The requirements are:
Cloudscape will be able to transform the following statements:
SELECT miles, meal
SELECT city_id, tour_level FROM Hotels
SELECT city_id, tour_level FROM Hotels Cloudscape cannot avoid two sorting nodes in the following statement, because of the order of the columns in the ORDER BY clause:
SELECT city_id, tour_level FROM Hotels |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |