db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeffrey Lichtman <swa...@rcn.com>
Subject Re: A few questions about index usage
Date Fri, 27 Jan 2006 21:49:42 GMT

>...or as a cross product:
>
>SELECT MIN(A.Id), MAX(B.Id) FROM Customer A, Customer B
>
>This might be fairly efficient, given that each branch of the cross 
>product only returns a single row. I guess this could be a possible 
>internal translation as well.
>
>Roy

In this case Derby would do the cross product before calculating the 
MIN and MAX values, so the performance would be worse than the original query.

To do this as two separate scans, Derby would have to be changed to 
know that MIN and MAX results are independent of the number of values 
fed to them. For example, the optimization you're proposing would get 
the wrong answer for a query like this:

SELECT SUM(A.balance), SUM(B.balance) FROM Customers A, Suppliers B

Although it could get the right answer for this:

SELECT SUM(DISTINCT A.balance), SUM(DISTINCT B.balance) FROM 
Customers A, Suppliers B

Figuring out all the cases where a Cartesian product could be split 
into two separate scans could be tricky.


                        -        Jeff Lichtman
                                 swazoo@rcn.com
                                 Check out Swazoo Koolak's Web Jukebox at
                                 http://swazoo.com/ 


Mime
View raw message