A cursor is a construct available in most implementations of SQL that allows the programmer to handle data in a row-by-row manner rather than as a group. Parallelizing row-by-row processing is much more complex than serial processing, which is another reason to make use of non-procedural SQL wherever possible. Database vendors typically handle parallel processing without requiring special handling by application developers.
Contents
- Constraints
- Example Insert rows based on a count in the table itself
- Solution in cursor form
- Solution in SQL
- Number table
- Solution core
- References
Parallel processing can be orders of magnitude faster than serial processing.
Constraints
In this article, the following constraints apply:
Example: Insert rows based on a count in the table itself
The table below represents types of marbles. The four text columns represent four marble characteristics. Each characteristic has two values for a total of 16 types of marbles.
The "quantity" column represents how many marbles of that type we have. The task is to create a second table holding one row for each marble of that type. Thus, the target table would have the four text columns, and a total of 40 + 20 + 20 + 10 + ... + 10 + 5 = 270 rows.
Source table:
QUANTITY TEXTURE APPEARANCE SHAPE COLOR ---------- ---------- ---------- ---------- ----- 40 smooth shiny round blue 20 smooth shiny warped blue 20 smooth dull round blue 10 smooth dull warped blue 20 rough shiny round blue 10 rough shiny warped blue 10 rough dull round blue 5 rough dull warped blue 40 rough dull warped red 20 rough dull round red 20 rough shiny warped red 10 rough shiny round red 20 smooth dull warped red 10 smooth dull round red 10 smooth shiny warped red 5 smooth shiny round redTable to generate:
TEXTURE APPEARANCE SHAPE COLOR ---------- ---------- ---------- ----- smooth shiny round blue -- 1 smooth shiny round blue -- 2 ... -- and so on smooth shiny round blue -- 40 smooth shiny warped blue -- 1 smooth shiny warped blue -- 2 ... -- and so on smooth shiny warped blue -- 20 ... -- and so on smooth shiny round red -- 1 smooth shiny round red -- 2 smooth shiny round red -- 3 smooth shiny round red -- 4 smooth shiny round red -- 5Solution in cursor form
Generating the target table with a cursor is fairly simple.
Solution in SQL
Solving the problem with SQL is a bit more code and requires a bit more creative thought than the nested loop approach of cursors.
Number table
The solution requires an intermediate table. The table has one column of type NUMBER that has the values 0 to whatever number of rows is needed. For this discussion, we'll limit it to one million rows. The code is as follows: Setup:
The numbers table can be created in parallel.
Solution core
Assume the marble type table above is named marbles_seed and the target table is named marbles. The code that generates the needed 270 rows is:
The database can process this insert in parallel without the programmer's involvement.