關於在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@]
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於Oracle自定義分組函式Oracle函式
- Oracle中自定義函式Oracle函式
- Oracle中關於函式的使用Oracle函式
- Oracle 自定義函式Oracle函式
- 轉:對字串的“sum”——在Oracle中自定義聚集函式的例子字串Oracle函式
- oracle 自定義聚合函式Oracle函式
- Oracle自定義聚集函式Oracle函式
- ORACLE 自定義函式BUG?Oracle函式
- HIVE中的自定義函式Hive函式
- 在資料庫中自定義外部函式資料庫函式
- Hive中自定義函式Hive函式
- 在python中使用sqlite的自定義函式功能PythonSQLite函式
- Oracle自定義聚集函式薦Oracle函式
- 關於oracle內建函式的使用Oracle函式
- Oracle帶引數的自定義函式Oracle函式
- 函式索引使用細節——自定義函式的索引化函式索引
- MySQL使用之五_自定義函式和自定義過程MySql函式
- 轉載:在c#中使用sqlite的3種自定義函式C#SQLite函式
- shell自定義函式函式
- perl自定義函式函式
- TDengine 3.0 中如何編譯、建立和使用自定義函式編譯函式
- Clickhouse 使用者自定義外部函式函式
- matlab自定義函式建立與使用Matlab函式
- 7.yii中使用自定義函式函式
- 自定義函式索引使用及其注意點函式索引
- Oracle優化案例-自定義函式索引(五)Oracle優化函式索引
- oracle 10g 新增:自定義聚集函式Oracle 10g函式
- 關於Oracle取整的函式Oracle函式
- GRDB自定義的純函式函式
- Hive常用函式及自定義函式Hive函式
- 影片直播系統原始碼,在Laravel中自定義模板函式 並在模板中呼叫原始碼Laravel函式
- Loadrunner 使用者自定義函式使用[轉]函式
- 關於在PostgreSQL中使用extract函式以及epochSQL函式
- oracle中關於null的定義OracleNull
- 關於qt中的tr()函式QT函式
- hive 3.0.0自定義函式Hive函式
- MySQL建立自定義函式MySql函式
- python 自定義函式Python函式