[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
- mysql條件查詢MySql
- MongoDB查詢條件MongoDB
- [20180625]10g下查詢條件rownum = 0.txt
- Laravel 多條件查詢Laravel
- SpringBoot Jpa多條件查詢Spring Boot
- AntDesignBlazor示例——列表查詢條件Blazor
- golang beego orm 查詢條件 or andGolangORM
- Javaweb-DQL-條件查詢JavaWeb
- 查詢條件封裝物件封裝物件
- 【mybatis-plus】條件查詢MyBatis
- [20201224]order by欄位順序與查詢條件為NULL.txtNull
- 寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)
- 20240719資料庫關聯查詢、條件查詢資料庫
- 34. 過濾條件、多表查詢、子查詢
- Linq查詢之多個排序條件排序
- Linq兩個from查詢條件
- 菜品條件分頁查詢
- hyperf關聯模型條件查詢模型
- mysql拆分字串做條件查詢MySql字串
- SQL-基礎語法 - 條件查詢 - 模糊查詢SQL
- 關於sqlserver字元型別查詢條件區分大小寫SQLServer字元型別
- mysql多條件過濾查詢之mysq高階查詢MySql
- MybatisPlus入門(五)MybatisPlus條件查詢MyBatis
- Vue請求介面查詢條件拼接Vue
- mybatis-plus QueryWrapper條件查詢器MyBatisAPP
- mysql like查詢 - 根據多個條件的模糊匹配查詢MySql
- 查詢條件和條數，先查詢兩條免費的，後面為vip
- 深入理解mongodb查詢條件語句MongoDB
- ORACLE 查詢條件出現關鍵字：&Oracle
- 讓NoSQL支援簡單條件查詢VRSQLVR
- gorm 使用map實現in 條件查詢用法GoORM
- jsp+servlet+mysql多條件模糊查詢JSServletMySql
- Oralce用不等於空串判斷查詢不出任何資料
- SpringBoot學習筆記13——MybatisPlus條件查詢Spring Boot筆記MyBatis
- mysql，where條件查詢等學習筆記MySql筆記
- JN專案-時間查詢條件驗證
- mybatis lambdaQuery 查詢條件導致空指標MyBatis指標