oracle 开发误区探索《一》
来源:互联网 发布:淘宝商品上架 编辑:程序博客网 时间:2024/06/05 08:03
sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux 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
- oracle 开发误区探索《一》
- oracle 开发误区探索《二》
- 探索Oracle之LogMiner 最佳实践一
- 探索性测试:常见误区
- 基于MapWinGis开发探索(一)
- Android开发的盈利探索:一…
- PHP扩展开发探索(一)
- 服务端开发笔记一:服务器架构探索
- Apple Watch 开发探索日志 (一)
- Android开发艺术探索读书笔记(一)
- Android开发艺术探索读书笔记(一)
- Android开发艺术探索读书笔记(一)
- IOS开发探索(一):CocoalPods相关
- Android高性能开发规范探索 一
- 《Android开发艺术探索读书笔记一》
- CSR1010蓝牙开发探索(一)
- android开发艺术探索(一)
- Android Realm数据库开发探索(一)
- 深入浅出MFC(第二章)
- 三大技术(决定国家的强弱)
- IntelliJ 直接编辑国际化文件(properties)方法
- s3c2410时钟信号:FCLK、HCLK和PCLK
- 怎样使div居中显示 随窗口改变大小
- oracle 开发误区探索《一》
- Xcode - Could not launch app - No such file or directory Error.
- IP控件(功能跟电脑自带的一样,输入三位数后自动跳转,输入"."也会自动跳转)
- QtQuick3D-例子-modelview3d
- DefWndProc/WndProc/IMessageFilter的区别
- HttpServletResponse的应用
- C语言数据类型
- 如何检查内存泄露问题[转载]
- 心悸