Shulou(Shulou.com)05/31 Report--

Editor to share with you how SQL can change those CBO powerless implementation plans, I hope you will learn something after reading this article, let's discuss it together!

User-written sql, Oracle will be equivalent to rewrite, even if the RBO optimization mode, Oracle will also do some conversion for you, these transformations are based on a fixed algorithm, oracle called the conversion is "heuristic". For example, when we write inner join and only access single table data, Oracle will automatically be reduced to semi-join, and then use semi join to do join for you. Transformation is a necessary step for Oracle, and transformation has been around at least since version 8.05.

There are many optimization rules on the Internet, some say that exists is more efficient than in, and some say that in executes faster than exists, it depends on how SQL is written, how CBO is converted, and whether it can be converted? Of course, this transformation is not based on cost but "heuristic transformation".

When Oracle cannot do transformation, it may be when there is a problem with sql, so it is time for us to find the cause. Here are some cases to illustrate the situation where the optimizer is powerless (table names and some columns have been renamed to protect customer privacy).

| | replace update with merge |

The UPDATE association update has been running for nearly 40 minutes. The SQL statement is as follows:

UPDATE PRO_S_ACCT A SET ACCT_SKID = (SELECT ACCT_SKID FROM ACCT_S_BK B WHERE A.ACCT_ID = B.ACCT_ID)

The implementation plan is as follows:

Check the amount of data in the scale, including 1044227 rows in PRO_S_ACCT and 553554 rows in acct_s_bk.

UPDATE is followed by a subquery similar to a nested loop. Pro_s_acct is a nested loop driven table, acct_s_bk is a driven table, then the table acct_s_bk will be scanned more than 1 million times, it will produce a large number of logical reads, the driven table will scan the whole table, and we can build an index on it, but at this time the index will be scanned more than 1 million times.

Let's build an index and see that the execution plan is as follows:

Create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID)

Let's take a look at the execution plan by using merge into equivalent rewriting:

Merge into PRO_S_ACCT A using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID) when matchedthen updateset a.ACCT_SKID = B.ACCT_SKID

MERGE INTO is free to control nesting loops or hash connections, and we can turn on parallel DML updates of the corresponding size when the usage data of the driven table and the driven table exceeds 1G.

Merge / * + PARALLEL (8) * / into PRO_S_ACCT Ausing ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID) when matchedthen updateset a.ACCT_SKID = B.ACCT_SKID

In the actual execution, 2s is completed.

The following is rewritten by sql to make the execution plan of sql under our control.

UPDATE INXX I SET (I.INTRANFRMFRMFRMFRMITHING DTJI. INTERTROWTH TOBINGTONDESCI. ACCTTONDESC) = (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE DBPP.ACCT_DESC FROM DBPP WHERE DBPP.SYS_ID='INV' AND DBPP.ACCT_TYPE = I.ACCT_TYPE AND DBPP.INT_CAT = I.INT_CAT) WHERE I.EXTDATE = TO_DATE ('2018-04-03') 'YYYY-MM-DD') AND EXISTS (SELECT DBPP.SYS_ID FROM DBPP WHERE DBPP.SYS_ID='INV' AND DBPP.ACCT_TYPE = I.ACCT_TYPE AND DBPP.INT_CAT = I.INT_CAT AND DBPP.ACCT_DESC =' S')

Merge / * + parallel (10) use_hash (iMagne X) swap_join_inputs (X) * / into INXX I using (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC,DBPP.ACCT_TYPE,DBPP.INT_CAT FROM DBPP WHERE DBPP.SYS_ID='INV' AND DBPP.ACCT_DESC ='S') xon (x.ACCT_TYPE = I.ACCT_TYPE AND x.INT_CAT = I.INT_CAT) when matched then update set I.INT_FRM_DT=x.CR_SOP_DATE I.INTERTROWTH TOBREDATX.EOPMETALDATEL I.ACCTRODESCX.ACCTTONDESC WHERE I.EXTDATE = TO_DATE ('2018-04-03

Another similar case:

Update WWW a set a.cny_bal=a.ll_bal*nvl ((select b.hl from MMM b where b.startdate a.extdate and b.zbaked flowers CNY 'anda.curr=b.yb), 0) where a.extdate=to_date (' 2018-04-01mm Murray dd') Since the www table is partitioned by day, the partition field is extdate Then it can be rewritten as follows: merge / * + parallel (8) * / into www a using (select b.hl from MMM b where b.zbaked and b.startdate=ADD_MONTHS CNY 'and b.enddate > date'2018-04-01' and b.startdate=ADD_MONTHS (TO_DATE (' 2018-04-27mm),-12) AND RQ=ADD_MONTHS (TO_DATE ('2018-04-27mm),-12) AND RQ=ADD_MONTHS (TO_DATE (' 2018-04-27') 'YYYY-MM-DD'),-12) AND RQ

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.