那我给你贴出来
[php]
关于字符和数字类型的索引,Oracle如何实现内部自动转换以及索引使用的验证测试
发表人:tomszrp | 发表时间: 2006年二月10日, 18:51
起因：今天早上有同事问我如下语句为什么执行很慢：
select * from tbcs.acc_bank_debit_info a
where a.region=534 and
a.status=1 and
a.accountid=(select accountoid from tbcs.subscriber b
where b.status='US10' and
b.region=534 and
b.servnumber=13626373466);
备注:以上2表均是按照region进行分区的分区表，在accountid上都有索引.
根据以前的经验，我一看就知道是子查询未用上索引的缘故，因为servernumber是varchar类型的，而... where b.status='US10' and b.region=534 and b.servnumber=13626373466这种写法是用不上索引的。
修改为... where b.status='US10' and b.region=534 and b.servnumber='13626373466'后，性能马上得以解决。
为了验证我对这个观点的认识，就顺手做了个实验，结果和我期望的一致。
实验环境：
windows xp2 + Oracle9i + 760M
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
验证过程如下：
SQL>create table zrp (
recoid number(14),
servnumber varchar2(9),
region number(3),
deal_date date ) tablespace study;
--编写脚本插入数据(多弄点感受一下效果)
SQL> begin
2 for i in 1..99999 loop
3 insert into zrp values(to_number('53109053'||to_char(i,'fm00000')),'8053'||to_char(i,'fm00000'),531,sysdate);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL>
SQL> begin
2 for i in 1..99999 loop
3 insert into zrp values(to_number('53208053'||to_char(i,'fm00000')),'8053'||to_char(i,'fm00000'),532,sysdate);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL>
...
SQL> select count(*) from zrp;
299997
SQL>
--分别在number类型和varchar2类型的字段上recoid和servnumber上建立索引
SQL> create index inx_zrp_oid on zrp(recoid);
索引已创建。
SQL> create index inx_zrp_serv on zrp (servnumber);
索引已创建。
SQL>
--下面开始测试验证过程,先验证number类型字段上的索引是否能对字符串进行自动转换，从而使用上索引.
select * from zrp where recoid=5310905310005
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.03 3 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 3 6 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID ZRP
1 INDEX RANGE SCAN INX_ZRP_OID (object id 30594)
********************************************************************************
select * from zrp where recoid='5310905310080'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 2 6 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID ZRP
1 INDEX RANGE SCAN INX_ZRP_OID (object id 30594)
********************************************************************************
/*
结果证明：对于number类型的字段的索引,Oracle在内部能实现对字符串和number之间的自动转换,从而继续使用索引.下面在看看varchar2类型字段上的索引是否能对数字进行自动转换,从而使用上索引呢
*/
select * from zrp where servnumber='905310001'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.02 2 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 2 6 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID ZRP
1 INDEX RANGE SCAN INX_ZRP_SERV (object id 30595)
********************************************************************************
select * from zrp where servnumber=905318899
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.35 0.45 799 1513 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.35 0.45 799 1513 0 1
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL ZRP
********************************************************************************
哈哈，看到了，太明显了。此时对于select * from zrp where servnumber=905318899 语句就用不上servnumber字段上的索引了.
分析总结：
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 上面recoid是一个数值类型的索引列.
select * from zrp where recoid='5310905310080';
实际上,经过ORACLE类型转换, 语句转化为:
select * from zrp where recoid=to_number('5310905310080');
类型转换没有发生在索引列上,索引的用途没有被改变. 现在,而servnumber是一个字符类型的索引列.
select * from zrp where servnumber=905318899
这个语句被ORACLE转换为:
select * frorm zrp to_number(servnumber)=905318899;
因为内部发生的类型转换, 这个索引将不会被用到!
为什么前者不不是发生在列上而后者是呢，闲着没事将Oracle内部对varchar/char和number类型的数据结构做了一个dump，发现引起这个内部转换可能与数据的存储类型有关系。
因为dump的过程比较罗嗦，也不容易看懂，此处略去,引用别人的总结如下：
>
最后,我又翻阅了Oracle官方文档，找到了如下的说法，证实了上面的结论：
...
The following rules govern the direction in which Oracle makes implicit datatype conversions:
1) During INSERT and UPDATE operations, Oracle converts the value to the datatype of the affected column.
2) During SELECT FROM operations, Oracle converts the data from the column to the type of the target variable.
3) When comparing a character value with a NUMBER value, Oracle converts the character data to NUMBER.
4) When comparing a character value with a DATE value, Oracle converts thecharacter data to DATE.
5) When you use a SQL function or operator with an argument of a datatype other than the one it accepts,
Oracle converts the argument to the accepted datatype.
6) When making assignments, Oracle converts the value on the right side of the equal sign (=) to the datatype of the target of the assignment on the left side.
7) During concatenation operations, Oracle converts from noncharacter datatypes to CHAR or NCHAR.
8) During arithmetic operations on and comparisons between character and noncharacter datatypes,
Oracle converts from any character datatype to a number, date, or rowid, as appropriate.
In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a number.
9) Comparisons between CHAR/VARCHAR2 and NCHAR/NVARCHAR2 types may entail different character sets.
The default direction of conversion in such cases is from the database character set to the national character set.
以上9个要点尤为重要,其他的可以不看,这9个声明一定要看.这一步骤,也再次证明了阅读Oracle官方文档的重要性.
[/php]
|
