CREATE SCHEMA EMPLOYEE;
set schema EMPLOYEE;
CREATE TABLE EMP_TABLE (empid INT);
INSERT into EMP_TABLE (empid) values (100);
INSERT into EMP_TABLE (empid) values (200);
INSERT into EMP_TABLE (empid) values (300);CREATE FUNCTION EMP_FUNC() RETURNS TABLE(empid INT)
AS BEGIN
RETURN SELECT * FROM EMP_TABLE WITH HINT(IGNORE_PLAN_CACHE);
END;explain plan set statement_name = 'EMP_FUNC_PLAN' for SELECT * FROM EMP_FUNC() WHERE empid > 100;
select statement_name, operator_name, operator_details, operator_properties, schema_name, table_name from explain_plan_table where statement_name ='EMP_FUNC_PLAN';
CREATE FUNCTION EMP_FUNC_no_hint() RETURNS TABLE(empid INT)
AS BEGIN
RETURN SELECT * FROM EMP_TABLE;
END;explain plan set statement_name = 'EMP_FUNC_No_hint_PLAN' for SELECT * FROM EMP_FUNC_no_hint() WHERE empid > 100;
select statement_name, operator_name, operator_details, operator_properties, schema_name, table_name from explain_plan_table where statement_name ='EMP_FUNC_No_hint_PLAN';
| Reasons | Details |
| NOT UNFOLDED BECAUSE FUNCTION BODY CANNOT BE SIMPLIFIED TO A SINGLE STATEMENT | Multiple statements in TUDF body cannot be simplified into a single statement. |
| DUE TO ANY TABLE | TUDF uses ANY TABLE type. |
| DUE TO BINARY TYPE PARAMETER | TUDF has a binary type as its parameter. |
| DUE TO DEV_NO_SQLSCRIPT_SCENARIO HINT | The caller of TUDF disables unfolding with the DEV_NO_PREPARE_SQLSCRIPT_SCENARIO hint. |
| DUE TO DEBUGGING SESSION | TUDF is executed in debugging session. |
| DUE TO ENCRYPTED PROCEDURE OR FUNCTION | TUDF is an encrypted function. |
| NOT UNFOLDED DUE TO IMPERATIVE LOGICS | TUDF has an imperative logic, including SQLScript IF, FOR,WHILE, or LOOP statements. |
| DUE TO INTERNAL SQLSCRIPT OPERATOR | TUDF unfolding is blocked by an internal SQLScript operator. |
| DUE TO INPUT PARAMETER TYPE MISMATCH | The type of the input argument does not match the defined type of the TUDF input parameter. |
| DUE TO JSON OR SYSTEM FUNCTION | TUDF uses JSON or system function. |
| DUE TO NATIVE SQLSCRIPT OPERATOR | TUDF has a SQLScript native operator, which does not have an appropriate SQL counterpart. |
| DUE TO NO CALCULATION VIEW UNFOLDING | The caller of TUDF disables Calculation View unfolding. |
| DUE TO PRIMARY KEY CHECK | TUDF has a primary key check. |
| DUE TO RANGE RESTRICTION | Table with RANGE RESTRICTION is used within the TUDF. |
| DUE TO RECURSION | The TUDF has a recursive call. |
| DUE TO SEQUENCE OBJECT | A SEQUENCE variable is used within the TUDF. |
| DUE TO SEQUENTIAL EXECUTION | TUDF is executed with SEQUENTIAL EXECUTION clause. |
| DUE TO SPATIAL TYPE PARAMETER | TUDF has a spatial type as its parameter. |
| DUE TO TIME TRAVEL OPTION | TUDF uses a history table OR the time travel option is used. |
| DUE TO WITH CLAUSE | TUDF uses a WITH clause. |
| DUE TO WITH HINT | TUDF uses a WITH HINT clause that cannot be unfolded. |
| DUE TO WITH PARAMETERS CLAUSE | TUDF uses a WITH PARAMETERS clause. |
| DUE TO XML CLAUSE | TUDF has an XML clause. |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 4246 | |
| 3357 | |
| 2603 | |
| 2153 | |
| 1983 | |
| 1255 | |
| 1164 | |
| 1122 | |
| 1100 |