Oracle SQL query optimization. Part4

Insert operation:

1. Copy the table structure without adding data:

--Copy table structure without inserting data create TABLE Emp_new as SELECT * from emp where 1 = 2;select * from emp_new;

2. With CHECK option, the mating view allows you to make some restrictions for inserting data operations:

--with check optiom restricting the insertion of data into the insert (select Empno, Deptno, EmpName, Empsalary, Empdesc from               emp              where emp.dept No <> ' dept02 '--with CHECK option            values (' emp008 ', ' dept02 ', ' Ross ', 7000, ' have strong insight into industry trends, have integrated global capabilities ');

Execution error: ORA-01402: View with CHECK optidn violates WHERE clause

3. Multiple table inserts:

Here are three kinds of multiple-table inserts: A. Unconditional insert;b. Conditional insert;c. Insert first.

• Unconditional Insert All

Empty the EMP and Emp_bak first, and then execute the SQL below:

--Unconditional Insert Allinsert allinto emp_new1into emp_new2select * from EMP;

This is the result of inserting the EMP data into the Emp_new1, Emp_new2 table, and performing the select * from Emp_new1 as follows (and so is the Emp_new2 dataset):

• Conditional Insert All
  

--Conditional Insert Allinsert Allwhen Empsalary < and into   Emp_new1when empsalary > when into   emp_new2s elect * from EMP;

After the upper SQL is executed, the employee information for payroll (empsalary) Less than 5000 is inserted into the Emp_new1, and the Payroll (empsalary) is greater than 3000 of the employee information inserted into DEPT_NEW2. where Empsalary is 4000 and the Emp_new1 and emp_new2 are inserted at the same time, it is sometimes necessary to insert the specified table, which is said below :

• Insert First

Insert first if there is a condition in front of you, the table behind does not insert the corresponding row:

--Insert Firstinsert Firstwhen Empsalary < The into   Emp_new1when empsalary > and into   emp_new2w Hen empsalary > "Into   emp_new3select" * from EMP;

EMP_NEW2 does not insert this record with Empsalary 4000, EMP_NEW3 does not have a record inserted.

Second, update updates operation:

1. To update with the Select subquery, care should be taken to avoid full table updates:

Data preparation, first set the Empdesc of all records in Emp_new1 to "not filled"

Now you are ready to update the EMPDESC of the empno corresponding record in the EMP table according to the records in the Emp_new1 table, many people will write the following sql:

--Full table update with SELECT for update update emp   set emp.empdesc =        (select Empdesc from          emp_new1         where emp.empno = Emp_new1.empno       );

The result above shows that the SQL for this update operation has a full table scan. records that are not matchedto the empno are updated to null. In fact, you should add the where statement to be correct:

Update emp   Set emp.empdesc =        (select Empdesc from          emp_new1         where emp.empno = Emp_new1.empno       ) where Exists (select 1 from          emp_new1         where emp.empno = Emp_new1.empno       )

2. Merge into statement:

For the top statement, you can use the merge into statement

--merge into is implemented by merge into empusing (SELECT * from          emp_new1      ) E on   (e.empno = emp.empno) while matched then  Update Set emp.empdesc = E.empdesc;

The result of the operation is the same as in 1, and the merge into method is recommended if you make a decision. Because merge into accesses only one emp_new1.

Iii. Deleting delete operations

1. Delete duplicate records:

There are many kinds of methods, here only one, using the analysis function grouping, determine whether the group number is greater than 1.

Delete from  emp where rowid in (select Rid from                    (select rowID as RIDs,                                row_number () over (partition by empsalary ORDER BY empno ASC) as SEQ (from                           emp                         )                   where seq > 1                 );

However, if you need to delete exactly one of the two data, you need to add all the column names behind partition by, otherwise deleting the data is not certain, and deleting the record is related to the order by statement.

Oracle SQL query optimization. Part4