Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <2045344040.1249511474915.JavaMail.jira@brutus>
Date: Wed, 5 Aug 2009 15:31:14 -0700 (PDT)
From: "Mike Matrigali (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-4339) improve sort avoidance algorithm to
 include equijoin relationships
In-Reply-To: <1392658460.1249507757280.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-4339?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mike Matrigali updated DERBY-4339:
----------------------------------


Here is another example of a query that could do sort avoidance with added code properly taking
into acount equijoin relationships:

select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
order by TENKTUP1.unique1 = TENKTUP2.unique1

The optimizer could pick either TENKTUP1.unique1 or TENKTUP2.unique1 as the outermost
node and then because it knows that TENKTUP1.unique1 = TENKTUP2.unique1 it would know
that the query also satisfies order by TENKTUP1.unique1 = TENKTUP2.unique1 without doing
an additional sort.  


> 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.