[20190502]查詢條件不等於測試.txt

lfree發表於2019-05-03

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章