SQL statements that are not indexed due to inconsistent data types

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.