oracle 开发误区探索《一》

环境：

sys@ORCL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

sys@ORCL> !uname -a
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

① 单列和复合列NOT IN 子查询

not in (........)里面的null，如果存在null，则返回的绝对是空值。因为，in本是或的关系，加上not，则任何值和null，逻辑与，其结果都是空。

测试：

--Q1;单列not in子查询有null分析

hr@ORCL> drop table test1;
hr@ORCL> drop table test2;
hr@ORCL> create table test1 (id number);

Table created.

hr@ORCL> create table test2 (id number);

Table created.

hr@ORCL> insert into test1 values(1);

1 row created.

hr@ORCL> insert into test1 values(2);

1 row created.

hr@ORCL> insert into test2 values(null);

1 row created.

hr@ORCL> insert into test2 values(1);

1 row created.

hr@ORCL> commit;

Commit complete.

--需求：选出在test1、却不在test2的记录
--单列、常见错误如下、没有结果：

hr@ORCL> select id from test1 where id not in (select id from test2);

no rows selected

--正确的写法、常见的还是not exists

hr@ORCL> select id from test1 where not exists (select 1 from test2 where test1.id=test2.id);

ID
----------
2

--Q2：复合列not in子查询有null分析

hr@ORCL> create table t1 (a number,b number);

Table created.

hr@ORCL> create table t2 (a number,b number);

Table created.

hr@ORCL> insert into t1 values(1,1);

1 row created.

hr@ORCL> insert into t1 values(1,2);

1 row created.

hr@ORCL> insert into t2 values(1,1);

1 row created.

hr@ORCL> insert into t2 values(null,2);

1 row created.

hr@ORCL> commit;

Commit complete.

--需求：选出在t1、却不在t2的记录
--常见错误、和Q1一样、没有结果

hr@ORCL> select * from t1 where (a,b) not in (select * from t2);

no rows selected


--正确解法、常见的是not exists
hr@ORCL> select * from t1 where not exists (select 1 from t2 where t1.a=t2.a and t1.b=t2.b);

A B
---------- ----------
1 2
--分析
因为是复合列，相当于列的组合条件是or，根据NULL的比较和逻辑运算规则，OR条件有一个为TRUE则返回TRUE，全为FALSE则结果为FALSE，其他为UNKNOWN，
比如(1,2) not in (null,2)则相当于1 <> null or 2 <> 2,那么明显返回的结果是UNKNOWN，所以不可能为真，不返回结果；
但是(1,2) not in (null,3)相当于1 <> null or 2 <> 3,因为2<>3的已经是TRUE,所以条件为TRUE，返回结果；
再做个简单测试：
hr@ORCL> select * from dual;

D
-
X
hr@ORCL> select * from dual where (1,1) not in ((null,2));

D
-
X
hr@ORCL> select * from dual where (1,1) not in ((null,1));

no rows selected

② 消除隐式转换

在所有的auto trace分析中，都带有Predicate information的关键字。Predicate information信息有两种取值：filter和access，一般索引读和hash join，体现为access。
关注Predicate information最重要的一点在于，查看是否发生了数据类型转换。
数据类型转换，既产生了开销，也影响索引的正常使用
所以，任何时刻都不该在此处看到有数据类型的转化
--测试
hr@ORCL> drop table t;

Table dropped.

hr@ORCL> create table t(col1 varchar2(20),col2 number);

Table created.

hr@ORCL> insert into t select rownum,rownum+1 from dual connect by level <=10000;

10000 rows created.

hr@ORCL> commit;

Commit complete.

hr@ORCL> create index idx_t on t(col1);

Index created.

hr@ORCL> set autot traceonly
hr@ORCL> select * from t where col1=2 and col2=3;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

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

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

1 - filter("COL2"=3 AND TO_NUMBER("COL1")=2)

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


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--通过statistics，可知：获取1行产生48个逻辑读，不符合获取单行记录小于5个逻辑读的标准
--在Predicate information信息里，我们发现此处存在TO_NUMBER("COL1")=2的类型转换
--查看t表的col1字段类型为varchar2
hr@ORCL> select * from t where col1='2' and col2=3;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

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

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

1 - filter("COL2"=3)
2 - access("COL1"='2')

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


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
463 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

--执行计划由全表扫描变更为索引读
--在Predicate information，access("COL1"='2')表示用到了索引方式的访问路径
--返回行与逻辑读的比率也<5