ora-03150&ora-02055&ora-02063 of ORACLE SQL optimization

Today, as a colleague responds, he executes a stored procedure that executes into a dblink insert INTO SELECT statement times wrong: ORA-03150 ORA-02055 ORA-02063, specific error:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170823155405405?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "style=" border:0px;vertical-align:middle; "/>

The SQL statement involved in the error is:

insert INTO [email protected]_jkb
Select oae001,
akb020,
akc190,
akc220,
aae072,
akc515,
aka135,
AAE100,
akc301,
B.reject_money,
B.reject_reson,
B.OPER_USER_ID,
B.oper_user_name,
B.oper_date,
' 0 ',
' EH ',
' EH ',
Sysdate,
Null
Null
From KC22 A
INNER JOIN Dr_audit b
On a.oae001 = B.prescription_no
and B.load_date > (select Syncdate from Dr_syncdate)
and a.aae100 = ' 1 ';

The following is an optimized processing process:

1, first, let it count the insert into select of the data volume of 3,394, not too much, but in the execution of INSERT into sub-query Select the process is very long, there are more than 10 minutes;

2. View the SQL execution plan for INSERT into subquery as follows:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170823155814711?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "style=" border:0px;vertical-align:middle; "/>

3, let the implementation of the colleague control sub-query data volume of 1, although the execution time is longer, but can be executed successfully

4, the proposed implementation colleague, according to the Where condition of the SELECT clause to create an index to the field AAE100 of table KC22, after the index is created, the subquery statement execution speed significantly increased, the execution plan is as follows:

650) this.width=650; "Src=" http://img.blog.csdn.net/20170823160100256?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqvd3d3x3h1zv94aq==/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Center "style=" border:0px;vertical-align:middle; "/>

5. Then, the implementation colleague executes the insert into select,2 minute or so successfully executes.

Problem Solving!

This article is from the "Breeze ai ai" blog, please be sure to keep this source http://3596022.blog.51cto.com/3586022/1976244

ora-03150&ora-02055&ora-02063 of ORACLE SQL optimization