SQL does not take indexes due to inconsistent data types

A few days ago, my colleague sent an SQL statement because the SQL statements caused by inconsistent data types did not go through the index. It was said that the SQL statement was executed slowly during the update operation. I checked that the data volume was not very 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: [SQL] -- 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 * FRO M dba_objects; COMMIT; UPDATE t_test_1 a SET. 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 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. *,. object_id object_id2 FROM dba_objects a; COMMIT; SELECT * FROM t_test_1; create index partition ON t_test_2 (object_id) TABLESPACE partition; create index partition ON t_test_2 (object_id2) TABLESPACE TBS_LUBINSU_DATA; T _ The object_id and object_id2 fields in Table TEST_2 have both created indexes. here we need to UPDATE the object type field object_type: [SQL] -- UPDATE data UPDATE t_test_1 a SET. object_type = (SELECT I. object_type FROM t_test_2 I WHERE I. object_id =. object_id); UPDATE t_test_1 a SET. object_type = (SELECT I. object_type FROM t_test_2 I WHERE I. object_id2 =. object_id); The object_id of T_TEST_2 In the first SQL statement is inconsistent with that of 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] SQL> EXPLAIN PLAN FOR 2 UPDATE t_test_1 a 3 SET. object_type = 4 (SELECT I. object_type FROM t_test_2 I WHERE I. object_id =. object_id); Explained SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY); PLAN_TABLE_OUTPUT partition Plan hash value: 2933162137 ---------------------------------------------------------------- --------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | average | 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 | ----- Required Predicate Information (identified by operation id): --------------------------------------------------- 3-filter (TO_NUMBER ("I ". "OBJECT_ID") =: B1) Note ------dynamic sampling used for this statement 19 rows selected. Here, Oracle performs a full table scan for both tables. Next, let's look at another sentence: [SQL] [lubinsu @ localhost ~] $ Sqlplus lubinsu/lubinsu SQL * Plus: Release 10.2.0.1.0-Production on Sat May 25 12:06:14 2013 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production With the Partitioning, OLAP and Data Mining options SQL> set timing on SQL> set autotrace traceonly SQL> UPDATE t_test_1 a 2 SET. object_type = 3 (SELECT I. object_t Ype FROM t_test_2 I WHERE I. object_id2 =. object_id); 49894 rows updated. elapsed: 00:00:02. 41 Execution Plan hash value: 2786494037 Bytes ----------------- | 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 by index rowid | T_TEST_2 | 603 | 14472 | 6 (0) | 00:00:01 | * 4 | index range scan | IND_T_TEST_2_ID2 | 241 | 1 (0) | 00:00:01 | ------------------------- Certificate ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 4-access ("I ". "OBJECT_ID2" =: B1) Note ------dynamic sampling used for this statement Statistics limit 448 recursive CILS 101974 db block gets 100838 consistent gets 1 10 physical reads 23668060 redo size 668 bytes sent via SQL * Net to client 658 bytes encoded ed via SQL * Net from client 4 SQL * Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 49894 rows processed [SQL] -- the layout is not good. Sort it out: [SQL] SQL> set linesize 200 SQL>/49894 rows updated. elapsed: 00:00:03. 98 Execution Plan ---------------------------------------------------------- Plan hash value: 27864940 37 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 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 by index rowid | T_TEST_2 | 603 | 14472 | 6 (0) | 00:00:01 | * 4 | index range scan | IND_T_TEST_2_ID2 | 241 | 1 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 4-access ("I ". "OBJEC T_ID2 "=: B1) note ------dynamic sampling used for this statement Statistics limit 326 recursive cballs 101033 db block gets 100815 consistent gets 0 physical reads 12975952 redo size 676 bytes sent via SQL * Net to client 658 bytes encoded ed SQL * Net from client 4 SQL * Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 49894 rows proc Essed SQL> we can see that the index is used here. END-lubinsu.