日期轉換函式的格式引數大小寫規則 （自yangtingkun)

首先建立一個簡單的測試環境：

SQL> CREATE TABLE T (ID NUMBER, START_TIME DATE);

Table created.

SQL> INSERT INTO T (ID, START_TIME) SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

36001 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX IND_T_TIME ON T (TO_CHAR(START_TIME, 'YYYY-MM-DD'));

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;

Session altered.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'YYYY-MM-DD') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=342 Bytes=7524)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=9 Card=342 Bytes=7524)
2 1 INDEX (RANGE SCAN) OF 'IND_T_TIME' (NON-UNIQUE) (Cost=1 Card=342)

SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'yyyy-mm-dd') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=360 Bytes=7920)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=360 Bytes=7920)

SQL> SELECT /*+ INDEX (T IND_T_TIME) */ * FROM T
2 WHERE TO_CHAR(START_TIME, 'yyyy-mm-dd') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=360 Bytes=7920)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=360 Bytes=7920)

問題產生了。由於函式索引一個常數輸入引數的不同，造成了Oracle無法使用函式索引。這時，Oracle認為索引掃描得到的結果是不正確的，因此，即使使用HINT，Oracle也不會採用索引掃描。使這個查詢語句使用索引的辦法就是在增加日期轉換格式為小寫的函式索引。

SQL> CREATE INDEX IND_T_LOWER ON T (TO_CHAR(START_TIME, 'yyyy-mm-dd'));

Index created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'yyyy-mm-dd') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=342 Bytes=7524)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=9 Card=342 Bytes=7524)
2 1 INDEX (RANGE SCAN) OF 'IND_T_LOWER' (NON-UNIQUE) (Cost=1 Card=342)

問題似乎解決了，但是新的問題出現了，如果輸入的日期格式是大小寫混寫怎麼樣。

而且lfree的測試發現了一個更有趣的現象。

SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'YYYY-MM-Dd') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=360 Bytes=7920)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=360 Bytes=7920)

SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'yyyy-mm-dD') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=342 Bytes=7524)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=9 Card=342 Bytes=7524)
2 1 INDEX (RANGE SCAN) OF 'IND_T_LOWER' (NON-UNIQUE) (Cost=1 Card=342)

仔細觀察上面兩個SQL語句，當格式為YYYY-MM-Dd時，無法使用YYYY-MM-DD格式的索引。這個很容易理解，輸入不同嗎。但是格式為yyyy-mm-dD時，卻可以使用yyyy-mm-dd的索引，這是為什麼呢？

經過一系列測試後，總結出了日期轉換函式格式引數的大小寫規則（終於引入正題了）。並發現一開始對CBO函式索引的理解還不算很清楚，對CBO的聰明程度有點低估了（當然，總體來說，還不算太聰明）。

剛開始測試時發現，即使是下面這種查詢也可以使用索引：

SQL> SELECT * FROM T WHERE TO_CHAR(START_TIME, 'yYYY-mM-dD') = '2006-01-18';

ID START_TIME
---------- -------------------
33873 2006-01-18 11:20:47
34280 2006-01-18 11:20:48
35990 2006-01-18 14:40:26
35991 2006-01-18 14:45:20

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=9 Card=342 Bytes=7524)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=9 Card=342 Bytes=7524)
2 1 INDEX (RANGE SCAN) OF 'IND_T_LOWER' (NON-UNIQUE) (Cost=1 Card=342)

似乎Oracle只關係第一個字母。但是綜合考慮YYYY-MM-Dd的情況，似乎又不是這樣。於是得到了Oracle會找到第一個小寫字母為止的推論。

在測試過程中，找到了一個更好的方法來確定Oracle使用字串轉換的格式。

SQL> SET AUTOT OFF
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM T WHERE TO_CHAR(START_TIME, 'yYYY-mM-dD') = '2006-01-18';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 342 | 7524 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 342 | 7524 | 9 |
|* 2 | INDEX RANGE SCAN | IND_T_LOWER | 342 | | 1 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(TO_CHAR("T"."START_TIME",'yyyy-mm-dd')='2006-01-18')

Note: cpu costing is off

15 rows selected.

透過看執行計劃後面的Predicate Information，可以清楚的看到Oracle使用的格式是yyyy-mm-dd而不是yYYY-mM-dD。

繼續測試：

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM T WHERE TO_CHAR(START_TIME, 'YyYY-MM-DD') = '2006-01-18';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 360 | 7920 | 10 |
|* 1 | TABLE ACCESS FULL | T | 360 | 7920 | 10 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_CHAR("T"."START_TIME",'Yyyy-MM-DD')='2006-01-18')

Note: cpu costing is off

14 rows selected.

上面這種情況也是對的，但是下面列出的情況又不符合了。

SQL> EXPLAIN PLAN FOR
2 SELECT * FROM T WHERE TO_CHAR(START_TIME, 'YYyY-MM-DD') = '2006-01-18';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 342 | 7524 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 342 | 7524 | 9 |
|* 2 | INDEX RANGE SCAN | IND_T_TIME | 342 | | 1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(TO_CHAR("T"."START_TIME",'YYYY-MM-DD')='2006-01-18')

Note: cpu costing is off

15 rows selected.

顯然，這種情況又和剛才得到的推論衝突了。似乎這種規律只對前兩位有效。

突然我明白了Oracle所定義的規則。由於英文會出現首字母大寫的情況，所以Oracle在判定的時候首先判斷第一位的大小寫狀態，如果是小寫，就不繼續判斷後面的內容了，而把整個字串都當作小寫。如果第一位是大寫，Oracle繼續判斷第二位，如果也是大寫，那麼Oracle就把整個字串都當作大寫，如果第二位是小寫，則Oracle認為這種情況是首字母大寫，就把第一個字母當作大寫，而剩餘字元做小寫出來。

如果有興趣可以對SELECT TO_CHAR(SYSDATE, 'Month') FROM DUAL;進行測試，不管給出的Month的大小寫如何變，Oracle的輸出結果只有三種，大寫、小寫和首字母大寫。

SQL> SELECT TO_CHAR(SYSDATE, 'Month') FROM DUAL;

TO_CHAR(S
---------
January

SQL> SELECT TO_CHAR(SYSDATE, 'MOnth') FROM DUAL;

TO_CHAR(S
---------
JANUARY

SQL> SELECT TO_CHAR(SYSDATE, 'MoNTH') FROM DUAL;

TO_CHAR(S
---------
January

SQL> SELECT TO_CHAR(SYSDATE, 'mONTH') FROM DUAL;

TO_CHAR(S
---------
january