Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com

  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  397 随笔 :: 33 文章 :: 29 评论 :: 0 Trackbacks
Update时不走索引的问题
 
    今天工作上遇到了一个问题,一个好几万数据的大表update数据时不走索引,时间长到无法忍受。建一个环境模拟一下:
 
create table t1_a(a int,b varchar2(100),c varchar2(100));
create table t1_b(a varchar2(2),bb varchar2(100),cc varchar2(100));
 
create index t1_a_idx on t1_a(a);
create index t1_b_idx on t1_b(a);
 
insert into t1_a values(1,'b1','c1');
insert into t1_a values(2,'b2','c2');
insert into t1_a values(3,'b3','c3');
insert into t1_a values(4,'b4','c4');
insert into t1_a values(5,'b5','c5');
insert into t1_b values('1','bb1','cc1');
insert into t1_b values('2','bb2','cc2');
insert into t1_b values('3','bb3','cc3');
insert into t1_b values('4','bb4','cc4');
insert into t1_b values('5','bb5','cc5');
 
commit;
 
 
    用来update的SQL是这样的:
 
update t1_a set (b,c) = (select bb,cc from t1_b where a=t1_a.a);
 
Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=585)
   1    0   UPDATE OF 'T1_A'
   2    1     TABLE ACCESS (FULL) OF 'T1_A' (TABLE) (Cost=3 Card=5 Bytes=585)
   3    1     TABLE ACCESS (FULL) OF 'T1_B' (TABLE) (Cost=3 Card=1 Bytes=107)

 
    上网看了一下,发现有人遇到跟我一样的问题,连原因也一样,具体网址: http://space.itpub.net/16179598/viewspace-539595
 
    在t1_a表的a字段外加上to_char函数后,使用索引:
 
update t1_a set (b,c) = (select bb,cc from t1_b where a=to_char(t1_a.a));
 
Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=5 Bytes=585)
   1    0   UPDATE OF 'T1_A'
   2    1     TABLE ACCESS (FULL) OF 'T1_A' (TABLE) (Cost=3 Card=5 Bytes=585)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1_B' (TABLE) (Cost=2 Card=1 Bytes=107)
   4    3       INDEX (RANGE SCAN) OF 'T1_B_IDX' (INDEX) (Cost=1 Card=1)
 
    这样速度就快很多了,主要原因是oracle自动进行类型转换之后就不再走索引了。
 
 
drop table t1_a;
drop table t1_b;
set autotrace off; 
 
 
 
因为实际中是由于varchar2与nvarchar2类型不匹配造成,所以转一篇NVARCHAR2类型介绍的帖子
***************************************************************************************************************
 
输入NVARCHRA2类型字符串
===========================================================

今天看到论坛的一个帖子,是关于NVARCHAR2字符串的。解答之后,顺便问了问同事,发现居然大家都不知道这个语法。所以这里简单描述一下。


其实语法非常检查,要指定一个国家字符集的字符串NCHARNVARCHAR2,只需要在字符串前面加上一个N就可以了。

举个例子:

SQL> CREATE TABLE T_NVARCHAR2 (ID NUMBER, NAME NVARCHAR2(30));

表已创建。

SQL> INSERT INTO T_NVARCHAR2 VALUES (1, N'ABC');

已创建 1 行。

SQL> DROP TABLE T_NVARCHAR2 PURGE;

表已删除。

SQL> CREATE TABLE T_NVARCHAR2 (ID NUMBER, NAME NVARCHAR2(30));

表已创建。

SQL> INSERT INTO T_NVARCHAR2 VALUES (1, N'ABC');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT DUMP(NAME, 16) FROM T_NVARCHAR2;

DUMP(NAME,16)
---------------------------------------------------------------------------------------
Typ=1 Len=6: 0,41,0,42,0,43

SQL> SELECT DUMP('ABC', 16) VAR, DUMP(N'ABC', 16) NVAR FROM DUAL;

VAR NVAR
-------------------------------------------- ----------------------------------------
Typ=96 Len=3: 41,42,43 Typ=96 Len=6: 0,41,0,42,0,43

如果对于VARCHAR2类型的表指定NVARCHAR2类型的查询,Oracle会自动进行隐式类型转换。

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已创建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM ALL_OBJECTS;

已创建54020行。

SQL> COMMIT;

提交完成。

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T WHERE NAME = 'T';

ID NAME
---------- ------------------------------
53170 T

执行计划
----------------------------------------------------------
Plan hash value: 1889074194

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_NAME | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("NAME"='T')

Note
-----
- dynamic sampling used for this statement

SQL> SELECT * FROM T WHERE NAME = N'T';

ID NAME
---------- ------------------------------
53170 T

执行计划
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 52 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 30 | 52 (6)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SYS_OP_C2C("NAME")=U'T')

Note
-----
- dynamic sampling used for this statement

这个隐式转换过程会将列字段的VARCHAR2类型转换为NVARCHAR2类型,导致索引无法使用。如果想要这种情况下仍然可以使用索引,需要建立一个函数索引:

SQL> CREATE INDEX IND_T_NNAME ON T(TO_NCHAR(NAME));

索引已创建。

SQL> SELECT * FROM T WHERE NAME = N'T';

ID NAME
---------- ------------------------------
53170 T

执行计划
----------------------------------------------------------
Plan hash value: 462587453

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 567 | 60669 | 48 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 567 | 60669 | 48 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_NNAME | 227 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(SYS_OP_C2C("NAME")=U'T')

Note
-----
- dynamic sampling used for this statement

yangtingkun 发表于:2009.01.07 22:15 ::分类: ( ORACLE ) ::阅读:(344次) :: 评论 (1)
***************************************************************************************************************
 
 
posted on 2009-01-26 18:16 decode360 阅读(581) 评论(0)  编辑  收藏 所属分类: 05.SQL

只有注册用户登录后才能发表评论。


网站导航: