Oracle 技術探討3

12、SQL魔術師---分析函式

     分析函式是ORACLE區別於其他資料庫的特性之一，有效的利用分析函式，可以極大的提高資料庫開發的效率。開發版的NEWKID，OO，掃把等等兄弟，可以說是玩到出神入化歎為觀止，感到欽佩不已！
    如果是介紹分析函式的語法，篇幅可能會太長，而且很多人也可以通過閱讀官方文件等相關資料的方式瞭解到。因此我重點是說說分析函式在工作中的運用，以工作中的具體案例為題材，舉例說明如下：

12.1 語法

FUNCTION_NAME（<引數>，…）
　　OVER
　　（表示式，…>
        表示式
        子句>）
           
         依次簡要說明：
             FUNCTION_NAME:                     具體函式，可見12.2
             OVER  ：                                   分析函式關鍵字，ORACLE靠此來識別！
             PARTITION子句：                       用於定義分組欄位
             ORDER BY子句：                        用於定義排序欄位
             WINDOWING子句：                （分為range和row）：預設時相當於RANGE UNBOUNDED PRECEDING
                                                            值域窗（RANGE WINDOW）  如：RANGE N PRECEDING
                                                            僅對數值或日期型別有效，選定窗為排序後當前行之前，某列（即排序列）值大於/小於（當 前 行該列值 –/+ N）的所有行，因此與ORDER BY子句有關係。
                                                            行窗（ROW WINDOW）如：ROWS N PRECEDING
                                                            選定窗為當前行及之前N行。還可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING，表示每行對應的資料視窗是之前m行與之後n行內。


12.2 最常用的分析函式

分析函式非常多，整理出最經常使用的三類，其他如 first_value()、COVAR方差系列以及其他函式等等，就算了，記多了會暈：）


row_number() over(partition by ... order by ...)                 ---------------（類似含rank(),dense_rank()）
簡要說明：分組後的序列排名（差別可比喻，3人比賽，出現2個並列第一時： row_number()為1，2，3  rank() 為1，1，3 ,dense_rank 為 1，1，2）
是不是容易搞混了，我說說自己是怎麼想象去區分的：
row_number單詞意思是行，那當然是數序不中斷號了。
rank和dense_rank是區分並列情況，那就這麼理解，dense_rank多出一個dense ，這個dense求求情，就皆大歡喜，兩個冠軍一個亞軍，rank少了dense，沒人求情，就只好出現兩個冠軍，一個季軍了，呵呵，隨意亂說這些放放輕鬆）

count() over(partition by ... order by ...)                ----------------（類似含max()，min(),sum(),avg() ）
簡要說明：這系列聚集函式本身無需多說

lag() over(partition by ... order by ...)                ----------------（類似含lead()）
簡要說明：Lag和Lead函式可以在一次查詢中取出同一欄位的前N行的資料和後N行的值




12.3 工作中的實戰應用舉例
其實分析函式為什麼神奇，我總結來就兩個字 "構造"！
經常難題就在用利用分析函式構造出的各類臨時別名列做文章，從而快速解決問題！
（回想到以前讀中學的時候學數學幾何也經常用到構造的思路。）

12.3.1廣西結算專案某需求（找出連續相等的號碼）
模擬環境類似如下：
create table ljb_serial (id1 int,id2 int ,id3 int);
insert into ljb_serial (id1 ,id2,id3) values (1,45,89);
insert into ljb_serial (id1 ,id2,id3) values (2,45,89);
insert into ljb_serial (id1 ,id2,id3) values (3,45,89);
insert into ljb_serial (id1 ,id2,id3) values (8,45,89);
insert into ljb_serial (id1 ,id2,id3) values (12,45,89);
insert into ljb_serial (id1 ,id2,id3) values (36,45,89);
insert into ljb_serial (id1 ,id2,id3) values (22,45,89);
insert into ljb_serial (id1 ,id2,id3) values (23,45,89);
insert into ljb_serial (id1 ,id2,id3) values (89,45,89);
insert into ljb_serial (id1 ,id2,id3) values (92,45,89);
insert into ljb_serial (id1 ,id2,id3) values (91,45,89);
insert into ljb_serial (id1 ,id2,id3) values (90,45,89);
commit;

12.3.1.1：將連續資料查詢出來，要達到如下效果
                                                                       
      ID1               ID2                                     ID3
--------- ----------------- ---------------------------------------
        1                45                                      89
        2                45                                      89
        3                45                                      89
       22                45                                      89
       23                45                                      89
       89                45                                      89
       90                45                                      89
       91                45                                      89
       92                45                                      89

實現上述結果的分析函式解決方法：
select id1,id2,id3
      from (select t.*,
                      lag(id1) over(order by id1) av,      ---構造出偽列av
                      lead(id1) over(order by id1) ev      ---構造出偽列ev
                  from ljb_serial t) a
         where id1 + 1 = ev                                --連續遞增的情況
            or id1 - 1 = av;                               --連續遞減的情況

12.3.1.2： 要求查出連續資料，並且要寫出最小值和最大值及連續的個數，效果如下

這個結果如果要用過程等普通方法來實現，起碼程式碼要寫300行以上，而分析函式僅僅如下一小段！

  ID1         MV         CT                  
----- ---------- ----------                  
   1          3           3                  
   22         23          2                  
   89         92          4   

實現上述結果的分析函式解決方法：
  select id1, mv, ct                                                        
        from (select id1,                                                   
                     id2,                                                   
                     id3,                                                   
                     count(*) over(partition by rm) ct,                     
                     rm,                                                     
                     max(id1) over(partition by rm) mv,                     
                     row_number() over(partition by rm order by id1) rn   ---構造rn，目的是where rn=1來取出rm區的最大序列（rm也是構造的偽列）
                from (select id1, id2, id3, id1 - rownum rm from ljb_serial)) ---rownum也是一個經常被利用的ORACLE自帶偽列，前面系列實驗也有說明過。
      where ct > 1                                                           
         and rn = 1;      


如果要舉例子，工作中用到的例子應該是數不勝數，我在省內結算專案中的階梯演算法中更是頻繁使用到這個技巧，考慮到如下三點原因，就不再
一一舉例了：

1、主要是為了讓大家知道有分析函式這好東東，要懂的來用。
2、希望大家能有“構造”的念想。（我認為這個很重要，體現思想性的地方！）
3、本例中將我說的最常用的三類函式都包囊進去，較為經典，都看明白了，就算掌握了我說的最常用的三類函式的用法了。


12.4 最易錯的ORDER BY

分析函式的order by 的存在將新增一個預設的開窗子句，這意味著計算中所使用的行的集合是當前分割槽中當前行和前面所有的行，在沒有order by 時候，預設的視窗是全部分割槽。開窗函式windwoing子句看上去好複雜，其實也並不難，range between unbounded precending and current  row 就是order by 預設的視窗。

  select ename ,sal,avg(sal) over(order by ename) from scott.emp;
  select ename ,sal,avg(sal) over() from scott.emp;

  這兩個結果不一樣,大家可以實驗一下，揣摩一二！（另：emp表在scott使用者下預設是存在的。）


總結：
   本帖我將工作中的用分析函式做了簡單的介紹，並簡要介紹了哪些是常用的分析函式。
   然後將工作中的經典案例發出，最後還說明了分析函式的易錯處。
   希望本貼能起到拋磚引玉作用，讓不用或者少用分析函式的人能重視並經常性使用以提高工作效率，希望
   大家寫SQL的時候能有構造的念想，這種思想挺好的！

 

13、和諧社會---排序
       應該說，在ORACLE優化中，排序是一個很重要的主題，相信很多人有這樣的體驗：比如某某排序應用，由於使用者PGA設定不夠大或者說sort_area_size區不足，出現了大量排序在臨時表空間中進行，速度越來越慢，甚至出現臨時表空間不夠的錯誤！
    因此我們瞭解哪些操作造成排序是必要的，只有知道了這些知識，才可能有效的應對，比如是否應用的排序是無必要的，比如是否需要為這個排序動作增加排序區大小，比如是否可以用索引來替代ORDER BY 排序動作，等等。。。。。
   以下是文件記載的常見排序動作及處理的方法：
1、需要進行排序的操作：
    A、建立索引及重建索引；
    B、涉及到索引維護的並行插入
    C、order by或者group by
    D、Distinct
    E、union /intersect/minus
    F、sort-merge join
    G、analyze命令（僅可能使用estamate而不是compute）
2、診斷及措施
    Select * from v$sysstat where name like ‘%sort%’;
    Sort(disk):要求Io去臨時表空間的排序數目
    Sort(memory)：完全在memory中完成的排序數目
    Sort(rows)：被排序的行數合計
    Sort（disk）/ Sort（memory）<5%,如果超過5％，增加sort_area_size的值。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME=’sorts (memory)’ AND disk.NAME=’sorts (disk)’;
   上面的這個文件記載很有參考價值，大家有興趣可以自己實驗驗證，方法完全可以參考我下面使用的實驗方法。這裡我重點講兩個，首先是並行與排序，這個在文件中一時沒看到，現場出了問題後才摸索到並行和排序的關係。其次是說說怎樣利用索引來避免排序動作，這個簡單但是有較強實用性。



  13.1並行與排序
涉及到並行操作會產生排序，排序是SQL語句中要特別注意的地方，並行會產生排序這點可以做實驗證明如下：
SQL> select statistic#,name from v$statname where name like '%sort%';
STATISTIC# NAME
---------- ----------------------------------------------------------------
       341 sorts (memory)
       342 sorts (disk)
       343 sorts (rows)
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#       VALUE
---------- --------------------------
       341         605
       342          0
       343        65410
SQL> drop table ljb_test;
Table dropped
這裡順道可以看出drop表也能產生排序，從605到617
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- ---------------------------
       341         617
       342          0
       343        65410
SQL> create table ljb_test as select * from dba_objects;
Table created
以下也順道看出create table 不會產生排序
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- -----------------------------
       341         617
       342          0
       343        65410
繼續實驗看看用並行度建表是什麼情況
SQL> drop table ljb_test;
Table dropped
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- --------------------------
       341        630             ---再次確認了drop 真會產生排序
       342          0
       343      65410   
SQL> create table ljb_test parallel 4 as select * from dba_objects;
Table created
可以看出，剛才用普通模式建表沒有產生排序，現在用並行模式，記憶體排序由630增至639
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#       VALUE
---------- ------------------------
       341         639
       342          0
       343       128118
接著繼續實驗如下
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- -------------------------
       341         672
       342          0
       343       190825
SQL> insert into ljb_test select * from ljb_test;
62684 rows inserted
以下可以看出普通插入操作排序由672增加到673，增加值為1
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- ---------------------------
       341        673
       342         0
       343      190825
繼續觀察並行插操作的記憶體使用情況
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- ---------------------------
       341         685
       342          0
       343        190825
SQL> insert into ljb_test select /*+parallel(a,4)*/ * from ljb_test a;
62685 rows inserted
發現記憶體排序從685增加到687，增加值為2
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#       VALUE
---------- ----------------------------
       341         687
       342          0
       343        190848
接著實驗讓DML並行生效的情況
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- -----------------------------
       341        699
       342         0
       343      190848
SQL> alter session enable parallel dml;
Session altered
SQL> insert /*+parallel(b,4)*/ into ljb_test b select /*+parallel(a,4)*/ * from ljb_test a;
62686 rows inserted
發現記憶體排序由699增加到702，增加到3，又多增加一個排序！
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- -------------------------
       341        702
       342         0
       343       190894
如果並行插入的表是帶索引的，排序會是什麼情況呢？
以下因為當時資料庫重啟過，所以值並不連貫。
SQL> create index idx_object_id on ljb_test(object_id);
Index created
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- ----------------------------------
       341        339
       342         0
       343       63914
SQL>  insert into ljb_test select /*+parallel(a,4)*/ * from ljb_test a;
62720 rows inserted
SQL> select statistic#,value from v$mystat where statistic# in (341,342,343);
STATISTIC#      VALUE
---------- -------------------------------
       341        346
       342         0
       343       63948
發現記憶體排序由339增加到346，增加到7，這下排序數量大增了！
並行語句無論是DDL還是DML還是查詢並行，三個方式都會用到排序,這種情況避免排序的方法很簡單，就是去掉並行度，
這個案例在自己專案組中遇到過，開始還納悶了好久。



13.2 非空索引可以避免排序

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb


SQL> create TABLE ljb_test AS SELECT * FROM user_objects ;

Table created

SQL> CREATE INDEX idx_object_id ON ljb_test(object_id);

Index created

SQL> ANALYZE TABLE ljb_test COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

Table analyzed

SQL> EXPLAIN PLAN FOR SELECT object_id FROM ljb_test  ORDER BY object_id;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2007178810
---------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   305 |  1220 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |   305 |  1220 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| LJB_TEST |   305 |  1220 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected
----看來索引列要非空才行，索引是不能儲存空值的，NULL在ORACLE中的陷阱無處不在啊！
SQL> ALTER TABLE ljb_test MODIFY object_id NOT NULL;

Table altered

SQL> ANALYZE TABLE ljb_test COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

Table analyzed

SQL> EXPLAIN PLAN FOR SELECT object_id FROM ljb_test  ORDER BY object_id;

Explained
---再看看，真的是沒有SORT ORDER BY 了，COST成本當然降低了，從4到1
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4290572743
--------------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |   305 |  1220 |     1   (0)| 00:00:01
|   1 |  INDEX FULL SCAN | IDX_OBJECT_ID |   305 |  1220 |     1   (0)| 00:00:01
--------------------------------------------------------------------------------

8 rows selected

SQL>

 

14. 小議函式索引


1. 選擇性索引有可能導致查詢高效

（選擇性索引就是函式索引）


C:\Documents and Settings\fujitsu>sqlplus ljb/ljb

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 28 12:37:13 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ljb_158@RQRQ> drop table ljb_test;

表已刪除。

ljb_158@RQRQ> create table ljb_test (id int ,status varchar2(2));

表已建立。

ljb_158@RQRQ> --建立普通索引
ljb_158@RQRQ> create index id_normal on ljb_test(status);

索引已建立。

ljb_158@RQRQ> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=1000000;

已建立1000000行。

ljb_158@RQRQ> insert into ljb_test select 1 ,'N' from dual;

已建立 1 行。

ljb_158@RQRQ> commit;

提交完成。

ljb_158@RQRQ> analyze table ljb_test compute statistics for table for all indexes for all indexed c
lumns;

表已分析。

ljb_158@RQRQ>
ljb_158@RQRQ>  explain plan for  select * from ljb_test where status='N';

已解釋。

ljb_158@RQRQ> select * from table(dbms_xplan.display);

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

Plan hash value: 3604305264

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |     1 |    10 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("STATUS"='N')

已選擇14行。

ljb_158@RQRQ> --看索引情況
ljb_158@RQRQ> analyze index id_normal validate structure;

索引已分析

ljb_158@RQRQ> select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL                         23264320    1000001          3

ljb_158@RQRQ> --建函式索引
ljb_158@RQRQ> drop index id_normal;

索引已刪除。

ljb_158@RQRQ> create index id_status on  ljb_test (Case when status= 'N' then 'N' end);

索引已建立。

ljb_158@RQRQ> analyze table ljb_test compute statistics for table for all indexes for all indexed c
lumns;

表已分析。

ljb_158@RQRQ> /*以下這個select * from ljb_test where (case when status='N' then 'N' end)='N’寫法不
能變,如果是select * from ljb_test where status='N'將無效!我見過有些人設定了選擇性索引，卻這樣呼叫的
，結果根本起不到任何效果！*/

表已分析。

ljb_158@RQRQ>
ljb_158@RQRQ> explain plan for  select * from ljb_test where (case when status='N' then 'N' end)='N
;

已解釋。

ljb_158@RQRQ> select * from table(dbms_xplan.display);

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

Plan hash value: 2720798490

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |     1 |    10 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')

已選擇14行。

ljb_158@RQRQ> --接著刪除掉選擇性索引，建普通索引比較一下效果
ljb_158@RQRQ> analyze index id_status validate structure;

索引已分析

ljb_158@RQRQ> --接著是看id_status即函式索引的情況
ljb_158@RQRQ>  select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS                             8000          1          1

2.無法使用到函式索引的情況

2.1必須是在CBO模式下
具體舉例來說明
SQL> create table test as select * from zhjs_app.tg_log where rownum<=500;
Table created
SQL> create index IDX_TG_LOG_BATCH_CODE on test (SUBSTR(BATCH_CODE,6,6));
Index created
表和函式索引索引建好後，看看是否能用到索引
SQL> explain plan for select count(*) from test where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
查詢發現始終無法用到該索引
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT AGGREGATE      |             |       |       |       |
|*  2 |   TABLE ACCESS FULL  | TEST        |       |       |       |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------
   2 - filter(SUBSTR("TEST"."BATCH_CODE",6,6)='010102')
Note: rule based optimization
15 rows selected
究竟怎麼回事？這裡我要重點說明一下，函式索引要滿足兩個條件，一個是查詢要基於COST的，才能生效，第二個是query_rewrite_enabled  這個引數要是true,第二個條件到9i以後並不怎麼必要了，關鍵是第一點。
試著給這個查詢加上一個索引的HINT，看系統能走索引嗎？
SQL> explain  plan for select /*+index(a,IDX_TG_LOG_BATCH_CODE)*/ count(*) from test a where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
查詢真的走了索引
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  
|   0 | SELECT STATEMENT     |                        |     1 |    17 |     1 |
|   1 |  SORT AGGREGATE      |                        |     1 |    17 |       |
|*  2 |   INDEX RANGE SCAN   | IDX_TG_LOG_BATCH_CODE  |    12 |   204 |     1 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("A"."BATCH_CODE",6,6)='010102')
Note: cpu costing is off
15 rows selected
為什麼加上HINT後能建索引嗎，這裡涉及到了解HINT的一個常識，ORACLE的所有HINT中，除了/*+RULE*/外，加了HINT，ORACLE都會選擇基於代價的查詢，也就是COST的查詢方式，所以滿足了我剛才提的條件，所以索引就用上了。
當然如果不用HINT，可以有另外一個選擇，就是將表分析後，讓ORACLE優化器自動選擇走COST而不是走基於RULE的優化器模式，這樣，也滿足了我剛才說的條件，具體操作如下：
SQL> analyze table test compute statistics for table for all indexes for all indexed columns;
Table analyzed
SQL> explain  plan for select count(*) from test a where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    17 |     2 |
|   1 |  SORT AGGREGATE      |                        |     1 |    17 |       |
|*  2 |   INDEX RANGE SCAN   | IDX_TG_LOG_BATCH_CODE  |   418 |  7106 |     2 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("A"."BATCH_CODE",6,6)='010102')
Note: cpu costing is off
15 rows selected
果然，該查詢也走索引，達到目的！
總結：函式索引必須要在基於代價的優化器模式下才可以執行，因此想利用到函式索引，必須要對錶進行分析或者是建立HINT強制讓ORACLE走COST模式。我們結算專案就有不少地方用到函式索引，希望引起大家的注意！



2.2 函式必須是確定性的


現構造ljb_test表內容如下
  SQL> create table ljb_test(year varchar2(4));
Table created
SQL> insert into ljb_test values ('2009');
1 row inserted
SQL>  insert into ljb_test values ('2008');
1 row inserted
SQL> commit;
Commit complete
SQL> select to_date(year,'yyyy') from ljb_test;
TO_DATE(YEAR,'YYYY')
--------------------------------
2009-03-01
2008-03-01
如果要讓這個to_date(year,'yyyy')建立索引該怎麼做了，很多人可能會立即回答，建函式索引！
那實驗一下，看建函式索引會是一種什麼情況
SQL>  create index idx_ljb_test on ljb_test(to_date(year,'yyyy'));
ORA-01743: 僅能編制純函式的索引
失敗了！這個提示是否讓摸不著頭腦！
真正原因是如上查詢是在三月份查的結果，如果是四月份，查詢出的結果將會如下
SQL> select to_date(year,'yyyy') from ljb_test;
TO_DATE(YEAR,'YYYY')
----------------------------------
2009-04-01
2008-04-01
這點大家應該不難明白吧，該函式充滿了不確定性！這就是函式索引無法建立的原因，函式索引是預先在索引中儲存了函式索引的結果以備使用，現在這個函式值不確定了，怎麼儲存函式結果？
總結：通過本小節的學習，大家應該能加深了對函式索引的認識吧，建函式索引的函式必須要有確定性，返回不確定結果的函式就無法建立起來，因為不確定結果根本不能被函式索引預儲存，所以要失敗！我就曾經要建立一個這樣不確定函式的函式索引而失敗，最後選擇了其它方式！



2.3 自定義函式需DETERMINISTIC關鍵字

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL>
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test as select * from user_objects ;
Table created
SQL> create or replace function f_minus1(i int)
  2  return int
  3  is
  4  begin
  5  return(i-1);
  6  end;
  7  /
Function created
建完函式後我們試著建立函式索引，發現建立失敗
SQL> create index idx_ljb_test on ljb_test (f_minus1(object_id));

create index idx_ljb_test on ljb_test (f_minus1(object_id))
ORA-30553: 函式不能確定
將函式加上DETERMINISTIC關鍵字重建
SQL> create or replace function f_minus1(i int)
  2  return int DETERMINISTIC
  3  is
  4  begin
  5  return(i-1);
  6  end;
  7  /
Function created
現在發現加上DETERMINISTIC關鍵字後的自定義函式可以建立函式索引成功了！
SQL> create index idx_ljb_test on ljb_test (f_minus1(object_id));
Index created
SQL> analyze table ljb_test compute statistics for table for all indexes;
Table analyzed
SQL> explain plan for select * from ljb_test where f_minus1(object_id)=23;
Explained
驗證得出該函式索引可以在語句中被正常的使用到。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3905012767
--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     3 |   273 |     2   (0)
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST     |     3 |   273 |     2   (0)
|*  2 |   INDEX RANGE SCAN          | IDX_LJB_TEST |     1 |       |     1   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("LJB"."F_MINUS1"("OBJECT_ID")=23)
14 rows selected
   總結：大家在工作中用到自定義函式的時候也很多，因為自定義函式可以直接在SQL中呼叫，簡化程式碼，給編寫資料庫應用帶來了很大的方便，但是與此同時我們要考慮SQL中用到的自定義函式是否能用的上索引，我們開發人員除了關注實現外也要同時考慮到效能，因此我們在建自定義函式的時候儘量考慮加上DETERMINISTIC的關鍵字，以方便將來用上函式索引。

    引申聯想：寫完該小節，大家明白了使用自定義函式一個很關鍵的一點，就是如果要用到自定義函式的函式索引，必須要有DETERMINISTIC的關鍵字，不過這個DETERMINISTIC到底是什麼東西啊，為什麼自定義函式的函式索引能否建成功完全依賴這個關鍵字，這麼神奇！原來對於指定了DETERMINISTIC的函式，在一次呼叫中，對於相同的輸入，只進行一次呼叫。這要求函式的建立者來保證DETERMINISTIC的正確性，如果這個函式的返回值和輸入引數沒有確定性關係，會導致函式結果異常的。這裡我舉個例子，大家體會一下
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
建立一個帶DETERMINISTIC關鍵字的函式，功能就是返回值為1
SQL>  CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER DETERMINISTIC
  2      AS
  3      BEGIN
  4      DBMS_LOCK.SLEEP(0.1);
  5      RETURN 1;
  6      END;
  7  /
Function created
SQL> set timing on
發現執行時間非常快，0.765秒完成
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
              1
              1
           這裡略去另外73個記錄（值都為1）
75 rows selected
Executed in 0.765 seconds
用不帶關鍵字DETERMINISTIC的方式建立函式，功能也是返回值為1
SQL>  CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER
  2    AS
  3    BEGIN
  4    DBMS_LOCK.SLEEP(0.1);
  5   RETURN 1;
  6    END;
  7  /
Function created
Executed in 0.047 seconds
發現執行速度慢了好多，8.469秒
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
              1
              1
這裡也略去另外73個記錄（值都為1）
75 rows selected
Executed in 8.469 seconds
其實速度相差如此之大的原因是什麼呢？那是因為帶DETERMINISTIC的函式對於相同的輸入只會運算一次，認為值是一樣的，就把上次算出來的結果直接引用了，所以為什麼第一種會執行速度這麼快，因為第一種情況下根本該自定義函式就只呼叫了1次，然後另外那74次結果都是直接考慮到輸入相同，直接把第1次呼叫的結果拿來用而已。而第二種其實是呼叫了75次。我這裡故意舉了無輸入值函式方式來做實驗因為這樣比較特殊，沒有輸入就是表示輸入相同值的含義！（有輸入的情況我也實驗過了，一樣！）為什麼ORACLE要對自定義函式做這個DETERMINISTIC限制呢？我思考了一下，應該是有這兩個原因吧：1、可避免我們建立一個不確定輸出值的函式！比如我上一小節的函式索引建立失敗（不過那不是建立自定義函式，是ORACLE自帶函式失敗），就和相同輸入返回不同結果有關係。2、SQL中寫函式函式很容易出現效能問題，比如你寫個不好的函式，又被大量呼叫，那估計執行起來麻煩要大了，ORACLE這樣做了，可以讓寫自定義函式的人在呼叫的時候一次執行多次使用結果，速度也可以快很多。這兩點是我猜測，有機會找ORACLE公司的人確認一下。

 

15、小心外連線陷阱


大家使用外連線的場合還是比較多的，下面就談談外連線的陷阱，希望引起開發人員的注意

1、外連線中，T1.ID(+)=T2.ID 的時候，要注意T1的WHERE謂詞條件寫法

C:\Documents and Settings\fujitsu>sqlplus ljb/ljb

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 1月 27 19:41:55 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

ljb_137@RQRQ> DROP TABLE ljb_test1;

表已刪除。

ljb_137@RQRQ> DROP TABLE ljb_test2;

表已刪除。

ljb_137@RQRQ> CREATE TABLE ljb_test1 (ID INT ,fee VARCHAR2(20),cycle_id VARCHAR2(20));

表已建立。

ljb_137@RQRQ> CREATE TABLE ljb_test2 (ID INT ,NAME VARCHAR2(20));

表已建立。

ljb_137@RQRQ> INSERT INTO ljb_test1 VALUES (111,47877,201001);

已建立 1 行。

ljb_137@RQRQ> INSERT INTO ljb_test1 VALUES (999,997650,200912);

已建立 1 行。

ljb_137@RQRQ> INSERT INTO ljb_test2 VALUES (111,'小明');

已建立 1 行。

ljb_137@RQRQ> INSERT INTO ljb_test2 VALUES (112,'小張');

已建立 1 行。

ljb_137@RQRQ> INSERT INTO ljb_test2 VALUES (108,'小王');

已建立 1 行。

ljb_137@RQRQ> INSERT INTO ljb_test2 VALUES (999,'小李');

已建立 1 行。

ljb_137@RQRQ> COMMIT;

提交完成。

/*
需求是如下
ljb_test2是配置表，記錄編號和編號姓名。
ljb_test1是清單表，記錄各個編號的收入等其他資訊，現要求兩表關聯，查詢所有ljb_test2配置
表中的記錄的金額，如果清單表中無記錄，則金額顯示為0
看完這個需求大家應該非常清楚是一個外關聯的寫法，不過雖然看上去簡單，但是要是不注意細節，就會出現錯誤。
*/

--比如如下寫法，發現112，108等其他記錄沒有展現，至少也應該是展現出來，值為0

ljb_137@RQRQ> SELECT t2.NAME,nvl(t1.fee,0)AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id=201001;

NAME                 收入
-------------------- --------------------
小明                 47877

--這裡要注意，外連線寫法中，如果兩表關聯的條件，T1(+)條件的時候，T1表的條件必須要帶上(+)
，如t1.cycle_id(+)=xxx,如果是t1.cycle_id=xxx則出問題了！

ljb_137@RQRQ> SELECT t2.NAME,nvl(t1.fee,0) AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id(+)=201001;

NAME                 收入
-------------------- --------------------
小明                 47877
小李                 0
小張                 0
小王                 0


---當然，如果把該條件用WITH子句寫進結果集中，也可以避免此類錯誤
ljb_137@RQRQ> WITH t1 AS (SELECT * FROM ljb_test1 WHERE ljb_test1.cycle_id=201001)
  2  SELECT t2.NAME, nvl(t1.fee,0)AS 收入  FROM t1, ljb_test2 t2
  3  WHERE t1.ID(+)=t2.ID ;

NAME                 收入
-------------------- --------------------
小明                 47877
小李                 0
小張                 0
小王                 0

--與之類似的是使用標量子查詢，同樣可以避免此類錯誤

ljb_137@RQRQ> SELECT T2.NAME, NVL(T1.FEE, 0) AS 收入
  2    FROM LJB_TEST2 T2,
  3         (SELECT * FROM LJB_TEST1 WHERE LJB_TEST1.CYCLE_ID = 201001) T1
  4   WHERE T1.ID(+) = T2.ID;

NAME                 收入
-------------------- --------------------
小明                 47877
小李                 0
小張                 0
小王                 0

另外特別特別注意一點就是在（+）條件寫法不允許寫IN或者OR,具體如下

ORA-01719: OR 或 IN 運算元中不允許外部聯接運算子 (+)

[email=ljb_158@RQRQ]ljb_158@RQRQ[/email]> SELECT t2.NAME,nvl(t1.fee,0) AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id(+) IN(201001,200908);
AND t1.cycle_id(+) IN(201001,200908)
                   *
第 3 行出現錯誤:
ORA-01719: OR 或 IN 運算元中不允許外部聯接運算子 (+)


[email=ljb_158@RQRQ]ljb_158@RQRQ[/email]> SELECT t2.NAME,nvl(t1.fee,0) AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id(+) =201001 OR cycle_id(+)=200908;
AND t1.cycle_id(+) =201001 OR cycle_id(+)=200908
                                         *
第 3 行出現錯誤:

那遇到這樣情況咋辦呢？很簡單，標量子查詢和WITH子句都能搞定！！！

[email=ljb_158@RQRQ]ljb_158@RQRQ[/email]> WITH t1 AS (SELECT * FROM ljb_test1 WHERE ljb_test1.cycle_id IN (201001,200908))
  2  SELECT t2.NAME, nvl(t1.fee,0)AS 收入  FROM t1, ljb_test2 t2
  3  WHERE t1.ID(+)=t2.ID ;
NAME                 收入
-------------------- --------------------
小明                 47877
小李                 0
小張                 0
小王                 0



2、外連線寫法限制了表連線順序，限制了效能的優化

ljb_137@RQRQ> set autotrace traceonly explain

--如下寫法發現順序根本無法根據自己來控制
ljb_137@RQRQ> SELECT /*+ordered*/ t2.NAME,nvl(t1.fee,0) AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id(+)=201001;

執行計劃
----------------------------------------------------------
Plan hash value: 921076258
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     4 |   248 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |           |     4 |   248 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| LJB_TEST2 |     4 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LJB_TEST1 |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

ljb_137@RQRQ> SELECT /*+ordered*/ t2.NAME,nvl(t1.fee,0) AS 收入 FROM ljb_test2 t2,LJB_TEST1 t1
  2  WHERE t1.ID(+)=t2.ID
  3  AND t1.cycle_id(+)=201001;

執行計劃
----------------------------------------------------------
Plan hash value: 921076258
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     4 |   248 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |           |     4 |   248 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| LJB_TEST2 |     4 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LJB_TEST1 |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

---在沒有外連線情況下，完全可以控制表連線的順序
ljb_137@RQRQ> SELECT /*+ordered*/ t2.NAME,nvl(t1.fee,0) AS 收入 FROM LJB_TEST1 t1 ,ljb_test2 t2
  2  WHERE t1.ID=t2.ID
  3  AND t1.cycle_id=201001;

執行計劃
----------------------------------------------------------
Plan hash value: 3355060888
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    62 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |           |     1 |    62 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| LJB_TEST1 |     1 |    37 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| LJB_TEST2 |     4 |   100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

ljb_137@RQRQ> SELECT /*+ordered*/ t2.NAME,nvl(t1.fee,0) AS 收入 FROM ljb_test2 t2,LJB_TEST1 t1
  2  WHERE t1.ID=t2.ID
  3  AND t1.cycle_id=201001;

執行計劃
----------------------------------------------------------
Plan hash value: 1009957034
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    62 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |           |     1 |    62 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| LJB_TEST2 |     4 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LJB_TEST1 |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-665569/，如需轉載，請註明出處，否則將追究法律責任。