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