Why does the execution plan hav a nested loop join when an UDF is used?

[smauldin]

Why does the execution plan have a nested loop join for a simple select
with an UDF in the where clause?

Here is the query:
select * from test_plan where vCol = my_udf('test')
Here is the table definition:
create table test_plan(iCol integer, vCol varchar(20))
Here is the UDF:
CREATE FUNCTION MY_UDF (inputData VARCHAR(32000))
RETURNS VARCHAR(32000)
F1: BEGIN ATOMIC
RETURN
CASE
WHEN inputData IS NULL
THEN NULL
ELSE
substr(inputData,1,2)
END;
END
/
GRANT EXECUTE ON FUNCTION MY_UDF (VARCHAR(32000)) TO PUBLIC
/
I ran RUNSTATS
Then I ran explain plan
db2expln -d test -u steve pwd -t -q "select * from test_plan where
vCol = my_udf('test')"
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"INGRIAN"
SQL Statement:

select *
from test_plan
where vCol =my_udf('test')
Section Code Page = 1252

Estimated Cost = 25.018660
Estimated Cardinality = 0.333333

Table Constructor
| 1-Row(s)
Residual Predicate(s)
| #Predicates = 1
| Conditional Evaluation
| | Condition #1:
| | | #Predicates = 1
| | Condition #2:
| | | #Predicates = 1
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
| | Condition #3:
| | | #Predicates = 1
| | Condition #4:
| | | #Predicates = 2
| | | Table Constructor
| | | | 1-Row(s)
| | | ANY/ALL Subquery
| | | | Access Table Constructor
Nested Loop Join
| Access Table Name = INGRIAN.TEST_PLAN ID = 2,82
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
| | | Return Data to Application
| | | | #Columns = 2
Return Data Completion

End of section

-Stephen

[Serge Rielau]

sm******@ingrian.com wrote:

Why does the execution plan have a nested loop join for a simple select
with an UDF in the where clause?

The join partner is the UDF's logic.
If you remove the BEGIN ATOMIC ... END and ONLY use the RETURN statement
teh optimzier will suck the RETURN right in and the plan will look
pretty as can be (I hope).

CREATE FUNCTION MY_UDF (inputData VARCHAR(32000))
RETURNS VARCHAR(32000) CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN
CASE
WHEN inputData IS NULL
THEN NULL
ELSE
substr(inputData,1,2)
END

Cheers
Serge

PS: You know that susbtr() will return null on null input, right? The
case expression is fluff.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[smauldin]

Thanks that work.