Plsql_ Performance Optimization Series 16_oracle Datascan data scanning

read operations on data are very resource-intensive, and how to reduce the scanning of data is an important aspect of improving SQL efficiency, such as materialized view technology . This article describes several SQL notation, namely case EXPRESSION/DML with returning clause/multitable insert. [@[email protected]]

First, use case expression to combine multi-sentence queries together
SELECT COUNT (*)
From Employees
WHERE Salary < 2000;
SELECT COUNT (*)
From Employees
WHERE salary between and 4000;
SELECT COUNT (*)
From Employees
WHERE salary>4000;

Rewrite into
SELECT COUNT (case when salary < 2000
Then 1 ELSE null END) Count1,
COUNT (case when salary between 2001 and 4000
Then 1 ELSE null END) Count2,
COUNT (case when salary > 4000
Then 1 ELSE null END) Count3
From employees;

Second, DML with returning clause
Sometimes a DML operation is followed by a select operation on these rows. DML with RETURNING clause combines insert/update/delete operations with select operations to avoid duplicate scans.
Example:
UPDATE Employees
SET job_id = ' Sa_man ', salary = salary + +, department_id = 140
WHERE last_name = ' Jones '
Returning salary*0.25, last_name, department_id
Into:bnd1,: Bnd2,: Bnd3;
If you modify multiple rows of records, returning clause is returned to the array variable.

Third, multitable insert
One sentence of SQL contains multiple insert notation. Compared to multiple INSERT statements, the former simply scans the source table once, while the latter is scanned multiple times. This is very different in the case of large amounts of data. Multitable Insert can be divided into conditional (conditional), Unconditional,insert all (performs all inserts), insert first (only performs insert that satisfies the condition), and other usages
Sometimes a row of records in a table contains multiple types of information, such as a single table that contains the cost of a device's various types of traffic, which we call a non-relational table. The following example uses Oracle multitable Insert technology to convert a non-relational table to a relational table.
CREATE TABLE test_table (id1 number,val_1 number,id2 number,val_2 number); --Non-relational tables
INSERT into test_table values (1,101,1,202);
INSERT into test_table values (6,666,7,777);

--base table
CREATE TABLE Id_type (a number);
INSERT into Id_type values (1);
INSERT into Id_type values (2);
INSERT into Id_type values (3);
--Target table (convert non-relational tables to two relational tables)
CREATE TABLE id1_table (ID number,val number);
CREATE TABLE id2_table (ID number,val number);

--common method, with multiple INSERT statements
Insert INTO id1_table select Id1,val1 from test_table;
Insert INTO id2_table select Id2,val2 from test_table;

--use multiple insert to split a record of non-relational table data into two strips, inserting two target tables respectively
Insert All
into id1_table values (id1,val_1)
into id2_table values (id2,val_2)
Select id1,val_1,id2,val_2 from test_table;

-Multiple insert with judging conditions
Insert All
When Id1 =1 Then
into id1_table values (id1,val_1)
When ID2 =5 Then
into id2_table values (id2,val_2)
Select id1,val_1,id2,val_2 from test_table;

--multiple Insert also has a more complex notation, judging conditions can be queried with subqueries
Insert All
When id1 in (select a from Id_type) then
into id1_table values (id1,val_1)
When ID2 in (select a from Id_type) then
into id2_table values (id2,val_2)
Select id1,val_1,id2,val_2 from test_table;

Plsql_ Performance Optimization Series 12_ Avoid data duplication scanning (2) Improve performance with the WITH AS clause

The previous article introduced several SQL methods to avoid repeated scanning of data, this article describes the use of complex subqueries with as to improve SQL performance. Let's look at an example: with Dept_costs as (SELECT d.department_name, SUM (e.salary) as Dept_total from Employees e JOIN departments DON e.de partment_id = d.department_id GROUP by d.department_name), Avg_cost as (SELECT SUM (dept_total)/count (*) as dept_avg from de pt_costs) SELECT * from dept_costs where dept_total > (SELECT dept_avg from Avg_cost) ORDER by Department_name; In this example, DEP T_costs is the equivalent of a view, unlike view, ...

Plsql_ Performance Optimization Series 16_oracle Datascan data scanning