数据类型不一致导致的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.