2.8.3 Database retrieval method (out-of-order execution)

(1) Concept of out-of-order execution

With relational databases, retrieval processing consists of a set operation executed against rows. There are no rules governing the sequence in which retrieval results are returned by a DBMS, except for the final sorting process. The out-of-order execution method was devised to take advantage of this. The following figure shows the concept of out-of-order execution.

To Page Top

(2) Retrieval using the out-of-order execution method

By effectively utilizing hardware resources, the out-of-order execution method can search through a large-scale database at high speeds. The following figure compares retrieval using the out-of-order execution method and other retrieval methods.

When the out-of-order execution method is not used, only one retrieval process is assigned to a thread. Furthermore, a new I/O instruction is issued only after the previously issued I/O instruction is completed. Therefore, you can only issue the number of I/O instructions that does not exceed the upper limit of the I/O performance of the storage device. As a result, as the volume of data in the database becomes larger, the amount of time the system must wait for I/O completion becomes longer and the retrieval takes longer.

With the out-of-order execution method, retrieval processes are divided by rows and assigned to individual threads. Each assigned retrieval process is executed independently in each thread. Additionally, the individual threads can be executed out-of-order based on the progress of I/O processing. That is, after the threads issue I/O instructions, they are processed in parallel and processing can be switched to another thread without having to wait for the completion of other I/O instructions (asynchronous I/O processing). The result is high-speed data retrieval.

To Page Top

(3) Threads with which the out-of-order execution method is used

HADB executes I/O instructions and other processing in parallel by assigning processes not having dependent relationships to multiple threads of the thread types listed below. The out-of-order execution method uses the following types of threads:

• Real threads

  Real threads are managed by the OS (kernel and library). When the CPU has two or more cores, threads allow multiple processes to be executed in parallel.

• Pseudo-threads

  Pseudo-threads are managed by HADB. Although multiple pseudo-threads can be generated in each real thread, two or more pseudo-threads cannot be active simultaneously. By using multiple pseudo-threads, HADB executes processes and I/O instructions in parallel.

To Page Top

(4) Cases in which the out-of-order execution method is not applied

In the following cases, the out-of-order execution method is not applied; standard SQL execution order is used instead:

• If an SQL statement other than retrieval (SELECT statement) is being executed

• If 0 is specified as the number of real threads to be used when executing an SQL statement (adb_sql_exe_max_rthd_num operand)

• If pseudo-threads cannot be generated (when 0 is specified as the number of pseudo-threads that can be generated in each real thread (adb_sys_uthd_num operand))

To Page Top

(5) Relationship between the out-of-order execution method and wait status

This subsection explains the relationship between the out-of-order execution method and the wait status that occurs if the number of processing real threads required for executing an SQL statement cannot be allocated.

To Page Top