Oracle Built-in Operators
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Operators manipulate individual data items called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication
operator is represented by an asterisk (*). Operators, in Oracle are implemented by means of creating in most cases overloaded objects, similar to PL/SQL packages that contain functions only.

The following SQL statement:

SELECT operator_name, number_of_binds
FROM dba_operators
ORDER BY 2;


will show you, for example, that the CONTAINS operator has 24 separate binds (overloads).
Dependencies
ALL_OPERATORS DBA_OPERATORS OPERATOR$
CDB_OPERATORS OBJ$ STANDARD
 
Arithmetic Operators
Addition <numeric_value> + <numeric_value>
SELECT 100 + 10 FROM dual;
Subtraction <numeric_value> - <numeric_value>
SELECT 100 - 10 FROM dual;
Multiplication <numeric_value> * <numeric_value>
SELECT 100 * 10 FROM dual;
Division <numeric_value> / <numeric_value>
SELECT 100 / 10 FROM dual;
Power (PL/SQL Only) '**' (left IN NUMBER, right IN NUMBER) RETURN NUMBER;
'**' (left IN BINARY_DOUBLE, right IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
set serveroutput on

BEGIN
  dbms_output.put_line('2 to the 5th is ' || TO_CHAR(2**5));
END;
/
 
Assignment Operator
Assign <variable> := <value>
set serveroutput on

DECLARE
 x VARCHAR2(1) := 'A';
BEGIN
  dbms_output.put_line(x);

  x := 'B';
  dbms_output.put_line(x);
END;
/
 
Association Operator
Association <parameter_name> => <value>
exec dbms_stats.gather_schema_stats(USER, CASCADE => TRUE);
 
Concatenation Operator
Concatenate <string> || <string>
SELECT 'Daniel ' || 'Morgan' FROM dual;
 
Date Operators
Addition <date_value> + <numeric_value>
SELECT SYSDATE + 10 FROM dual;
Subtraction <date_value> - <date_value>
SELECT SYSDATE - 10 FROM dual;
 
Hierarchical Query Operators
CONNECT, CONNECT BY, CONNECT BY PRIOR, and CONNECT BY ROOT will be dealt with on a separate page on Hierarchical Queries
 
Multiset Operators
Note: Combine the results of two nested tables into a single nested table
Multiset CAST(MULTISET(<select statement> AS object_type)
See CAST Library Page Linked Below
Multiset Except All 'MULTISET_EXCEPT_ALL' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_EXCEPT_ALL',18, 2, 40);

<nested_table1> MULTISET EXCEPT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET EXCEPT ALL cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Except Distinct 'MULTISET_EXCEPT_DISTINCT' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_EXCEPT_DISTINCT',18, 2, 40);

<nested_table1> MULTISET EXCEPT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET EXCEPT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Intersect All 'MULTISET_INTERSECT_ALL' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_INTERSECT_ALL',18, 2, 40);

<nested_table1> MULTISET INTERSECT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET INTERSECT ALL cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Intersect Distinct 'MULTISET_INTERSECT_DISTINCT' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_INTERSECT_DISTINCT',18, 2, 40);

<nested_table1> MULTISET INTERSECT <ALL | DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET INTERSECT DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Union All  'MULTISET_UNION_ALL' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_UNION_ALL',18, 2, 40);

<nested_table1> MULTISET UNION ALL <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET UNION DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
Multiset Union Distinct 'MULTISET_UNION_DISTINCT' (collection IN "<TABLE_1>", collection2 IN "<TABLE_1>")
RETURN "<TABLE_1>";
PRAGMA BUILTIN('MULTISET_UNION_DISTINCT',18, 2, 40);

<nested_table1> MULTISET UNION DISTINCT <nested_table2>
SELECT customer_id, cust_address_ntab1
MULTISET UNION DISTINCT cust_address_ntab2 RESULTS
FROM customer_demo;
 
Pivot Operators
Note: Traditional pivoting is done with DECODE and CASE so you may want to look at those demos on the DECODE page
Pivot / Unpivot Demo 1 PIVOT [XML] (<aggregate function> (expression) [AS <alias>]
FOR (<column_list>)
IN <subquery>)

UNPIVOT [<INCLUDE | EXCLUDE> NULLS] (<column_list>) FOR (<column_list>)
IN (<column_list>) [AS (<constant_list>)])
conn oe/oe@pdborcl

CREATE TABLE pivot_table AS
SELECT * FROM (
  SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total
  FROM orders)
PIVOT (SUM(order_total)
FOR order_mode
IN ('direct' AS Store, 'online' AS Internet));

desc pivot_table

SELECT * FROM pivot_table;

SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;

EXPLAIN PLAN FOR
SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |   12 |   408 |     7  (15)|
|  1 |  SORT ORDER BY     |             |   12 |   408 |     7  (15)|
|* 2 |   VIEW             |             |   12 |   408 |     6   (0)|
|  3 |   UNPIVOT          |             |      |       |            |
|  4 |   TABLE ACCESS FULL| PIVOT_TABLE |    6 |   234 |     3   (0)|
---------------------------------------------------------------------

SELECT * FROM pivot_table
UNPIVOT INCLUDE NULLS (yearly_total FOR order_mode
IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;
Pivot / Unpivot Demo 2 conn uwclass/uwclass@pdbdev

SELECT * FROM (
  SELECT program_id, customer_id, 1 CNT
  FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));

EXPLAIN PLAN FOR
SELECT * FROM (
  SELECT program_id, customer_id, 1 CNT
  FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------
| Id | Operation           | Name      | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    5 |    45 |   302   (5)|
|  1 |  HASH GROUP BY PIVOT|           |    5 |    45 |   302   (5)|
|  2 |   TABLE ACCESS FULL | AIRPLANES |  250K|  2197K|   290   (2)|
--------------------------------------------------------------------

CREATE TABLE pivot_table AS
SELECT * FROM (
  SELECT program_id, customer_id, 1 CNT
  FROM airplanes)
PIVOT (SUM(cnt)
FOR customer_id
IN ('AAL' AS AAL, 'DAL' AS DAL, 'ILC' AS ILC, 'NWO' AS NWO, 'SAL' AS SAL, 'SWA' AS SWA, 'USAF' AS USAF));

desc pivot_table

SELECT * FROM pivot_table;

SELECT * FROM pivot_table
UNPIVOT (sumx FOR AAL IN (AAL AS 'AAL', DAL AS 'DAL', ILC AS 'ILC', NWO AS 'NWO', SAL AS 'SAL', SWA AS 'SWA', USAF AS 'USAF'))
ORDER BY 2,1;
Unpivot with GROUP BY conn scott/tiger@pdborcl

SELECT *
FROM (
  SELECT ename, job, sal, comm
  FROM emp)
  UNPIVOT (income_component_value FOR income_component_type
  IN (sal, comm))
ORDER BY 1;

SELECT *
FROM emp
WHERE ename = 'ALLEN';

SELECT ename, job, SUM(income_component_value) income
FROM (
  SELECT ename, job, sal, comm
  FROM emp)
  UNPIVOT (income_component_value FOR income_component_type
  IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;

EXPLAIN PLAN FOR
SELECT ename, job, SUM(income_component_value) income
FROM (
  SELECT ename, job, sal, comm
  FROM emp)
  UNPIVOT (income_component_value FOR income_component_type
  IN (sal, comm))
GROUP BY ename, job
ORDER BY 1;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes   | Cost (%CPU)|
------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |   28 |   728   |    7   (15)|
|  1 |  SORT GROUP BY       |      |   28 |   728   |    7   (15)|
|* 2 |   VIEW               |      |   28 |   728   |    6    (0)|
|  3 |    UNPIVOT           |      |      |         |            |
|  4 |     TABLE ACCESS FULL| EMP  |   14 |   280   |    3    (0)|
------------------------------------------------------------------

SELECT *
FROM emp
WHERE ename = 'ALLEN';
 
Set Operators
INTERSECT <expression> INTERSECT <expression>
SELECT DISTINCT table_name
FROM user_tables
INTERSECT
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
INTERSECT
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |   608 |     4  (75)|
|  1 |  INTERSECTION          |              |      |       |            |
|  2 |   SORT UNIQUE NOSORT   |              |  141 |   564 |     1   (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |     1   (0)|
|  4 |   SORT UNIQUE          |              |   11 |    44 |     3   (0)|
|  5 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3   (0)|
--------------------------------------------------------------------------
MINUS <expression> MINUS <expression>
SELECT DISTINCT table_name
FROM user_tables
MINUS
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
MINUS
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |  141 |   608 |     4  (75)|
|  1 |  MINUS                 |              |      |       |            |
|  2 |   SORT UNIQUE NOSORT   |              |  141 |   564 |      1  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |   SORT UNIQUE          |              |   11 |    44 |      3  (0)|
|  5 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------
UNION ALL <expression> UNION ALL <expression>
SELECT DISTINCT table_name
FROM user_tables
UNION ALL
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
UNION ALL
SELECT DISTINCT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |  152 |   608 |     4  (75)|
|  1 |  UNION-ALL             |              |      |       |            |
|  2 |   INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |     1   (0)|
|  3 |   HASH UNIQUE          |              |   11 |    44 |     3   (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3   (0)|
--------------------------------------------------------------------------
UNION <expression> UNION <expression>
SELECT DISTINCT table_name
FROM user_tables
UNION
SELECT DISTINCT table_name
FROM user_indexes;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
UNION
SELECT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

-- note that Oracle used a UNION ALL followed by a SORT UNIQUE
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              | 1140 |  4560 |     4  (75)|
|  1 |  SORT UNIQUE           |              | 1140 |  4560 |     4  (75)|
|  2 |   UNION-ALL            |              |      |       |            |
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |     1   (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3   (0)|
--------------------------------------------------------------------------

Related Topics
Cast
Conditions
Delete Statements
Hierarchical Queries
Insert
Regular Expressions
Select Statments
Update Statements
User Defined Operators
Where Clause
Wildcards

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved