A few days ago, my colleague sent an SQL statement saying that the update operation was very slow. I checked that the data volume was not large. Check the execution plan and find that the execution path is incorrect.
A few days ago, my colleague sent an SQL statement saying that the update operation was very slow. I checked that the data volume was not large. Check the execution plan and find that the execution path is incorrect.
A few days ago, my colleague sent an SQL statement saying that the update operation was very slow. I checked that the data volume was not large. Check the execution plan and find that the execution path is incorrect. Why is the execution path incorrect? Statistics are not too problematic. Here we simulate:
Data preparation:
-- 1. Data preparation, table 1:
Drop table t_test_1;
Create table T_TEST_1
(
Owner VARCHAR2 (30 ),
Object_name VARCHAR2 (128 ),
Subobject_name VARCHAR2 (30 ),
Object_id NUMBER,
Data_object_id NUMBER,
Object_type VARCHAR2 (19 ),
Created DATE,
Last_ddl_time DATE,
Timestamp VARCHAR2 (19 ),
Status VARCHAR2 (7 ),
Temporary VARCHAR2 (1 ),
Generated VARCHAR2 (1 ),
Secondary VARCHAR2 (1)
);
Insert into T_TEST_1
SELECT * FROM dba_objects;
COMMIT;
UPDATE t_test_1 a SET a. object_type = 'table ';
COMMIT;
-- 2. Data preparation, table 2:
Drop table t_test_2;
Create table T_TEST_2
(
Owner VARCHAR2 (30 ),
Object_name VARCHAR2 (128 ),
Subobject_name VARCHAR2 (30 ),
-- The data type here is inconsistent with the object_id data type in T_TEST_1
Object_id VARCHAR2 (100 ),
Data_object_id NUMBER,
Object_type VARCHAR2 (19 ),
Created DATE,
Last_ddl_time DATE,
Timestamp VARCHAR2 (19 ),
Status VARCHAR2 (7 ),
Temporary VARCHAR2 (1 ),
Generated VARCHAR2 (1 ),
Secondary VARCHAR2 (1 ),
-- The data type here is the same as the object_id data type in T_TEST_1.
Object_id2 NUMBER
);
Insert into T_TEST_2
SELECT a. *, a. object_id object_id2 FROM dba_objects;
COMMIT;
SELECT * FROM t_test_1;
Create index ind_t_test_2_id1 ON t_test_2 (object_id) TABLESPACE TBS_LUBINSU_DATA;
Create index ind_t_test_2_id2 ON t_test_2 (object_id2) TABLESPACE TBS_LUBINSU_DATA;
The object_id and object_id2 fields in the T_TEST_2 table have both created indexes.
Here we need to update the object type field object_type in table 1:
-- Update Data
UPDATE t_test_1
SET a. object_type =
(SELECT I. object_type FROM t_test_2 I WHERE I. object_id = a. object_id );
UPDATE t_test_1
SET a. object_type =
(SELECT I. object_type FROM t_test_2 I WHERE I. object_id2 = a. object_id );
The object_id of T_TEST_2 In the first SQL statement is inconsistent with the object_id in T_TEST_1, while the data types of the two fields in the second SQL statement are consistent.
Let's take a look at the execution plan:
SQL> EXPLAIN PLAN
2 UPDATE t_test_1
3 SET a. object_type =
4 (SELECT I. object_type FROM t_test_2 I WHERE I. object_id = a. object_id );
Explained
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY );
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2933162137
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------------------------------------------------
| 0 | update statement | 64296 | 1506K | 137 (3) | 00:00:02 |
| 1 | UPDATE | T_TEST_1 |
| 2 | table access full | T_TEST_1 | 64296 | 1506K | 137 (3) | 00:00:02 |
| * 3 | table access full | T_TEST_2 | 603 | 37989 | 150 (3) | 00:00:02 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter (TO_NUMBER ("I". "OBJECT_ID") =: B1)
Note
-----
-Dynamic sampling used for this statement
19 rows selected