db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4339) improve sort avoidance algorithm to include equijoin relationships
Date Wed, 05 Aug 2009 21:29:17 GMT
improve sort avoidance algorithm to include equijoin relationships
------------------------------------------------------------------

                 Key: DERBY-4339
                 URL: https://issues.apache.org/jira/browse/DERBY-4339
             Project: Derby
          Issue Type: Improvement
          Components: SQL
            Reporter: Mike Matrigali
            Priority: Minor


Not all sort avoidance paths are recognized by the optimizer.  Here is an example of the class
targeted by this issue, from 
the wisc tests :

select * from --DERBY-PROPERTIES joinOrder=FIXED
TENKTUP2, TENKTUP1
where TENKTUP1.unique1 = TENKTUP2.unique1
and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1';

There is a unique index on TENKTUP1.unique1 and TENKTUP2.unique1.  To exercise the path, the
plan
forces  TENKTUP2 as outermost join, knows that query result is sorted on TENKTUP2.unique1,
but does not
recognize that because "TENKTUP1.unique1 = TENKTUP2.unique1" that query is also sorted on
TENKTUP1.unique1 and could avoid a sort.

Note without the fixed join order a sort avoidance plan is picked by choosing the plan where
TENKTUP1.unique1 is the
outermost node.


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message