Work with WITH option in Oracle 12c!


Starting Oracle 9.0  – according to SQL 1999 standard – Oracle introduced the WITH option which can be considered
as the extension of the inline views.
Let’s see an “old” example:

WITH
dept_costs  AS
(SELECT department_name, SUM(salary) as dept_total
FROM employees, departments
WHERE employees.department_id =
departments.department_id
GROUP BY department_name),
avg_cost  AS
(SELECT SUM(dept_total)/COUNT(*) as dept_avg
FROM   dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT dept_avg FROM  avg_cost)
ORDER BY department_name;

(The origin of this statement is Oracle course,titled:
Oracle Database: SQL Fundamentals I
Volume II • Student Guide).
In Oracle 12c we have new enhancements for this kind of SQL statement:
We can define “in-line” functions or procedures after the WITH clause.
In the following example we are looking for those departments, where
total salary of the department is greater than the maximum of the average of the total salaries:

WITH
FUNCTION tax(p_amount IN NUMBER)
RETURN NUMBER
IS
m  NUMBER;
BEGIN
IF p_amount <8000 THEN
m:=0.08;
ELSIF p_amount <18000 THEN
m:=0.25;
ELSE
m:=0.3;
END IF;
RETURN p_amount * m;
END;
emp_costs  AS (
SELECT d.department_name dept_name,e.last_name, e.salary AS salary,
tax(e.salary) AS tax_amount
FROM   employees e JOIN departments d
ON     e.department_id = d.department_id),
dept_costs    AS (
SELECT dept_name, SUM(salary) AS dept_sal,SUM(tax_amount) tax_sum,
AVG(salary) avg_sal
FROM   emp_costs GROUP BY dept_name)
SELECT * FROM   dept_costs
WHERE  dept_sal > (SELECT MAX(avg_sal) FROM dept_costs)
ORDER BY dept_name;
/

The result is:

DEPT_NAME             DEPT_SAL    TAX_SUM    AVG_SAL
------------------------------ ---------- ---------- ----------
Accounting               20308       5077      10154
Executive                58000      15700 19333.3333
Finance                  51608       9094 8601.33333
IT                       28800       3834       5760
Purchasing               24900       3862       4150
Sales                   304500      62083 8955.88235
Shipping                156400      15266 3475.55556

7 rows selected.

The execution plan is the following ( not very simple!):

Plan hash value: 38700341

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |       |       |     7 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION      |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                |                           |       |       |            |          |
|   3 |    HASH GROUP BY                |                           |    27 |   621 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                           |       |       |            |          |
|   5 |      NESTED LOOPS               |                           |   106 |  2438 |     3   (0)| 00:00:01 |
|   6 |       TABLE ACCESS FULL         | EMPLOYEES                 |   107 |   749 |     3   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | DEPT_ID_PK                |     1 |       |     0   (0)|          |
|   8 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS               |     1 |    16 |     0   (0)|          |
|   9 |   SORT ORDER BY                 |                           |    27 |  1512 |     4   (0)| 00:00:01 |
|* 10 |    VIEW                         |                           |    27 |  1512 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
|  12 |     SORT AGGREGATE              |                           |     1 |    13 |            |          |
|  13 |      VIEW                       |                           |    27 |   351 |     2   (0)| 00:00:01 |
|  14 |       TABLE ACCESS FULL         | SYS_TEMP_0FD9D6694_1CABAF |    27 |   621 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
10 - filter("DEPT_SAL">)

====================================================================================================</pre>

We can use the WITH option with plsql_declarations clause
in DML statements as well.
Let’s consider the following example where we want to compute the
tax amount of the salaries and modify the tax_amount column,
but we DON’T WANT TO USE the stored tax function if exists at all!
First we create a copy of the employees table and modify the
structure with a new column

CREATE TABLE newmp AS SELECT * FROM employees;
ALTER TABLE newemp ADD tax_amount NUMBER(10,2);

Now we modify the content of the tax_amount column
(which is originally empty) with each employee’s tax amount:

UPDATE     /*+ WITH_PLSQL */ newemp E
SET tax_amount=(WITH FUNCTION TAX(P_AMOUNT IN NUMBER)
RETURN NUMBER
IS
M  NUMBER;
BEGIN
IF P_AMOUNT <8000 THEN
M:=0.08;
ELSIF P_AMOUNT <18000 THEN
M:=0.25;
ELSE
M:=0.3;
END IF;
RETURN P_AMOUNT*M;
END;
SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)
/

Observe that the WITH statement has a special hint.
The WITH_PLSQL hint only enables you to specify
the WITH plsql_declarations clause within the statement.
It is not an optimizer hint.(see Oracle 12c documentation)

Let’s check the result (only the first 10 rows are displayed):


SELECT *
FROM
(SELECT  last_name,salary,tax_amount from newemp
ORDER BY  salary DESC)
WHERE ROWNUM<=10;

LAST_NAME                          SALARY TAX_AMOUNT
-------------------------      ---------- ----------
King                                24000       7200
Kochhar                             17000       4250
De Haan                             17000       4250
Russell                             14000       3500
Partners                            13500       3375
Hartstein                           13000       3250
Greenberg                           12008       3002
Higgins                             12008       3002
Errazuriz                           12000       3000
Ozer                                11500       2875

10 rows selected.

The execution plan for the UPDATE is the following(simpler!)

Executions:1 | is_bind_sensitive:N  | is_bind_aware: N | Parsing schema:HR | Disk reads:0 | Consistent gets:505
SQL_ID  6s126hzr93g2d, child number 0
-------------------------------------
UPDATE  /*+ WITH_PLSQL */ newemp E SET tax_amount=(WITH FUNCTION
TAX(P_AMOUNT IN NUMBER) RETURN NUMBER IS M  NUMBER; BEGIN IF P_AMOUNT
<8000 THEN M:=0.08; ELSIF P_AMOUNT <18000 THEN M:=0.25; ELSE M:=0.3;
END IF; RETURN P_AMOUNT*M; END; SELECT tax(salary) FROM employees m
WHERE m.employee_id=e.employee_id)

Plan hash value: 759157450

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |               |       |       |   217 (100)|          |
|   1 |  UPDATE                      | NEWEMP        |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | NEWEMP        |   107 |  1819 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |     8 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("M"."EMPLOYEE_ID"=:B1)

====================================================================================================

The greatest benefit of using PL/SQL functions or procedures that we
can use our (not stored) subprograms in queries without writing
procedural code.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: