Exceeding JOIN threshold limit during Explain within Plan Analyzer

Products

• Plan Analyzer for DB2 for z/OS

Issue/Introduction

Received the following warning during an Explain using Plan Analyzer for Db2 for z/OS (PPA):                        

THIS SQL STATEMENT IS EXPLICITLY JOINING 4 TABLES IN THE FOLLOWING FROM...
YOUR INSTALLATION HAS SET A THRESHOLD OF 2 FOR THE NUMBER OF TABLES THAT MAY BE
EXPLICITLY JOINED TOGETHER IN A SINGLE SQL STATEMENT. EXCEEDING THIS LIMIT    
WILL CAUSE POOR PERFORMANCE.

How will exceeding these thresholds cause poor performance?                                                                  

 

Resolution

Increasing the thresholds will take care of the warning but not of the overhead that multiple join can cause.
There are many factors to take in consideration when joining more tables. Depending on the size of the tables,
the result set of joining 4 small tables can take less time than joining 3 large tables.