Effective Sql

Effective SQL, PL/SQL Using • • • • •

Multi-Table Insert Data Aggregation Merge Statement Analytical Functions BulkCollect and ForAll

May 2007

Multi-Table Insert •

INSERT … SELECT

In a multi table insert, you insert computed rows derived from the rows returned from the evaluation of a sub query into one or more tables. Types: • Unconditional Insert • Conditional Insert • Conditional First Insert • Pivoting Insert

Current Scenario : INSERT INTO revenue (person,month,revenue) SELECT person, 'Jan', rev_jan FROM revert UNION ALL SELECT person, 'Feb', rev_feb FROM revert UNION ALL SELECT person, ‘Apr‘ ,rev_mar FROM revert ; With Multi Table Insert: Insert ALL into revenue values (person ,'JAN', rev_jan) into revenue values (person, 'FEB', rev_feb) into revenue values (person,‘ MAR', rev_mar) Select person, rev_jan, rev_feb, rev_mar, rev_apr, rev_may, rev_jun from revert;

Unconditional INSERT Insert all The rows fetched by the select

INSERT ALL INTO hbg2 VALUES (emp_no,name ) INTO hbg1 VALUES (emp_no,name) SELECT emp_no, name FROM hbg WHERE emp_no > 2;

Conditional INSERT ALL

When the Row Meets this Predicate – Insert

INSERT ALL WHEN emp_no > 4 THEN INTO hbg2 VALUES (emp_no,name ) WHEN emp_no <= 4 THEN INTO hbg1 VALUES (emp_no,name) SELECT emp_no,name FROM hbg WHERE emp_no > 2;

Conditional INSERT FIRST Check for the First Satisfying Condition

INSERT FIRST WHEN name ='Harsh' THEN INTO hbg1 VALUES (emp_no,name) WHEN emp_no = 2 THEN INTO hbg2 VALUES (emp_no,name) WHEN emp_no >2 and emp_no < 4 THEN INTO hbg3 VALUES (emp_no,name) ELSE INTO hbg4 VALUES (emp_no,name) SELECT emp_no, name FROM hbg;

Pivoting INSERT

All the Inserts are in same table

INSERT ALL INTO sales VALUES (emp_id,name,region1) INTO sales VALUES (emp_id,name,region2) INTO sales VALUES (emp_id,name,region3) SELECT emp_id, name, region1,region2, region3 FROM sales_main;

Tips and Fine Points •

Specifying FIRST tells oracle to skip subsequent WHEN clauses after it finds one true for the row to be evaluated.

•

We can also use an ELSE clause to tell oracle what to do if none of the WHEN clauses evaluates to true

•

Aliases name of the column given in the select can be used in conditions as well as insert values.

•

Watch For Sequences.

•

Instead of using Sqlldr to load data into multiple table , multi table inserts coupled with an external table can be used.

MERGE Statement •

This specialized statement combines insert and update into a single operation. It is intended for applications that perform particular patterns of inserts and updates.

•

Previous approach for achieving the same was: - Insert, if failed on primary key then update - Update, if SQL%NOTFOUND then insert.

• •

Avoid Multiple update statements. Now, a single SQL statement does the job.

Syntax We can conditionally insert rows into table B from table A with help of single SQL statement. MERGE INTO high_score_copy B  Target table USING high_score A - source (can be a table or view or subquerry) ON ( A. name=B.name) condition to either update or insert

WHEN MATCHED THEN UPDATE SET B.score=A. score WHEN NOT MATCHED THEN INSERT VALUES (A.name,A.score);

Classic Example

Merge into accountattributes a using dual On (a.Account_num = ‘acc00010102') When matched Then update Set a.Marital_status =‘single' When not matched Then insert (a.Account_num, a.Marital_status) Values (‘acc00010102',‘single')

Data Aggregation • ROLLUP Used to calculate sub-totals • CUBE Used to get cross-tabulation results • Grouping Sets For Multiple Groupings • With Clause Materialize the Repeated Inline Views

With Clause • The WITH clause (aka subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. • This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. • Using the WITH clause, Oracle retrieves the results of a query block and stores them in the user's temporary table space. With data as (Select rownum r from all_objects Where rownum< 50) Select sysdate + r from data

Tips and Fine Points • All the group by enhancements make only one pass over the base table • Code readability is improved • Gets you different variations in aggregating data

Analytics Ordered Array Semantics in SQL queries

Select deptno,ename,sal Row_number() over (partition by deptno Order by sal desc ) from emp SCOTT 3000 11 Deptno Ename Sal King 5000 FORD 3000 22 10 Clark 2450 JONES 2975 33 Miller 1300 20 ADAMS 1100 4 5 SMITH 800 30

Analytics •

And the list is infinitely long – "Analytics are the coolest thing to happen to SQL since the keyword Select" – Lets look at a complex example

•

RANK

•

DENSE_RANK

•

ROW_NUMBER

•

FIRST_VALUE / LAST_VALUE

•

LEAD

•

LAG

Classic Problem I have records like this: Time 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003

12:22:01 12:22:03 12:22:04 12:22:45 12:22:46 12:23:12 12:23:12

Amount 100 200 300 100 200 100 200

What I need to do is sum the amounts where the time of the records is within 3 seconds of each other. In the case where the data is like this: 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003 11/22/2003

12:22:03 12:22:04 12:22:05 12:22:06 12:22:07 12:22:08 12:22:09

200 200 200 200 200 200 200

There would only be one row with the total for all the rows. (Basically, we are looking for "instances" where we define an instance such that all the records within the instance are no more than three seconds apart. So there can be 1 or many records all of the same instance and the resulting summation would have one summary record per instance.) Would you please point me in the right direction?

Analytics on Analytics •

Start with first row (thinking iteratively here) – If prior row is within 3 seconds -- same group, continue • Abs(lag(x) over (order by x)-x) <= 3 seconds – Else new group, break and get a "new" group id – Need to use analytics on top of analytics • Inline views -- very powerful here Analytics at two levels Select min(x), max(x), sum(y) from ( select x, y, max(rn) over (order by x) max_rn from ( select x, y, lag(x) over (order by x), case when abs(lag(x) over (order by x) - x) > 3/24/60/60 then row_number() over (order by x) end rn from t)) group by max_rn order by 1

BULK COLLECT and FORALL •

Improve the performance of multi-row SQL operations by an order of magnitude or more with bulk/array processing in PL/SQL!

CREATE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE, newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = newsal_in WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept;

“Conventional binds”

Conventional Bind

Oracle server SQL Engine

PL/SQL Runtime Engine PL/SQL block FOR rec IN emp_cur LOOP UPDATE employee SET salary = ... WHERE employee_id = rec.employee_id; END LOOP;

Procedur al statement executor

Performance penalty for many “context switches”

SQL statement executor

Enter the “Bulk Bind”

Oracle server0 SQL Engine

PL/SQL Runtime Engine PL/SQL block FORALL indx IN deptlist.FIRST.. deptlist.LAST UPDATE employee SET salary = ... WHERE employee_id = deptlist(indx);

Procedural statement executor

SQL statement executor

Much less overhead for context switching

Use the FORALL Bulk Bind Statement • Instead of executing repetitive, individual DML statements, you can write your code like this: PROCEDURE remove_emps_by_dept (deptlist dlist_t) IS BEGIN FORALL aDept IN deptlist.FIRST..deptlist.LAST DELETE FROM emp WHERE deptno = deptlist(aDept); END;

• Things to be aware of: – – –

You MUST know how to use collections to use this feature! Only a single DML statement is allowed per FORALL. SQL%BULK_ROWCOUNT returns the number of rows affected by each row in the binding array.

Use BULK COLLECT INTO for Queries

Declare a collection of records to hold the queried data.

Use BULK COLLECT to retrieve all rows.

Iterate through the collection contents with a loop.

DECLARE TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; l_employees employees_aat; BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP process_employee (l_employees(indx)); END LOOP; END;

Limit the number of rows returned by BULK COLLECT CREATE OR REPLACE PROCEDURE bulk_with_limit (deptno_in IN dept.deptno%TYPE) IS CURSOR emps_in_dept_cur IS SELECT * FROM emp WHERE deptno = deptno_in; TYPE emp_tt IS TABLE OF emp%ROWTYPE; emps emp_tt; BEGIN OPEN three_cols_cur; LOOP FETCH emps_in_dept_cur BULK COLLECT INTO emps LIMIT 100; EXIT WHEN emps.COUNT = 0; process_emps (emps); END LOOP; END bulk_with_limit;

Use the LIMIT clause with the INTO to manage the amount of memory used with the BULK COLLECT operation.

WARNING! BULK COLLECT will not raise NO_DATA_FOUND if no rows are found. Best to check contents of collection to confirm that something was retrieved.

Dynamic FORALL Example •

This example shows the use of bulk binding and collecting, plus application of the RETURNING clause. CREATE TYPE NumList IS TABLE OF NUMBER; CREATE TYPE NameList IS TABLE OF VARCHAR2(15); PROCEDURE update_emps ( col_in IN VARCHAR2, empnos_in IN numList) IS enames NameList; BEGIN FORALL indx IN empnos_in.FIRST .. empnos_in.LAST EXECUTE IMMEDIATE 'UPDATE emp SET ' || col_in || ' = ' || col_in || ' * 1.1 WHERE empno = :1 RETURNING ename INTO :2' USING empnos_in (indx ) RETURNING BULK COLLECT INTO enames; ... END;

Notice that empnos_in is indexed, but enames is not.

Excellent Exception Handling for Bulk Operations •

Allows you to continue past errors and obtain error information for each individual operation (for dynamic and static SQL).

CREATE OR REPLACE PROCEDURE load_books (books_in IN book_obj_list_t) IS bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 ); BEGIN FORALL indx IN books_in.FIRST..books_in.LAST Allows processing of SAVE EXCEPTIONS all rows, even after an INSERT INTO book values (books_in(indx)); error occurs. EXCEPTION WHEN BULK_ERRORS THEN New cursor FOR indx in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP attribute, a log_error (SQL%BULK_EXCEPTIONS(indx)); pseudo-collection END LOOP; END;

Tips and Fine Points •

•

Use bulk binds in these circumstances: – Recurring SQL statement in PL/SQL loop. Oracle recommended threshold: five rows! Bulk bind rules: – Can be used with any kind of collection; Collection subscripts cannot be expressions.

•

Bulk collects: – Can be used with implicit and explicit cursors – Collection is always filled sequentially, starting at row 1

•

Also stick with a cursor FOR loop.... – If you want to do complex DML processing on each row as it is queried – and possibly halt further fetching, and you can't use SAVE EXCEPTIONS. Otherwise, moving to BULK COLLECT and FORALL is a smart move!

•