3.4.2 Access Method Hints

Table of Contents Previous Next


3 The SQL Language : 3.4 Optimizer Hints : 3.4.2 Access Method Hints

FULL(table)
INDEX(table [ index ] [...])
Use index on table to access the relation.
NO_INDEX(table [ index ] [...])
Do not use index on table to access the relation.
In addition, the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints of Table 3‑3‑10 can be used.
The following steps create a database named, bank, populated by the tables, accounts, branches, tellers, and history. The –s 5 option specifies a scaling factor of five which results in the creation of five branches, each with 100,000 accounts, resulting in a total of 500,000 rows in the accounts table and five rows in the branches table. Ten tellers are assigned to each branch resulting in a total of 50 rows in the tellers table.
Note, if using Linux use the export command instead of the SET PATH command as shown below.
The EXPLAIN command shows the plan selected by the query planner. In the following example, aid is the primary key column, so an indexed search is used on index, accounts_pkey.
The FULL hint is used to force a full sequential scan instead of using the index as shown below:
The NO_INDEX hint also forces a sequential scan as shown below:
In addition to using the EXPLAIN command as shown in the prior examples, more detailed information regarding whether or not a hint was used by the planner can be obtained by setting the client_min_messages and trace_hints configuration parameters as follows:
The SELECT command with the NO_INDEX hint is repeated below to illustrate the additional information produced when the aforementioned configuration parameters are set.
Note that if a hint is ignored, the INFO: [HINTS] line will not appear. This may be an indication that there was a syntax error or some other misspelling in the hint as shown in the following example where the index name is misspelled.

3 The SQL Language : 3.4 Optimizer Hints : 3.4.2 Access Method Hints

Table of Contents Previous Next