By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
Thanks that work.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.