Use of bulk operation to speed up queries

Abstract

Bulk operations are the advantage enabled by Oracle database. They are used through PL/SQL programming language and significantly decrease time necessary to fulfill statements on large amount of data. Their advantage is that a large amount of data is transferred in a single step into SQL where they are executed, data are retrieved from it. In this way, it is avoided that for every row from a table a change of context is being done between PL/SQL engine and SQL engine. Difference in execution time is increasing with the number of rows to process. Bulk operations may be used also with a predefined maximal number of rows that will be transferred between two engines in a single step. In this way a memory overload is avoided, which improves performances additionally. Performances comparison will be shown at a PL/SQL block which will execute without bulk operation and then with it. These executions will be done on a table with different number of rows so the differences in performance over different amount of data could be measured.