[20190502]查詢條件不等於測試.txt
[20190502]查詢條件不等於測試.txt
--//開發有時候寫程式碼邏輯思維不轉彎，明明該欄位僅僅2個取值'Y','N'，卻偏偏使用喜歡使用<>'N'之類的語法。
--//實際上開發寫這類程式碼時心理一定要一根弦即使使用索引執行計劃效率也不高。
--//自己也很久不做sql語句最佳化的事情,放假還是做一些例子說明問題。
1.環境：
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t as select rownum id ,lpad(rownum,10,'0') name ,decode(rownum,1,'Y','N') flag from dual connect by level<=1e5;
Table created.
SCOTT@test01p> create index i_t_flag on t(flag);
Index created.
SCOTT@test01p> select flag,count(*) from t group by flag;
F COUNT(*)
- ----------
Y 1
N 99999
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 10 ',Cascade => True ,No_Invalidate => false);
--//在flag欄位建立直方圖。
2.測試：
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select * from t where flag<>'N';
ID NAME F
---------- -------------------- -
1 0000000001 Y
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 93 (100)| | 1 |00:00:00.01 | 335 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 18 | 93 (2)| 00:00:01 | 1 |00:00:00.01 | 335 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"<>'N')
--//可以發現實際oracle估計E-Rows=1,執行計劃依舊選擇全表掃描.
--//透過提示強制使用索引看看：
SCOTT@test01p> select /*+ index(t) */ * from t where flag <> 'N';
ID NAME F
---------- -------------------- -
1 0000000001 Y
Plan hash value: 2240098092
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 185 (100)| | 1 |00:00:00.01 | 184 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 185 (1)| 00:00:01 | 1 |00:00:00.01 | 184 |
|* 2 | INDEX FULL SCAN | I_T_FLAG | 1 | 1 | | 184 (1)| 00:00:01 | 1 |00:00:00.01 | 183 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"<>'N')
--//透過提示控制,可以發現執行計劃選擇INDEX FULL SCAN,依舊是非常糟糕的執行計劃.因為索引全掃描是單塊讀,
--//導致cost成本比全表掃描高.看看是否可以透過索引快速全掃描.
SCOTT@test01p> select /*+ index_FFS(t) */ * from t where flag <> 'N';
ID NAME F
---------- -------------------- -
1 0000000001 Y
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 93 (100)| | 1 |00:00:00.01 | 335 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 18 | 93 (2)| 00:00:01 | 1 |00:00:00.01 | 335 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"<>'N')
--//不行!!
3.各種重寫看看：
--//select * from t where flag='Y';
--//select * from t where flag in ('Y');
Plan hash value: 1509392667
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG"='Y')
--//很好選擇索引範圍掃描.
--//select * from t where flag < 'N' or flag > 'N';
Plan hash value: 3426571612
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 5 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 2 | 54 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$9162BF3C / VW_ORE_1B35BA0F@SEL$1B35BA0F
2 - SET$9162BF3C
3 - SET$9162BF3C_1 / T@SEL$1
4 - SET$9162BF3C_1 / T@SEL$1
5 - SET$9162BF3C_2 / T@SEL$1
6 - SET$9162BF3C_2 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FLAG"<'N')
6 - access("FLAG">'N')
filter(LNNVL("FLAG"<'N'))
--//select * from t where flag NOT BETWEEN 'N' and 'N';
Plan hash value: 3426571612
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 5 |
| 1 | VIEW | VW_ORE_1B35BA0F | 1 | 2 | 54 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |
| 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 18 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | I_T_FLAG | 1 | 1 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$9162BF3C / VW_ORE_1B35BA0F@SEL$1B35BA0F
2 - SET$9162BF3C
3 - SET$9162BF3C_1 / T@SEL$1
4 - SET$9162BF3C_1 / T@SEL$1
5 - SET$9162BF3C_2 / T@SEL$1
6 - SET$9162BF3C_2 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("FLAG"<'N')
6 - access("FLAG">'N')
filter(LNNVL("FLAG"<'N'))
--//同樣寫成 select * from t where flag not in ( 'N');也不行.
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 93 (100)| | 1 |00:00:00.01 | 335 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 18 | 93 (2)| 00:00:01 | 1 |00:00:00.01 | 335 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG"<>'N')
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2643197/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mongodb條件查詢不等於MongoDB
- 基於Solr的HBase多條件查詢測試Solr
- MongoDB查詢條件MongoDB
- MongoDB條件查詢MongoDB
- mysql條件查詢MySql
- RANK函式基於條件的查詢函式
- Laravel 多條件查詢Laravel
- SQL多條件查詢SQL
- 條件查詢JSPJS
- 等於NULL的查詢條件導致查詢結果不正確Null
- golang beego orm 查詢條件 or andGolangORM
- 【mybatis-plus】條件查詢MyBatis
- 多條件查詢---ssh版本
- sql 查詢條件問題SQL
- 寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)
- mysql拆分字串做條件查詢MySql字串
- AntDesignBlazor示例——列表查詢條件Blazor
- 查詢作為條件的SQLSQL
- 菜品條件分頁查詢
- 關於sqlserver字元型別查詢條件區分大小寫SQLServer字元型別
- Linq查詢之多個排序條件排序
- Linq兩個from查詢條件
- 複合條件查詢的重構
- oracle date資料的條件查詢Oracle
- 【索引】反向索引--條件 範圍查詢索引
- SQL SERVER 條件語句的查詢SQLServer
- SpringBoot Jpa多條件查詢Spring Boot
- mysql多條件過濾查詢之mysq高階查詢MySql
- [20180625]10g下查詢條件rownum = 0.txt
- 關於 hibernate 邏輯刪除 預設查詢過濾條件問題（java set 條件）Java
- 查詢條件和條數，先查詢兩條免費的，後面為vip
- 深入理解mongodb查詢條件語句MongoDB
- mybatis-plus QueryWrapper條件查詢器MyBatisAPP
- gorm 使用map實現in 條件查詢用法GoORM
- 讓NoSQL支援簡單條件查詢VRSQLVR
- 【索引】反向索引--條件 範圍查詢（二）索引
- JUnit5的條件測試、巢狀測試、重複測試巢狀
- javaee導測（ssh框架）分頁 mysql資料庫（帶條件查詢）Java框架MySql資料庫