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 Mate view can 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 a strong insight into industry trends. Ability to co-ordinate global capabilities ');

Run 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 run the SQL below:

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

This is the same time that the EMP data is inserted into the emp_new1, Emp_new2 table, the results of running SELECT * from emp_new1 such as the following (the EMP_NEW2 data set is also the same):

• Conditional Insert All
  

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

After running the upper SQL, employee information for payroll (empsalary) Less than 5000 is inserted into Emp_new1. Employee Information for payroll (empsalary) greater than 3000 is inserted into the dept_new2. Emp_new1 and Emp_new2 are inserted at the same time as Empsalary 4000, and sometimes the specified table needs to be inserted. This is said below :

• Insert First

Insert first assumes that there is a condition in front. The following table will 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;

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

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

Second, update updates operation:

1. Update with the Select subquery. Care should be taken to avoid full table updates:

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

Now you are ready to update the EMPDESC in the EMP table empno corresponding 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 is 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       )

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

2. Merge into statement:

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

--Merge into implementation method merge into Empusing (SELECT * from          emp_new1      ) E on   (e.empno = emp.empno) when matched then
    update set emp.empdesc = E.empdesc;

The execution results are the same as the results in 1, assuming inference. It is recommended to use the merge into method.

Because merge into only visits once emp_new1.

Iii. Deleting delete operations

1. Delete Repeated records:

There are many ways to do this, just one, using analytic functions to group. Infers whether the grouping ordinal 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                 );

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

However, assuming that one of the same two data is deleted, it is necessary to add all the column names behind the 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