關於在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@]