17.5.7 Types of retrieval methods

For details about the retrieval methods, see the HiRDB Version 8 UAP Development Guide.

Organization of this subsection
(1) Retrieval without using an index
(2) Retrieval using one index
(3) SELECT-APSL
(4) Retrieval using a multicolumn index
(5) Retrieval of work tables
(6) Retrieval using row identifier
(7) Retrieval of the result of queries to foreign servers

(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.