关于在oracle 的group by中使用自定义函数

--DB2 下面的SQL报错
select tm_intrvl_cd,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2) ,count(distinct group_id)
from EDS.TW_CORP_BRCC_MBRBIND_DAY_200809
group by tm_intrvl_cd ,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2)
with ur
-DB2 正确的形式
select tm_intrvl_cd,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2) ,count(distinct group_id)
from EDS.TW_CORP_BRCC_MBRBIND_DAY_200809
group by tm_intrvl_cd
with ur

--ORACLE 可以正常执行
select TM_INTRVL_CD,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2) ,count(distinct VEST_LVL1_CD)
from frnt.TN_ZQ_TTT_05
group by tm_intrvl_cd ,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2)

--ORACLE中两种写法执行计划的对比
----group by中含ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2)
SQL> set autotr on
SQL> select TM_INTRVL_CD,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2) ,count(distinct VEST_LVL1_CD)
2 from frnt.TN_ZQ_TTT_05
3 group by tm_intrvl_cd ,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2);

TM_INTRVL_CD ETL.FUNC_DATEINS(TM_INTRVL_CD,1,2) COUNT(DISTINCTVEST_LVL1_CD)
------------ ---------------------------------- ---------------------------
201004 200804 8
201001 200804 8
200912 200804 8
201002 200804 8
201005 200804 8
201003 200804 8

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1557629788

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7962 | 194K| 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | P->P | HASH |
| 6 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 7962 | 194K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| TN_ZQ_TTT_05 | 7962 | 194K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
162 recursive calls
3 db block gets
240 consistent gets
0 physical reads
672 redo size
667 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
66 sorts (memory)
0 sorts (disk)
6 rows processed

----去除 group by 中 ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2)
SQL> select TM_INTRVL_CD,ETL.FUNC_DATEINS( tm_intrvl_cd, 1, 2) ,count(distinct VEST_LVL1_CD)
2 from frnt.TN_ZQ_TTT_05
3 group by tm_intrvl_cd;

TM_INTRVL_CD ETL.FUNC_DATEINS(TM_INTRVL_CD,1,2) COUNT(DISTINCTVEST_LVL1_CD)
------------ ---------------------------------- ---------------------------
200912 200804 8
201004 200804 8
201005 200804 8
201003 200804 8
201001 200804 8
201002 200804 8

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1557629788

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7962 | 194K| 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
| 3 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10001 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | P->P | HASH |
| 6 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 7962 | 194K| 3 (34)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | SORT GROUP BY | | 7962 | 194K| 3 (34)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 7962 | 194K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL| TN_ZQ_TTT_05 | 7962 | 194K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
144 recursive calls
3 db block gets
223 consistent gets
0 physical reads
672 redo size
667 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
61 sorts (memory)
0 sorts (disk)
6 rows processed

recursive sql概念：当用户执行一些SQL语句时，会自动执行一些额外的语句，我们把这些额外的SQL语句称为“recursive calls” 或者是
“recursive sql statement”,当在执行一个DDL语句时，Oracle总会隐含的发出一些Recursiv sql语句，用于修改数据字典，如果数据字典
没有在共享内存中，则就执行“resursive calls”，它会把数据字典从物理读取到共享内存。当然DML和select语句都可能引起recursive
SQL。[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23937368/viewspace-1048059/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23937368/viewspace-1048059/