Join query in block join mode

******************* Query ****************************

Query:<SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno>

********************* Parsed Query **********************

SELECT ( -1.*),

FROM emp (-1), dept (-1),

WHERE ( ( -1.deptno) = ( -1.deptno) )

********** 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 *************

********** FKEY conditions used for deciding join order *************

( 0.deptno) = ( 1.deptno)

********** END *************

********** Execution Plan for JOIN *************

SELECT ( 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.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, ), 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) )

************************************

SELECT

FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, ),

************************************

SELECT ( 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 ( 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.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, ), 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) )

************************************

SELECT

FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, ),

************************************

SELECT ( 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:deptno, XoType=4, Nullable=0, Precision = 10, Scale=0

Result Column:1 – Name:dname, XoType=12, Nullable=1, Precision = 32, Scale=0

Result Column:2 – Name:LOC, XoType=12, Nullable=1, Precision = 32, Scale=0

Result Column:3 – Name:divno, XoType=4, Nullable=0, Precision = 10, Scale=0

Result Column:4 – Name:empno, XoType=4, Nullable=0, Precision = 10, Scale=0

Result Column:5 – Name:ename, XoType=12, Nullable=1, Precision = 32, Scale=0

Result Column:6 – Name:job, XoType=12, Nullable=1, Precision = 32, Scale=0

Result Column:7 – Name:hiredate, XoType=11, Nullable=1, Precision = 19, Scale=0

Result Column:8 – Name:sal, XoType=8, Nullable=1, Precision = 15, Scale=0

Result Column:9 – Name:comm, XoType=7, Nullable=1, Precision = 7, Scale=0

Result Column:10 – Name:deptno, XoType=4, Nullable=1, Precision = 10, Scale=0

********************* Block Join **********************

Split search expression for block joins:

Split Join Expression:

Split Table Expression:

********************************************************

********** Execution Plan for SIMPLE STMT *************

SELECT

FROM SCHEMA.OAUSER.dept (1)( 1.deptno, 1.dname, 1.LOC, 1.divno, ::1.deptno, ),

-- Sub Query has no WHERE expression --

********************* Block Join **********************

Split search expression for block joins:

Split Join Expression:

( ( 0.deptno) = ( 1.deptno) )

Split Table Expression:

********************************************************

********** Execution Plan for SIMPLE STMT *************

SELECT ( 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 ******************

********** BlockJoin processing is Enabled.

********** IP is being called to execute SubQuery on Table:<dept> with Search Column:<NULL>. hstmt:<0x6d7900>

********** IP is being called to execute SubQuery on Table:<emp> with Search Column:<deptno>. hstmt:<0x6dbe78>