[top]
[prev]
[next]

Documentation Top
Global Index
Reference Manual
TOC Index
Grammar Index
Developer’s Guide
TOC Index
Tuning Cloudscape
TOC Index
|
constraint
Function
The Cloudscape optimizer chooses an index, including the indexes that enforce constraints, as the access path for query execution if it is useful. If there is more than one useful index, in most cases Cloudscape chooses the index that is most useful.
You can override the optimizer's selection and force use of a particular index or force a table scan. To force use of the index that enforces a primary key or unique constraint, use this property, specifying the unqualified name of the constraint.
System-generated constraint and index names use lowercase letters, so you must treat them as delimited identifiers and enclose them in double quotation marks.
NOTE: An exception is thrown if the access path is nonexistent or invalid. For example, the optimizer cannot use an index if the statement is an updatable cursor whose updatable columns are in the index's key, or if the statement is an UPDATE statement and the updated columns are in the index's key.
Syntax
constraint=constraintName
Example
-- specifying the index or constraint affects the access
-- path during query execution
CREATE TABLE mytable (a int, b int, c int,
CONSTRAINT mykey primary key (a, b));
0 rows inserted/updated/deleted
ij> INSERT INTO mytable values (1, 2, 3), (1, 3, 3), (2, 2, 3);
3 rows inserted/updated/deleted
ij> CREATE BTREE INDEX myindex ON mytable(a);
0 rows inserted/updated/deleted
ij> -- there are now two useful indexes on the
-- values in column a
SET RUNTIMESTATISTICS ON;
0 rows inserted/updated/deleted
ij> -- let the optimizer choose an access path
-- it will use one of the two useful indexes
SELECT * FROM mytable WHERE a = 1;
A |B |C
-----------------------------------------------
1 |2 |3
1 |3 |3
2 rows selected
ij> -- runtimestatistics() will show the access path
VALUES RUNTIMESTATISTICS().toString();
. . .
IndexRowToBaseRowResultSet for MYTABLE:
. . .
IndexScanResultSet for MYTABLE using constraint MYKEY . . .
1 row selected
ij> -- force a table scan (use no index)
SELECT * FROM mytable
PROPERTIES index=NULL
WHERE a = 1;
A |B |C
-----------------------------------------------
1 |2 |3
1 |3 |3
2 rows selected
ij> VALUES RUNTIMESTATISTICS().toString();
. . .
TableScanResultSet for MYTABLE . . .
. . .
1 row selected
ij>
-- force the use of myindex
SELECT * FROM mytable
PROPERTIES index=myindex
WHERE a = 1;
A |B |C
-----------------------------------------------
1 |2 |3
1 |3 |3
2 rows selected
ij> VALUES RUNTIMESTATISTICS().toString();
. . .
IndexRowToBaseRowResultSet for MYTABLE:
. . .
IndexScanResultSet for MYTABLE using index MYINDEX . . .
1 row selected
ij> -- force the use of the (named) primary key constraint
SELECT * FROM mytable
PROPERTIES constraint=mykey
WHERE a = 1;
A |B |C
-----------------------------------------------
1 |2 |3
1 |3 |3
2 rows selected
ij> VALUES RUNTIMESTATISTICS().toString();
. . .
IndexRowToBaseRowResultSet for MYTABLE:
IndexScanResultSet for MYTABLE using constraint MYKEY . . .
next time in milliseconds/row = 0
1 row selected
ij>
Scope
Table optimizer-override property.
|