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.