17.5.7 Types of retrieval methods
(1) Retrieval without using an index
- TABLE SCAN
- This method retrieves data pages in a table without using an index.
(2) Retrieval using one index
- INDEX SCAN
- This method narrows down the table by retrieving the index pages of a single-column index, then retrieves the data pages of the table.
- KEY SCAN
- This method retrieves only the index pages of a single-column index. It does not retrieve data pages.
- MULTI COLUMNS INDEX SCAN
- This method narrows down the table by retrieving the index pages of a multicolumn index, then retrieves the data pages of the table.
- MULTI COLUMNS KEY SCAN
- This method retrieves only the index pages of a multicolumn index. It does not retrieve data pages.
- PLUGIN INDEX SCAN
- This method retrieves table data pages after narrowing the search by using a plug-in index.
- PLUGIN KEY SCAN
- This method retrieves index pages by using a plug-in index only. It does not retrieve data pages.
(3) SELECT-APSL
- SELECT-APSL (applicable only to HiRDB/Parallel Server)
- If the specified conditions contain a ? parameter, the optimum join method may depend on the value of the ? parameter. The optimum join method cannot be selected during preprocessing because the value of the ? parameter cannot be determined. This method selects a join method by calculating the hit rate during SQL execution.
(4) Retrieval using a multicolumn index
- AND PLURAL INDEXES SCAN
- This method retrieves rows using each applicable index according to the search conditions concatenated by AND and OR operators, and stores the row identifiers (ROWID) in each work table. The method combines all work tables into one by obtaining the product set for the AND operators, the union for the OR operators, and the difference set for the ANDNOT operators (specifiable only in the ASSIGN LIST statement). Then it retrieves rows on the basis of the row identifiers in the work table.
- When a work table of row identifiers is created from a given set of conditions, the utility may create the work table using TABLE SCAN, even when there is no index for condition columns.
- OR PLURAL INDEXES SCAN
- This method retrieves rows using each index according to the search conditions concatenated by OR operators, and stores row identifiers (ROWID) in a single work table. The method eliminates all duplicate rows from the work table, then retrieves rows on the row identifier.
- When a work table for row position information is created from a given set of conditions, the utility may create the work table using TABLE SCAN, even when there is no index for condition columns.
(5) Retrieval of work tables
- LIST SCAN
- This method retrieves the work tables there are created internally.
(6) Retrieval using row identifier
- ROWID FETCH
- This method searches a table by using the row identifier (ROWID) as a key. The system does not execute this search if there is no need to fetch rows.
(7) Retrieval of the result of queries to foreign servers
- FOREIGN SERVER SCAN
- This method issues SQL statements to foreign servers to receive the results of a query.
- FOREIGN SERVER LIMIT SCAN
- This may be displayed when the function for retrieving the first n rows of the retrieval result is used. This method issues an SQL statement containing the ORDER BY clause to a foreign server and receives the first n rows of the query result.