Replay eygle's example in the speech at the Oracle conference to see what is the master's starting point and face, replay eygle

Replay eygle's example in the speech at the Oracle conference to see what is the master's starting point and face, replay eygle

In his speech at the just-concluded Oracle conference, the company demonstrated the optimization of vertices and surfaces through a simple UPDATE statement, what is the knowledge coverage of points and surfaces? It is not about how to operate the case. We should pay more attention to or learn more about this learning attitude and Methods. How can the Masters combine them? I think this case can give us some inspiration.

 

Next we will repeat the entire process of this case. Note: copyright belongs to all rights of the general website (eygle ~

 

Problem description:

The title of the problem is: "parallel update becomes a bottleneck of the system"

SQL:

UPDATE /*+ parallel(a, 8) */ tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);

This SQL statement takes a very long time to execute. From the introduction, it takes 2.5 minutes.

 

Optimization process:

1. In order to better illustrate the problem, we have simplified the SQL statement. What we need to optimize is this SQL statement:

UPDATE tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT id FROM tbl_b b);

We create two simulated tables:

SQL> create table tbl_a(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create table tbl_b(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create sequence seq_a cache 1000;Sequence created.SQL> create sequence seq_b cache 1000;Sequence created.

Insert some random data:

begin  for i in 1 .. 100000 loop    insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_a;  COUNT(*)------------     100000begin  for i in 1 .. 10000 loop    insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_b;  COUNT(*)------------      10000

2. Execute the original SQL statement

SQL> set timing onSQL> UPDATE tbl_a a          SET name = (SELECT name FROM tbl_b WHERE id = a.id),                 class = (SELECT class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:07.42

It takes more than 7 seconds (although there is a gap with the 2.5 minutes in the example, the time gap can be ignored only to illustrate the optimization problem ).

3. First Optimization

We can see from this SQL statement that the ID column of the TBL_A table is updated, but the SELECT column of the TBL_ B table has three times, that is, three full table scans, so if the number of TBL_ B table searches can be reduced, the execution time can certainly be reduced.

SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:04.04

This adjustment is in line with the SQL syntax. The execution time is changed to more than 4 seconds, and the effect is remarkable.

 

4. Second Optimization

Although the execution time is reduced by nearly half, the SQL still performs two scans on TBL_ B. Can it be reduced?

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;SET name = b_name, class = b_class    *ERROR at line 4:ORA-01779: cannot modify a column which maps to a non key-preserved tableElapsed: 00:00:00.01

This does only scan the TBL_ B table once, directly updating the child query, but this reported an error, ORA-01779,

This introduces the non key-preserved table concept. This error was mentioned in elders Yang's blog (http://blog.itpub.net/4227/viewspace-195889/) for non-key-value-saving tables:

"The reason for this error is that the updated column is not a column in the fact table, but a column in the dimension table. In other words, if two tables are associated, and one of the table's associated columns is the primary key, the other table is the fact table, that is, the columns in the other table are updatable; this table cannot be updated unless the associated column of another table is also the primary key. If the update statement involves this table, a ORA-1799 error occurs. If two tables are associated with primary keys, you can update the fields of the table.

In fact, the real reason for this restriction is that Oracle should ensure that the updated content after the connection can be written to a table, which requires that the connection method must be 1-to-N or 1-to-1 connections. In this way, the number of result sets after the connection is consistent with that of the fact table. This allows Oracle to smoothly update sub-queries after connection to the fact table ."

A. id = B. id. We use the id column of TBL_ B as the condition update. Make sure that this column only corresponds to a row of records in the TBL_ B table, you can set the primary key, unique index, or unique constraint for the id column of TBL_ B. Here, set the unique constraint:

SQL> alter table tbl_b add constraint uq_b_id unique(id);Table altered.

Execute again:

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;10000 rows updated.Elapsed: 00:00:00.12

The execution time is 0.12 seconds.

If the ID column of TBL_A is set as the primary key, the connection is 1-to-1. If the ID column of TBL_ B is only a unique constraint, the connection is 1-to-N.

 

Summary:

After two optimizations, the execution time is reduced from 7 seconds to 0.12 seconds. Although the sample data here may not be the same as the actual situation, the proportional scaling is sufficient to illustrate this problem. From this case, we can see that, the essence of optimization is to do less. When the original SQL statement executes three full table scans, the goal is to reduce the number of full table scans. The first optimization operation may be relatively easy to think of, but the second optimization operation, you need to know that you can have this syntax, and there is a ORA-01799 error, you also need to know what is the root cause of this error, in order to have a feasible solution.

 

The problem is not over yet. The above describes the optimization of SQL statements. The following is the knowledge about this SQL statement.

Assume that the preceding TBL_A and TBL_ B tables belong to user bisal. In this case, create a user phibisal and grant the simplest permissions:

SQL> create user phibisal identified by phibisal;User created.SQL> grant create session to phibisal;Grant succeeded.

The public synonyms of the two tables created by the bisal User:

SQL> create public synonym tbl_a for bisal.tbl_a;Synonym created.SQL> create public synonym tbl_b for bisal.tbl_b;Synonym created.

Then grant the phibisal user the read and update permissions on the TBL_A table:

SQL> grant select, update on tbl_a to phibisal;Grant succeeded.

After logging on to phibisal, run the following command:

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-00942: table or view does not exist

A message is prompted that TBL_ B does not exist because the user does not have any permissions for the table (Note: This is different from the feedback in the eygle example, And it prompts the ORA-01031: insufficient privileges)
If you authorize phibisal to read the TBL_ B table,

SQL> grant select on tbl_b to phibisal;Grant succeeded.

In this case, the update can be completed:

sqlplus phibisal/phibisalSQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.

However, the following SQL statement will prompt a permission error:

UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges

That is, this seed query UPDATE will report an error because it does not have the UPDATE permission for the TBL_ B table.
However, if the following with syntax is used, it can be executed normally:

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)             )SET name = b_name, class = b_class;10000 rows updated.

More thorough:

SQL> revoke update on tbl_a from phibisal;Revoke succeeded.

The phibisal user's update permission on TBL_A is revoked. It is reasonable to say that the phibisal user should not be able to update the TBL_A table.
The preceding two adjusted SQL statements are true:

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges     SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);UPDATE tbl_a a       *ERROR at line 1:ORA-01031: insufficient privileges

However, it is strange that the following SQL statements can be executed:

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)              SELECT * FROM tmp             )SET name = b_name, class = b_class;10000 rows updated.

This violates permission control in terms of principles and rules. Check the version below:

SQL> select banner from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

This is a bug proposed in July 2014, which exists in versions 11.2.0.3, 11.2.0.4, and 12.1. You need to correct this bug, which is equivalent to using the with syntax to bypass user permissions, perform DML operations on tables that do not have permissions.

 

Summary:

The essence is not this bug, but a simple UPDATE statement that can derive such a wealth of knowledge. On the one hand, we need to be able to understand every concept in terms of principle. On the other hand, we also need to cultivate our own ideas about the points and aspects of knowledge, so that we can gradually move closer to the masters, learn from masters.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.