Table 4-10 describes the search method types (except LIST SCAN, ROWID FETCH, FOREIGN SERVER SCAN and FOREIGN SERVER LIMIT SCAN) and their features.

LIST SCAN is applied when a work table is created and searched, for example, in a viewed table search or a WITH clause query expression. ROWID FETCH is applied when a cursor is used. FOREIGN SERVER SCAN and FOREIGN SERVER LIMIT SCAN are applied when the local HiRDB gets retrieval results from a foreign server.

Table 4-10 Search method types and features

Search method	Processing method	Advantages	Disadvantages
Table scan(TABLE SCAN)	This method sequentially searches the pages (data pages) in which the table is stored and references all rows. The initial data fetch is fairly slow.	When all data items are to be searched, the data can be searched rapidly. The data can be searched rapidly even if the search cannot be narrowed with an index.	Even if the search results can be narrowed by conditions, the performance is poor because all data pages are referenced.
Index scan (INDEX SCAN, MULTI COLUMNS INDEX SCAN, PLUGIN INDEX SCAN)	This method executes a binary search of the index, and then each time it retrieves the row identifier of a target data item, it references the database row indicated by that row identifier. The initial data fetch is fast.	The data can be searched rapidly when the search can be narrowed with an index. The data rows can be obtained in order (or reverse order) of the index configuration column values.1 The data can be searched rapidly even if a cluster key index is used and the search cannot be narrowed very much.	If the search cannot be narrowed very much with an index, the number of random I/O operations performed on the data pages increases, and performance drops.
Key scan(KEY SCAN, MULTI COLUMNS KEY SCAN, PLUGIN KEY SCAN)	This method executes a binary search of the index and references only the data found in the index (configuration column values or row identifiers of the index). This method is applied when only the configuration columns or row identifiers of the index are to be referenced. The initial data fetch is fast.	Even if the search cannot be narrowed very much with an index, the data can be searched rapidly because only the index pages are referenced and there is no data page input or output. The rows can be obtained in order (or reverse order) of the index configuration column values.1	None
SELECT-APSL	If a condition contains a ? parameter, this method prepares several join method candidates, and determines the optimal search method when the value of the ? parameter is input. The speed of the initial data fetch differs according to the search method that is actually selected.	When the value of the ? parameter is input, the optimal search method can be selected by considering the narrowing rate obtained with the index.	The optimizing information collection utility (pdgetcst) must be executed.2 Also, the SQL object size becomes large because several search candidates are prepared.
AND multiple index usage(AND PLURAL INDEXES SCAN)	This method uses multiple indexes, creates multiple work tables, combines product sets, sum sets, and difference sets between the work tables to obtain results. The initial data fetch is slow.	Because the results are obtained by combining, product sets, sum sets, and difference sets, indexes can be used in evaluating the data even when multiple conditions are specified.	This method creates several work tables and sorts the data in each work table. Thus, if the search cannot be narrowed with an index, the performance drops because the number of items to be sorted is large.
OR multiple index usage(OR PLURAL INDEXES SCAN)	This method stores results retrieved by using multiple indexes into one work table, and executes duplicate elimination at the end to obtain results. The initial data fetch is slow.	The data can be searched rapidly if narrowing with an index is possible for the individual search conditions that are combined with the OR operator.	This method uses multiple indexes to search the data, stores the results in one work table, sorts the results, and executes duplicate elimination. Thus, if there are many data items before duplicate elimination, the performance drops.

¹ In processing that requires sorting, the rows can be obtained in order (or reverse order) of the index configuration column values. However, if HiRDB judges that sorting is unnecessary, it may cancel sort processing.

² In some cases, the optimal access path cannot be selected even if the optimizing information collection utility is executed. For details about the necessity of executing the optimizing information collection utility, see the manual HiRDB Version 8 Command Reference and verify the performance.

(a) Search using no index

TABLE SCAN

This processing method searches the database pages of a table without using an index. Figure 4-29 shows the TABLE SCAN processing method.

Figure 4-29 TABLE SCAN processing method

(b) Search using one index

INDEX SCAN

This processing method searches the index pages of a single-column index to narrow the search and then searches the data pages of the table. Figure 4-30 shows the INDEX SCAN processing method.

Figure 4-30 INDEX SCAN processing method

KEY SCAN

This processing method searches only the index pages of a single-column index. The data pages are not searched. Figure 4-31 shows the KEY SCAN processing method.

Figure 4-31 KEY SCAN processing method

MULTI COLUMNS INDEX SCAN

This processing method searches the index pages of a multi-column index to narrow the search and then searches the data pages of the table. Figure 4-32 shows the MULTI COLUMNS INDEX SCAN processing method.

Figure 4-32 MULTI COLUMNS INDEX SCAN processing method

MULTI COLUMNS KEY SCAN

This processing method searches only the index pages of a multi-column index. The data pages are not searched. Figure 4-33 shows the MULTI COLUMNS KEY SCAN processing method.

Figure 4-33 MULTI COLUMNS KEY SCAN processing method

PLUGIN INDEX SCAN

This processing method uses a plug-in index to narrow the search and then searches the data pages of the table. Figure 4-34 shows the PLUGIN INDEX SCAN processing method.

Figure 4-34 PLUGIN INDEX SCAN processing method

Note

The structure of the plug-in index differs according to the plug-in.

PLUGIN KEY SCAN

This processing method searches only the index pages of a plug-in index. The data pages are not searched. Figure 4-35 shows the PLUGIN KEY SCAN processing method.

Figure 4-35 PLUGIN KEY SCAN processing method

Note

The structure of the plug-in index differs according to the plug-in.

SELECT-APSL (for HiRDB/Parallel Server)

If the conditions include the ? parameter, the optimal search method may change depending on the value of the ? parameter. Also, if the value of the ? parameter cannot be determined during preprocessing, the optimal search method cannot be determined. The system therefore determines the search method by calculating the hit ratio during SQL execution.

AND PLURAL INDEXES SCAN

For search conditions that are combined with the AND or OR operator, the respective indexes are used to conduct the search, and the row identifiers (ROWID) are stored in the respective work tables. These work tables are consolidated into a single work table by forming a product set when the AND operator is used, a sum set when the OR operator is used, and a difference set when the ANDNOT operator (can only be used in the ASSIGN LIST statement) is used. Then rows are fetched based on the row identifiers of this work table.

When creating a work table of row identifiers from each condition, HiRDB sometimes uses TABLE SCAN to create the work table, even if the condition column does not have an index.

Figure 4-36 shows the AND PLURAL INDEXES SCAN processing method.

Figure 4-36 AND PLURAL INDEXES SCAN processing method

OR PLURAL INDEXES SCAN

For search conditions that are combined with the OR operator, the respective indexes are used to conduct the search, and the row identifiers (ROWID) are stored in one work table. After the duplicate rows in the work table are eliminated by duplicate elimination, the rows are fetched based on the row identifiers.

When creating a work table of row identifiers from each condition, HiRDB sometimes uses TABLE SCAN to create the work table, even if the condition column does not have an index.

Figure 4-37 shows the OR PLURAL INDEXES SCAN processing method.

Figure 4-37 OR PLURAL INDEXES SCAN processing method

LIST SCAN

This processing method searches a work table that was created internally.

Figure 4-38 shows the LIST SCAN processing method.

Figure 4-38 LIST SCAN processing method

ROWID FETCH

This processing method searches a table by using row identifiers (ROWID) as keys. If the row does not have to be fetched, a search is not executed.

Figure 4-39 shows the ROWID FETCH processing method.

Figure 4-39 ROWID FETCH processing method

FOREIGN SERVER SCAN

The local HiRDB executes an SQL statement to the foreign server where the foreign table is located. The local HiRDB then receives the retrieval results for the query.

Figure 4-40 shows the FOREIGN SERVER SCAN processing method.

Figure 4-40 FOREIGN SERVER SCAN processing method

FOREIGN SERVER LIMIT SCAN

When the retrieve first n records facility is being used, the local HiRDB executes an SQL statement containing an ORDER BY clause to the foreign server where the foreign table is located. The local HiRDB then receives the first n rows of the retrieval results for the query.

Figure 4-41 shows the FOREIGN SERVER LIMIT SCAN processing method.

Figure 4-41 FOREIGN SERVER LIMIT SCAN processing method