前幾天，同僚發來一條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對兩個表都執行了全表掃描。
下面再看另外一句：
[[email protected] ~]$ 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.
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)