[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.