BULK COLLECT

1.   BULK COLLECT in Oracle is used to fetch multiple rows from a SQL query into a collection in a single context switch, which significantly improves performance compared to row-by-row fetching.

2.   It reduces the number of round trips between the SQL engine and the PL/SQL engine, making it especially useful when processing large volumes of data.

3.   BULK COLLECT can be used with SELECT INTO, FETCH INTO, and RETURNING INTO clauses to retrieve data efficiently into collections.

4.   When BULK COLLECT is used without a LIMIT clause, all rows are fetched at once, which may cause high memory consumption if the result set is very large.

5.   Using the LIMIT clause with BULK COLLECT helps control memory usage by fetching rows in batches, making it safer for large datasets.

6.   BULK COLLECT does not raise the NO_DATA_FOUND exception when no rows are returned; instead, the target collection remains empty.

7.   If the number of rows fetched exceeds the available memory, it can result in performance degradation or memory-related errors.

8.   BULK COLLECT is commonly combined with FORALL to perform bulk DML operations efficiently on the collected data.

9.   The order of data fetched using BULK COLLECT is preserved as returned by the query unless an ORDER BY clause is explicitly used.

10.  While BULK COLLECT improves performance, it should be used carefully with proper exception handling and memory management to avoid runtime issues.