Getting Started › Overriding SQL Key Selection › Examples

Examples

  CREATE TABLE CARS (MAKE CHAR(8), COLOR CHAR(8));
  CREATE INDEX CARS_COLOR_KEY ON CARS (COLOR) DATACOM NAME COLOR;
  CREATE SYNONYM CARS_HINT_COLOR FOR CARS;

Note: The first line in the previously shown example creates a key on column MAKE.

The following example shows the use of a synonym for override:

  SELECT * FROM CARS_HINT_COLOR
  WHERE MAKE = 'FORD' AND COLOR= 'PINK';

The following example shows the use of a correlation name for override:

  SELECT * FROM CARS CARS_HINT_COLOR
  WHERE MAKE = 'FORD' AND COLOR= 'PINK';

Note: Using statistics such as cardinality and BLKCHG (how closely does key sequence reflect the physical sequence of rows in the data area), the SQL Optimizer might select the key on column MAKE when the key on COLOR is the most efficient, since PINK is a rare color. These queries cause the COLOR key to be used.