Vantage uses indexes and partitions to access the rows of a table. If indexed or partitioned access is not suitable for a query, or if a query accesses a NoPI table that does not have an index defined on it, the result is a full-table scan.
Access Methods
The following table access methods are available to the Optimizer:
- Unique Primary Index
- Unique Partitioned Primary Index
- Nonunique Primary Index
- Nonunique Partitioned Primary Index
- Unique Secondary Index
- Nonunique Secondary Index
- Join Index
- Full-Table Scan
- Partition Scan
Effects of Conditions in WHERE Clause
For a query on a table with an index defined, the predicates or conditions in the WHERE clause determine whether the system can use row hashing, can do a table scan with partition elimination, or must do a full-table scan.
The following functions are applied to rows identified by the WHERE clause, and have no effect on the selection of rows from the base table:
- GROUP BY
- HAVING
- INTERSECT
- MINUS/EXCEPT
- ORDER BY
- QUALIFY
- SAMPLE
- UNION
- WITH ... BY
- WITH
Statements that specify any of the following WHERE clause conditions cause full-table scans (FTS). If the table has a PPI, partition elimination may reduce the FTS access to only the affected partitions.
- Nonequality comparisons
- column_name IS NOT NULL
- column_name NOT IN (list_of_values)
- column_name NOT IN (subquery)
- column_name BETWEEN ... AND
- Condition_1 OR condition_2
- NOT condition_1
- column_name LIKE
- column_1 || column_2 = value
- table1.column_x = table1.column_y
- table1.column_x [computation] = value
- table1.column_x [computation] - table1.column_y
- INDEX (column_name)
- SUBSTR (column_name)
- SUM
- MIN
- MAX
- AVG
- DISTINCT
- COUNT
- ANY
- ALL
- Missing WHERE clause
The type of table access that the system uses when statements specify any of the following WHERE clause conditions depends on whether the column or columns are indexed, the type of index, and its selectivity:
- column_name = { value | constant_expression }
- column_name IS NULL
- column_name IN (list_of_values)
- column_name IN (subquery)
- condition_1 AND condition_2
- Different data types
- table1.column_x = table2.column_x
In summary, a query influences processing choices:
- A full-table scan (possibly with partition elimination if the table has a PPI) is required if the query includes an implicit range of values, such as in the following WHERE examples.
When a small BETWEEN range is specified, the Optimizer can use row hashing rather than a full-table scan.
... WHERE column_name [BETWEEN <, >, <>, <=, >=] ... WHERE column_name [NOT] IN (SELECT...) ... WHERE column_name NOT IN (val1, val2 [,val3])
- Row hashing can be used if the query includes an explicit value, as shown in the following WHERE examples:
... WHERE column_name = val ... WHERE column_name IN (val1, val2, [,val3])
Related Information
For more information about:
- The efficiency, number of AMPs used, and the number of rows accessed by all table access methods, see Database Design Techniques to Support Localized Work .
- Full-table scans, see Full-Table Scans.