db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: Question regarding runtimestatistics and join order
Date Wed, 01 Oct 2008 23:51:37 GMT
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.
>

Mime
View raw message