富达2540437主管[20201201]约束大写与查询.txt

20201201]约束大写与查询.txt  --//开发在谓词条件中使用大写函数，我看了表中数据全部是大写。而查询条件两者情况都出现，看看是否加入约束，取消建立函数索引  --//的需求。  1.环境：  SCOTT@book> @ 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 t1 as select rownum id,dbms_random.string('U',6) vc from dual connect by level<=1e5;  Table created.  SCOTT@book> select * from t1 where vc <> UPPER(vc);  no rows selected  --//全部是大写vc字段。  SCOTT@book> create index i_t1_vc on t1(vc);  Index created.  SCOTT@book> create index if_t1_vc on t1(upper(vc));  Index created.  alter table t1 add ( constraint ck_upper_vc check (vc = upper(vc)) enable validate);  2.测试:  SCOTT@book> select id from t1 where vc='A';  no rows selected  Plan hash value: 1263437441  ----------------------------------------------------------------------------------------  | Id  | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  ----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |         |        |       |     2 (100)|          |  |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |    12 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | I_T1_VC |      1 |       |     1   (0)| 00:00:01 |  ----------------------------------------------------------------------------------------  SCOTT@book> select id from t1 where upper(vc)='A';  no rows selected  Plan hash value: 1591134070  -----------------------------------------------------------------------------------------  | Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |          |        |       |    67 (100)|          |  |   1 |  TABLE ACCESS BY INDEX ROWID| T1       |   1000 | 12000 |    67   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IF_T1_VC |    400 |       |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------  --//可以发现分别使用各自的索引。  3.隐藏索引测试看看：  SCOTT@book> alter index if_t1_vc invisible;  Index altered.  SCOTT@book> select id from t1 where upper(vc)='A';  no rows selected  Plan hash value: 3617692013  ---------------------------------------------------------------------------  | Id  | Operation         | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  ---------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |      |        |       |    69 (100)|          |  |*  1 |  TABLE ACCESS FULL| T1   |   1000 | 12000 |    69   (2)| 00:00:01 |  ---------------------------------------------------------------------------  Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------     1 - SEL$1 / T1@SEL$1  Predicate Information (identified by operation id):  ---------------------------------------------------     1 - filter(UPPER("VC")='A')  --//很明显隐藏函数索引行不通，反过来测试看看。  SCOTT@book> alter index if_t1_vc visible;  Index altered.  SCOTT@book> alter index i_t1_vc invisible;  Index altered.  --//现在函数索引有效。  SCOTT@book> select id from t1 where vc='A';  no rows selected  SCOTT@book> @ dpc '' ''  PLAN_TABLE_OUTPUT  -------------------------------------  SQL_ID  b4twjdv2mqt2n, child number 0  -------------------------------------  select id from t1 where vc='A'  Plan hash value: 1591134070  -----------------------------------------------------------------------------------------  | Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |          |        |       |    67 (100)|          |  |*  1 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |    12 |    67   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IF_T1_VC |    400 |       |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------  Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------     1 - SEL$1 / T1@SEL$1     2 - SEL$1 / T1@SEL$1  Predicate Information (identified by operation id):  ---------------------------------------------------     1 - filter("VC"='A')     2 - access("T1"."SYS_NC00003$"='A')  --//在这样的情况下等值查询可以使用建立的函数索引。注意看Predicate Information部分，增加了filter("VC"='A')。  SCOTT@book> drop index i_t1_vc;  Index dropped.  --//再次验证，过程略。  4.总结：  --//这样可以建立一个函数索引，覆盖等值查询，不过不是我前面提到的取消函数索引建立，而是建立对应的函数索引。  --//建议开发在写代码时候好好想一下，谓词加入函数前自己写sql代码时心里要有一个弦，不然写一辈子的代码，做出来一堆垃圾,对不  --//起自己这份职业。  --//在收尾时在生产系统应用发现在11.2.0.3上无法实现。放弃，我只能建立2套索引，无语....  --//补充一点在建立函数索引后要单独分析表或者隐含列。不然E-ROWS不准.注意前面的E-Rows不准，可能在连接时可能出现不好的执行计划。  --//重新分析表后的测试：  SCOTT@book> select id from t1 where upper(vc)='A';  no rows selected  SCOTT@book> @ dpc '' ''  PLAN_TABLE_OUTPUT  -------------------------------------  SQL_ID  0gy85dby7p25q, child number 0  -------------------------------------  select id from t1 where upper(vc)='A'  Plan hash value: 1591134070  -----------------------------------------------------------------------------------------  | Id  | Operation                   | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |          |        |       |     2 (100)|          |  |   1 |  TABLE ACCESS BY INDEX ROWID| T1       |      1 |    12 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IF_T1_VC |      1 |       |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------  Query Block Name / Object Alias (identified by operation id):  -------------------------------------------------------------     1 - SEL$1 / T1@SEL$1     2 - SEL$1 / T1@SEL$1  Predicate Information (identified by operation id):  ---------------------------------------------------     2 - access("T1"."SYS_NC00003$"='A')  --//现在E-rows=1.