[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')