COLLECTION ITERATOR
Execution Plan image:
Row operation.
The Collection Iterator operation returns certain values from a collection such as VARRAY and nested table.
CONCATENATION
Execution plan image: 
Row operation.
CONCATENATION performs a UNION ALL (a UNION without elimination of duplicate values) of result sets.
Example
select Name, City, State
from COMPANY
where State = 'TX'
and City in ('Houston', 'Austin', 'Dallas');
This query selects from the COMPANY Table based on two criteria in the WHERE clause. The criteria can use the COMPANY$CITY index and the COMPANY$STATE index to obtain ROWIDs for the rows to be returned. Since the NAME column is required by the query, the COMPANY table will have to be accessed; the other column values are already available via the index searches.
The query of the CITY column uses an IN clause, which is functionally equivalent to an OR clause. The query could be rewritten as:
select Name, City, State
from COMPANY
where State = 'TX'
and (City = 'Houston'
or City = 'Austin
or City = 'Dallas);
Taking this expansion one step further, the STATE portion of the query can be placed into each section of the OR clause. The revised query would now read:
select Name, City, State
from COMPANY
where (State = 'TX' and City = 'Houston')
or (State = 'TX' and City = 'Austin')
or (State = 'TX' and City = 'Dallas');
This query helps with understanding the plan that is generated.
Execution Plan
CONCATENATION
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
TABLE ACCESS BY ROWID COMPANY
AND-EQUAL
INDEX RANGE SCAN COMPANY$CITY
INDEX RANGE SCAN COMPANY$STATE
Interpreting the Execution Plan
The Execution Plan shows that the query is executed as if the IN clause is rewritten as an OR clause, and the other criteria are placed within the OR clauses. Within each OR clause, an AND-EQUAL operation is performed to merge the lists of ROWIDs returned from the index scans. The ROWIDs returned by the AND-EQUAL operations are then used to select the requested columns from the COMPANY table via a TABLE ACCESS BY ROWID operation. The resulting records from each part of the query are then concatenated to carry out the OR clause.
Note: When the query is very complex, the Optimizer may decide not to use the CONCATENATION operation. Instead, it will use partial index range scans. If you want to force the use of the CONCATENATION operation, you may need to use the format shown in the last example query.
CONNECT BY
Execution Plan image: 
CONNECT BY does a recursive join of a table to itself, in a hierarchical fashion.
Example
select Company_ID, Name
from COMPANY
where State = 'VA'
connect by Parent_Company_ID = prior Company_ID
start with Company_ID = 1;
The query shown in the preceding statement selects companies from the COMPANY in a hierarchical fashion; that is, it returns the rows based on each Company’s parent company. If there are multiple levels of company parentage, those levels display in the report.
Execution Plan
FILTER
CONNECT BY
INDEX UNIQUE SCAN COMPANY_PK
TABLE ACCESS BY ROWID COMPANY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$PARENT
Interpreting the Execution Plan
The plan shows that first the COMPANY_PK index is used to find the root node (Company_ID = 1), index on the Parent_Company_ID column is used to provide values for queries against the Company_ID column in an iterative fashion. After the hierarchy of Company_IDs is complete, the FILTER operation&emdash;the WHERE clause related to the STATE value&emdash;is applied. Notice that the query does not use the index on the STATE column, although it is available and the column is used in the WHERE clause.
CONNECT BY PUMP
Execution plan image:
Join operation.
PUMP is an operator which is used with "Connect by" in hierarchical self-join.