Thanks for the reply Mamta. But the thing is I could not find the specic
join order in the runstime statistics plan. May be I don't know how ot read
it? In the given plan did you find anything about the join order??
On Wed, Oct 1, 2008 at 4:51 PM, Mamta Satoor <[EMAIL PROTECTED]> wrote:
> 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.
> >
>
--
Thanks,
Manjula.
