数据类型不一致导致的SQL不走索引
前几天,同事发来一条SQL,说是更新操作的时候执行的很慢,我看了下,数据量也不是很大。再查看执行计划,发现是执行路径错误导致的,可是为什么会走错误的执行路径呢?统计信息并没有太大的问题。在这里模拟下:
数据准备:
--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.数据准备,表二: DROP TABLE t_test_2; create table T_TEST_2 ( owner VARCHAR2(30), object_name VARCHAR2(128), subobject_name VARCHAR2(30), --这里数据类型和T_TEST_1中object_id的数据类型不一致 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), --这里数据类型和T_TEST_1中object_id的数据类型一致 object_id2 NUMBER ); INSERT INTO T_TEST_2 SELECT a.*, a.object_id object_id2 FROM dba_objects a; 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;
T_TEST_2表中的object_id和object_id2两个字段都创建了索引
在这里需要更新表1的对象类型字段object_type:
--更新数据 UPDATE t_test_1 a SET a.object_type = (SELECT i.object_type FROM t_test_2 i WHERE i.object_id = a.object_id); UPDATE t_test_1 a SET a.object_type = (SELECT i.object_type FROM t_test_2 i WHERE i.object_id2 = a.object_id);
第一条SQL中T_TEST_2的object_id和T_TEST_1中的object_id数据类型是不一致的,而第二条中两个字段数据类型是一致的。
我们来看下执行计划:
SQL> EXPLAIN PLAN FOR 2 UPDATE t_test_1 a 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
我们可以看到,在这里Oracle对两个表都执行了全表扫描。
下面再看另外一句:
[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, 2005, 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 a.object_type = 3 (SELECT i.object_type FROM t_test_2 i WHERE i.object_id2 = a.object_id); 49894 rows updated. Elapsed: 00:00:02.41 Execution Plan ---------------------------------------------------------- Plan hash value: 2786494037 -------------------------------------------------------------------------------- ----------------- | 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 | -------------------------------------------------------------------------------- ----------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("I"."OBJECT_ID2"=:B1) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 448 recursive calls 101974 db block gets 100838 consistent gets 110 physical reads 23668060 redo size 668 bytes sent via SQL*Net to client 658 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 49894 rows processed
--排版不好,整理下:
SQL> set linesize 200 SQL> / 49894 rows updated. Elapsed: 00:00:03.98 Execution Plan ---------------------------------------------------------- Plan hash value: 2786494037 ------------------------------------------------------------------------------------------------- | 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 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("I"."OBJECT_ID2"=:B1) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 326 recursive calls 101033 db block gets 100815 consistent gets 0 physical reads 12975952 redo size 676 bytes sent via SQL*Net to client 658 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 11 sorts (memory) 0 sorts (disk) 49894 rows processed SQL>
可见这里,是走了索引的。END-lubinsu.