BULK COLLECT Vs CURSOR

Bulk Collect will be having better performance at the cost of Server RAM.

Cursor resides in our temp tablespace but as we bulk collect into some collection and this collection resides in the computer memory(RAM). So while using cursor we should consider about our temp tablespace and while using Bulk collect we should consider about server RAM.

COLLECTIONS in Oracle

Index-By Tables (Associative Arrays) -  Same as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;

Nested Table Collections - Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.

TYPE table_type IS TABLE OF NUMBER(10);

Varray Collections - A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

TYPE table_type IS VARRAY(5) OF NUMBER(10);

Assignments and Equality Tests

Same type of collections - 
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;

  v_tab_2 := v_tab_1; -- This works

  IF v_tab_1 = v_tab_2 THEN -- This works
    ....
  END IF;


Different type of collections - 
  
  TYPE table_type_1 IS TABLE OF NUMBER(10);
  TYPE table_type_2 IS TABLE OF NUMBER(10);
  v_tab_1  table_type_1;
  v_tab_2  table_type_2;

  v_tab_2 := v_tab_1;  -- This will throw error

  IF v_tab_1 = v_tab_2 THEN  -- This will throw error
    ....
  END IF;


Collection Methods:

A variety of methods exist for collections, but not all are relevant for every collection type.

EXISTS(n) - Returns TRUE if the specified element exists.
COUNT - Returns the number of elements in the collection.
LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
FIRST - Returns the index of the first element in the collection.
LAST - Returns the index of the last element in the collection.
PRIOR(n) - Returns the index of the element prior to the specified element.
NEXT(n) - Returns the index of the next element after the specified element.
EXTEND - Appends a single null element to the collection.
EXTEND(n) - Appends n null elements to the collection.
EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
TRIM - Removes a single element from the end of the collection.
TRIM(n) - Removes n elements from the end of the collection.
DELETE - Removes all elements from the collection.
DELETE(n) - Removes element n from the collection.
DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

MULTISET Operations

  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
  ...

MULTISET UNION {ALL | DISTINCT} Operator - 

  l_tab1 := l_tab1 MULTISET UNION l_tab2;

  l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;

MULTISET EXCEPT {DISTINCT} Operator

  l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;

MULTISET INTERSECT {DISTINCT} Operator

  l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;

https://oracle-base.com/articles/8i/collections-8i
https://docs.oracle.com/cloud/latest/db112/LNPLS/composites.htm#LNPLS005

Query optimization using CBO and RBO

Query optimization is the overall process of choosing the most efficient means of executing a SQL statement. SQL is a nonprocedural language, so the optimizer is free to merge, reorganize, and process in any order.

The database optimizes each SQL statement based on statistics collected about the accessed data. The optimizer determines the optimal plan for a SQL statement by examining multiple access methods, such as full table scan or index scans, different join methods such as nested loops and hash joins, different join orders, and possible transformations.

For a given query and environment, the optimizer assigns a relative numerical cost to each step of a possible plan, and then factors these values together to generate an overall cost estimate for the plan. After calculating the costs of alternative plans, the optimizer chooses the plan with the lowest cost estimate. For this reason, the optimizer is sometimes called the cost-based optimizer (CBO) to contrast it with the legacy rule-based optimizer (RBO).

Note:

The optimizer may not make the same decisions from one version of Oracle Database to the next. In recent versions, the optimizer might make different decision because better information is available and more optimizer transformations are possible.

Execution Plans

An execution plan describes a recommended method of execution for a SQL statement.

The plan shows the combination of the steps Oracle Database uses to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them for the user issuing the statement.

An execution plan displays the cost of the entire plan, indicated on line 0, and each separate operation. The cost is an internal unit that the execution plan only displays to allow for plan comparisons. Thus, you cannot tune or change the cost value.