db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Manjula Kutty" <manjula.ku...@gmail.com>
Subject Re: Question regarding runtimestatistics and join order
Date Thu, 02 Oct 2008 00:01:39 GMT
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 <msatoor@gmail.com> 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 <manjula.kutty@gmail.com>
> 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.

Mime
View raw message