Hi
I'm trying to convert subquery.sql to junit and found this comment on the
original test :
-- DERBY-1007: Optimizer for subqueries can return incorrect cost estimates
-- leading to sub-optimal join orders for the outer query. Before the patch
-- for that isssue, the following query plan will show T3 first and then
T1--
-- but that's determined by the optimizer to be the "bad" join order. After
-- the fix, the join order will show T1 first, then T3, which is correct
-- (based on the optimizer's estimates).
And I ran the values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
And the plan returned was
Statement Name:
null
Statement Text:
select x1.j, x2.b from (select distinct i,j from t1) x1, (select
distinct a,b from t3) x2 where x1.i = x2.a order by x1.j, x2.b
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Sort ResultSet:
Number of opens = 1
Rows input = 4
Rows returned = 4
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=4
Number of rows output=4
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 130.00
optimizer estimated cost: 330.98
Source result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 4
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 130.00
optimizer estimated cost: 330.98
Source result set:
Hash Join ResultSet:
Number of opens = 1
Rows seen from the left = 5
Rows seen from the right = 4
Rows filtered = 0
Rows returned = 4
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 130.00
optimizer estimated cost: 330.98
Left result set:
Distinct Scan ResultSet for T1 at read committed isolation level
using instantaneous share row locking:
Number of opens = 1
Hash table size = 5
Distinct columns are column numbers (0,1)
Rows seen = 5
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 10.00
optimizer estimated cost: 35.34
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=5
Number of rows visited=5
Scan type=heap
start position:
null stop position:
null scan qualifiers:
None
next qualifiers:
None
optimizer estimated row count: 10.00
optimizer estimated cost: 35.34
Right result set:
Hash Table ResultSet (4):
Number of opens = 5
Hash table size = 9
Hash key is column number 0
Rows seen = 9
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 13.00
optimizer estimated cost: 295.64
next time in milliseconds/row = 0
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Source result set:
Distinct Scan ResultSet for T3 at read committed isolation
level using instantaneous share row locking:
Number of opens = 1
Hash table size = 9
Distinct columns are column numbers (0,1)
Rows seen = 9
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 13.00
optimizer estimated cost: 295.64
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=9
Number of rows visited=9
Scan type=heap
start position:
null stop position:
null scan qualifiers:
None
next qualifiers:
None
optimizer estimated row count: 13.00
optimizer estimated cost: 295.64
My question is how do I find the join order from this plan? How should I do
an assert statement with this plan??
Thanks in advance for your help
Manjula
--
Thanks,
Manjula.
