INLIST PARTITION
Execution plan image:
Partition operation.
Accesses multiple partitions based on the values contained in an IN-list predicate.
INSERT
Execution Plan image:
The INSERT statement allows rows to be added to a table. The following is the basic form for this statement:
INSERT INTO table_expression
[(column_list)]
{VALUES(value_list)
Table_expression specifies the table, partition, view, sub-query or table collection into which rows are inserted.
Column_list provides an optional list of columns into which data is to be inserted. Column_list is only optional if the values clause or sub-query contains data for all columns in the same order in which they display in the table definition. Omitting the column list is generally bad practice.
The VALUES clause contains a list of literals or variables containing the data to be inserted. The variables may be array bind variables in which case multiple rows may be inserted in a single operation. If the VALUES clause is not specified, a sub-query must be specified which returns the data to be inserted. The sub-query can return multiple rows.
INTERSECTION
Execution Plan image: 
Set operation.
INTERSECTION is used to merge sets of records returned by multiple queries; in this sense, it is analogous to the index record merge performed by AND-EQUAL. INTERSECTION is used when the INTERSECT clause is used in a query.
Most INTERSECT queries should be rewritten to use joins instead, to improve their ability to use row operations instead of set operations. The following example could be rewritten to use a join, but for purposes of illustration is shown using an INTERSECT clause.
Example
select Company_ID
from COMPANY
where State = 'AZ' and Parent_Company_ID is null
INTERSECT
select Company_ID
from COMPETITOR;
The query in the previous listing will select all COMPANY_IDs from the COMPANY table whose state value is 'AZ', whose Parent_Company_ID value is null, and whose Company_ID value also is present in the COMPETITOR table.
Execution Plan
PROJECTION
INTERSECTION
SORT UNIQUE
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$STATE
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR
Interpreting the Execution Plan
The Execution Plan shows the INTERSECTION operation, along with two operations described later: SORT UNIQUE and PROJECTION.
The Execution Plan shows that each of the queries is executed separately, and the results of the two queries are returned to the INTERSECTION operation. In the first (top) query, the index on the State column is used, and the ROWID values returned from that index scan are used to select records from the COMPANY table. During the TABLE ACCESS BY ROWID on the COMPANY table, the Parent_Company_ID is null criteria is applied (via an implicit FILTER operation).
The second (bottom) query does not have any WHERE clause criteria, so a TABLE ACCESS FULL (full table scan) of the COMPETITOR table is performed. The results of each query are then sorted separately via the SORT UNIQUE operations&emdash;only unique Company_ID values are passed on to the INTERSECTION operation.
The INTERSECTION operation takes the sorted rows from the two queries and returns to the user the rows that are common to both queries via the PROJECTION operation (which makes the two result sets display as one).
Advanced INTERSECTION Example
A second example of INTERSECTION involves the CONNECT BY operation. Assume that you want to add a row into the COMPANY table, with a Company_ID =10 and Parent_Company_ID =5. How can you be sure that loops are not in the hierarchical structure of the data in the COMPANY table? The query in the following listing checks for the intersection of two searches - one up the tree, and one down the tree. If the intersection of these two queries returns a row, there is a loop in the hierarchy of the Company_ID values.
Example
select Company_ID, Name
from COMPANY
where State = 'VA'
connect by Parent_Company_ID
= prior Company_ID /*down the tree*/
start with Company_ID = 10
INTERSECTION
select Company_ID, Name
from COMPANY
where State = 'VA'
connect by Company_ID
= prior Parent_Company_ID /*up the tree*/
start with Company_ID = 5;
If the query in the preceding listing does not return any rows, there will be no loop in the Company_ID hierarchy if you insert a row with a Company_ID value of 10 and a Parent_Company_ID value of 5.
INTO
The INTO operation indicates data was inserted using the multi-table insert function.