![]() |
DML Statements and Performance
|
Reference Manual |
Non-Cost-Based OptimizationsThe optimizer makes some non-cost-based optimizations, which means that it does not consider them when determining the access path and join order. If all the conditions are right, it makes the optimizations after the access path and join order are determined. Non-Cost-Based Sort Avoidance (Tuple Filtering)In most cases, Cloudscape needs to perform two separate steps for statements that use DISTINCT or GROUP BY: first sorting the selected columns, then either discarding duplicate rows or aggregating grouped rows. Sometimes it is able to avoid sorting for these statements with tuple filtering. Tuple filtering means that the rows are already in a useful order. For DISTINCT, Cloudscape can simply filter out duplicate values when they are found and return results to the user sooner. For GROUP BY, Cloudscape can aggregate a group of rows until a new set of rows is detected and return results to the user sooner. These are non-cost-based optimizations; the optimizer does not yet consider the cost of these optimizations. The examples in this section refer to the following tables: CREATE TABLE t1(c1 INT, c2 INT, c3 INT, c4 INT) CREATE INDEX i1_2_3_4 ON t1(c1, c2, c3, c4) DISTINCTTuple filtering is applied for a DISTINCT when the following criteria are met:
NOTE: The set of column references must be an in-order prefix of the index. Here is the most common case in which tuple filtering will be applied: Equality predicates allow tuple filtering on the following:
SELECT DISTINCT c2
SELECT DISTINCT c2, c4
-- the columns don't have to be in the Quick DISTINCT ScansCloudscape can use a hash table instead of a sorter to eliminate duplicates when performing a DISTINCT in the following cases:
This technique allows for minimal locking when performing the scan at the READ COMMITTED isolation level. NOTE: This technique appears in RunTimeStatistics as a DistinctScanResultSet. GROUP BYTuple filtering is applied for a GROUP BY when the following criteria are met:
Here is the most common case in which tuple filtering will be applied: SELECT max(c2) FROM t1 GROUP BY c1 Equality predicates allow tuple filtering on the following:
SELECT c2, SUM(c3)
SELECT max(c4) The MIN() and MAX() OptimizationsThe optimizer knows that it can avoid iterating through all the source rows in a result to compute a MIN() or MAX() aggregate when data are already in the right order. When data are guaranteed to be in the right order, Cloudscape can go immediately to the smallest (minimum) or largest (maximum) row. The following conditions must be true:
For example, the optimizer can use this optimization for the following queries (if the optimizer uses the appropriate indexes as the access paths):
-- index on orig_airport
-- index on orig_airport
-- index on orig_airport
-- index on segment_number, flight_id
SELECT * FROM Flights WHERE segment_number = (SELECT MIN(segment_number) FROM Flights The optimizer decides whether to implement the optimization after choosing the plan for the query. The optimizer does not take this optimization into account when costing the plan. |
|
![]() 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. |