When writing PL/SQL code for Big Data applications it’s always a good idea to leverage the database as much as possible.  This is particularly true for ETL or warehousing processing.

In my experience, I’ve tried to quantify objects in Oracle programming that work best to temporarily  store data.  As part of your Big Data application, anytime you need to leverage relational databases, here is a table that I typically use as a guideline for storing data for loading to or extracting data from a database.

Typically, I judge the what type of cursor object to used based on factors such as performance, the number of rows that need to be read, the operation that I am performing and how much memory that I would like to leverage.

Number of Rows	Explicit Cursor	Temporary Table	PL/SQL Memory Collections	Record Collections	Typical Memory Used
<= 1000					10 MB
1000 to 500,000					100-500 MB
500,00 to 2,000,000+					500MB +

Although, explicit cursors can be used for much larger data sizes, if performance is your goal, a few thousand rows are optimal to maintain good performance, especially if you are opening and closing many cursors in a small window of time.