[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。
--//知道這個沒什么意思，只是提醒自己要注意一些細節。