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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9932141/viewspace-665569/
1146
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
