Other operations

IN list iterator explain plan

Please note the difference between “BETWEEN…AND” and “IN list”. The “BETWEEN…AND” means a range while “IN list” means a list of separate values.

SELECT order_id, order_type, order_amount FROM orders WHERE order_type IN (1, 2, 3);

Execution Plan

SELECT STATEMENT 
   INLIST ITERATOR 
     TABLE ACCESS (BY INDEX ROWID) OF ‘orders’ 
        INDEX (RANGE SCAN) OF ‘orders_idx1’ (NON-UNIQUE)

Concatenation explain plan

Concatenation explain plan means the SQL statement uses “OR” operator to concatenate multiple query conditions associated with “different” columns. In this case, the SQL statement will be split into multiple SELECT clauses with the best explain plan chose for each query portion, and at last combine (concatenate) the result of each query potion.
Please note that only if the “OR” query condition is used as the driving condition will the concatenation explain plan be chosen by the optimizer; otherwise the “OR” query condition will be used as the filter only.
The execution order of the each “query portion” is starting from the last predicates (query condition) in the “OR” list.
For example,

SELECT * FROM table1 WHERE A = '10' OR B = '123';

Execution plan

  CONCATENATION 
    TABLE ACCESS (BY INDEX ROWID) OF ‘table1’
      INDEX (RANGE SCAN) OF ‘b_idx’ (NON-UNIQUE) ---- b is executed first 
    TABLE ACCESS (BY INDEX ROWID) OF ‘table1’ 
      INDEX (RANGE SCAN) OF ‘a_idx’ (NON-UNIQUE)

Sort explain plan

• SORT (UNIQUE)
  There are two possibilities for this explain plan: one is there is DISTINCT operation in the SELECT-list and the other one is there is one sub-query acting as the data provider for the main query.
• SORT (AGGREGATE)
  There is no GROUP BY clause but aggregation function is used in the SELECT-list.
• SORT (GROUP BY)
  There is GROUP BY clause in the SQL statement.
• SORT (JOIN)
  Sort Merge Join.
• SORT (ORDER BY)
  There is ORDER BY clause in the SQL statement.

SET operation explain plan

• Union/Union-All explain plan
• Intersection explain plan
• Minus explain plan

COUNT (STOPKEY) explain plan

When the SQL statement has ROWNUM used, the explain plan will show “COUNT (STOPKEY)” operation.

SELECT * FROM orders WHERE order_date = :b1 AND ROWNUM <= 20;

Execution Plan

SELECT STATEMENT 
  COUNT (STOPKEY) 
    TABLE ACCESS (BY INDEX ROWID) OF ‘orders’ 
       INDEX (RANGE SCAN) OF ‘order_idx2’ (NON-UNIQUE)