skip to main content
Designing and coding the IP : Query execution analysis : Join query
 

Join query

In this example, the SQL Engine Plan includes details of the order in which each individual table is queried and how the join query is split into query on the individual tables. The following output shows the call to IP EXECUTE for each of the tables.
******************* Query ****************************
Query:<SELECT * FROM emp, dept, division WHERE emp.deptno = dept.deptno AND dept.divno = division.divno>
********************* Parsed Query **********************
SELECT ( -1.*),
FROM emp (-1), dept (-1), division (-1),
 
WHERE ( ( ( -1.deptno) = ( -1.deptno) )AND( ( -1.divno) = ( -1.divno) ) )
 
********** TABLE conditions used for deciding join order *************
Table:SCHEMA.OAUSER.emp (0)
Search Exp:
Search Cond:
********** END *************
********** TABLE conditions used for deciding join order *************
Table:SCHEMA.OAUSER.dept (1)
Search Exp:
Search Cond:
********** END *************
********** TABLE conditions used for deciding join order *************
Table:SCHEMA.OAUSER.division (2)
Search Exp:
Search Cond:
********** END *************
********** FKEY conditions used for deciding join order *************
( 0.deptno) = ( 1.deptno) AND ( 1.divno) = ( 2.divno)
********** END *************
********** Execution Plan for JOIN *************
SELECT ( 2.divno), ( 2.division), ( 1.deptno), ( 1.dname), ( 1.LOC), ( 1.divno), ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.division (2)( 2.divno, 2.division, ::2.divno, ), SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, 1.divno, ), SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.deptno, ),
 
WHERE ( ( ( 0.deptno) = ( 1.deptno) )AND( ( 1.divno) = ( 2.divno) ) )
 
************************************
SELECT
FROM SCHEMA.OAUSER.division (2)( 2.divno, 2.division, ::2.divno, ),
 
************************************
SELECT
FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, 1.divno, ),
 
WHERE ( ( 1.divno) = ( 2.divno) )
************************************
SELECT ( 2.divno), ( 2.division), ( 1.deptno), ( 1.dname), ( 1.LOC), ( 1.divno), ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.deptno, ),
 
WHERE ( ( 0.deptno) = ( 1.deptno) )
************************************
Join Expression:
<Empty>
**************************************************
********************* Validated Query **********************
SELECT ( 2.divno), ( 2.division), ( 1.deptno), ( 1.dname), ( 1.LOC), ( 1.divno), ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.division (2)( 2.divno, 2.division, ::2.divno, ), SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, 1.divno, ), SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.deptno, ),
 
WHERE ( ( ( 0.deptno) = ( 1.deptno) )AND( ( 1.divno) = ( 2.divno) ) )
 
************************************
SELECT
FROM SCHEMA.OAUSER.division (2)( 2.divno, 2.division, ::2.divno, ),
************************************
SELECT
FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, 1.divno, ),
 
WHERE ( ( 1.divno) = ( 2.divno) )
************************************
SELECT ( 2.divno), ( 2.division), ( 1.deptno), ( 1.dname), ( 1.LOC), ( 1.divno), ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.deptno, ),
 
WHERE ( ( 0.deptno) = ( 1.deptno) )
******************* End Validated Query ********************
 
********************* Query Result Specification **********************
Result Column:0 – Name:divno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:1 – Name:division, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:2 – Name:deptno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:3 – Name:dname, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:4 – Name:LOC, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:5 – Name:divno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:6 – Name:empno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:7 – Name:ename, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:8 – Name:job, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:9 – Name:hiredate, XoType=11, Nullable=1, Precision = 19, Scale=0
Result Column:10 – Name:sal, XoType=8, Nullable=1, Precision = 15, Scale=0
Result Column:11 – Name:comm, XoType=7, Nullable=1, Precision = 7, Scale=0
Result Column:12 – Name:deptno, XoType=4, Nullable=1, Precision = 10, Scale=0
 
********** Execution Plan for SIMPLE STMT *************
SELECT
FROM SCHEMA.OAUSER.division (2)( 2.divno, 2.division, ::2.divno, ),
 
-- Sub Query has no WHERE expression --
 
********** Execution Plan for SIMPLE STMT *************
SELECT
FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, 1.divno, ),
WHERE ( ( 1.divno) = ( 2.divno) )
************** Sub Query:1 **************
No Search List
Condition List:1
( 1.divno) = ( 2.divno)
***************** End of Plan ******************
********** Execution Plan for SIMPLE STMT *************
SELECT ( 2.divno), ( 2.division), ( 1.deptno), ( 1.dname), ( 1.LOC), ( 1.divno), ( 0.empno), ( 0.ename), ( 0.job), ( 0.hiredate), ( 0.sal), ( 0.comm), ( 0.deptno),
FROM SCHEMA.OAUSER.emp (0)( 0.empno, 0.ename, 0.job, 0.hiredate, 0.sal, 0.comm, 0.deptno, ::0.deptno, ),
WHERE ( ( 0.deptno) = ( 1.deptno) )
************** Sub Query:1 **************
Search List:
( 0.deptno) = ( 1.deptno)
 
***************** End of Plan ******************
 
********** IP is being called to execute SubQuery on Table:<division> with Search Column:<NULL>. hstmt:<0x6e0f98>
********** IP is being called to execute SubQuery on Table:<dept> with Search Column:<NULL>. hstmt:<0x6e3220>
********** IP is being called to execute SubQuery on Table:<dept> with Search Column:<NULL>. hstmt:<0x6e3220>
********** IP is being called to execute SubQuery on Table:<dept> with Search Column:<NULL>. hstmt:<0x6e3220>
********** IP is being called to execute SubQuery on Table:<emp> with Search Column:<deptno>. hstmt:<0x6e77d0>
********** IP is being called to execute SubQuery on Table:<emp> with Search Column:<deptno>. hstmt:<0x6e77d0>
********** IP is being called to execute SubQuery on Table:<emp> with Search Column:<deptno>. hstmt:<0x6e77d0>
 
********************* Query Result Specification **********************
Result Column:0 – Name:divno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:1 – Name:divisiondname, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:2 – Name:deptno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:3 – Name:dname, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:4 – Name:LOC, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:5 – Name:divno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:6 – Name:empno, XoType=4, Nullable=0, Precision = 10, Scale=0
Result Column:7 – Name:ename, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:8 – Name:job, XoType=12, Nullable=1, Precision = 32, Scale=0
Result Column:9 – Name:hiredate, XoType=11, Nullable=1, Precision = 19, Scale=0
Result Column:10 – Name:sal, XoType=8, Nullable=1, Precision = 15, Scale=0
Result Column:11 – Name:comm, XoType=7, Nullable=1, Precision = 7, Scale=0
Result Column:12 – Name:deptno, XoType=4, Nullable=1, Precision = 10, Scale=0