Nonstop Database, HiRDB Version 9 UAP Development Guide
The table below describes the search method types (except LIST SCAN and ROWID FETCH) 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.
Table 4-12 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 can be searched rapidly even if a cluster key index is used and the search cannot be narrowed very much. The sort processing might be omitted.#1 |
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 sort processing might be omitted.#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. |
Figure 4-36 TABLE SCAN processing method
Figure 4-37 INDEX SCAN processing method
Figure 4-38 KEY SCAN processing method
Figure 4-39 MULTI COLUMNS INDEX SCAN processing method
Figure 4-40 MULTI COLUMNS KEY SCAN processing method
Figure 4-41 PLUGIN INDEX SCAN processing method
Figure 4-42 PLUGIN KEY SCAN processing method
Figure 4-43 AND PLURAL INDEXES SCAN processing method
Figure 4-44 OR PLURAL INDEXES SCAN processing method
Figure 4-45 LIST SCAN processing method
Figure 4-46 ROWID FETCH processing method
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.