Manjula, there are some existing utility methods in
RuntimeStatisticsParser which may help you determine if your query is
using the right join order or not. If not, I wonder if a new method
can be written to provide that functionality. I just copied some
sample code about how we enforce that the expected index was used for
a given query. This is from the junit test
lang/UpdateStatisticsTest.java
JDBC.assertDrainResults(ps.executeQuery());
RuntimeStatisticsParser rtsp =
SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));
Mamta
On Wed, Oct 1, 2008 at 2:26 PM, Manjula Kutty <[EMAIL PROTECTED]> wrote:
> 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.
>
