[20170420]表达式加0或者减0不一样.txt
--//oracle 有时候避免某个索引采用字段+0或者-0的方式，不使用索引，但是两者存在一点点区别，通过例子说明。
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select rownum id1,round(rownum/50,0) id2,lpad('x',100,'x') name from dual connect by level <=4e5;
Table created.
execute sys.dbms_stats.gather_table_stats ( OwnName => user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
2.测试：
SCOTT@book> select count(*) from t where id2=100;
COUNT(*)
----------
50
Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1782 (100)| | 1 |00:00:00.04 | 6449 | 6446 |
| 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.04 | 6449 | 6446 |
|* 2 | TABLE ACCESS FULL| T | 1 | 50 | 200 | 1782 (1)| 00:00:22 | 50 |00:00:00.04 | 6449 | 6446 |
------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> select count(*) from t where id2+0=100;
COUNT(*)
----------
50
Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1782 (100)| | 1 |00:00:00.07 | 6449 | 6446 |
| 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.07 | 6449 | 6446 |
|* 2 | TABLE ACCESS FULL| T | 1 | 50 | 200 | 1782 (1)| 00:00:22 | 50 |00:00:00.07 | 6449 | 6446 |
------------------------------------------------------------------------------------------------------------------------------
SCOTT@book> select count(*) from t where id2-0=100;
COUNT(*)
----------
50
Plan hash value: 2966233522
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1782 (100)| | 1 |00:00:00.08 | 6449 | 6446 |
| 1 | SORT AGGREGATE | | 1 | 1 | 4 | | | 1 |00:00:00.08 | 6449 | 6446 |
|* 2 | TABLE ACCESS FULL| T | 1 | 4000 | 16000 | 1782 (1)| 00:00:22 | 50 |00:00:00.08 | 6449 | 6446 |
------------------------------------------------------------------------------------------------------------------------------
3.分析我这里没有建立索引在字段ID2上，执行计划选择全表扫描，但是你如果仔细看E-Rows就可以看出几种的区别。
id2=100 E-Rows 50
id2+0=100 E-Rows 50
id2-0=100 E-Rows 4000
--//很明显oracle在分析生成执行计划上+0,-0是区别对待的，采用+0方式，oracle视乎知道执行者选择绕过索引(当然我没建立），估算E-Rows的统计信息是正确的。
--//而采用-0方式，oracle把这样的表达式当作函数对待，按照1%取E_rows ,400000*0.01=4000。
--//知道这个没什么意思，只是提醒自己要注意一些细节。
