开发者

How to select sum of values in a given range from huge tables quickly (Derby)

I got A and B table like:

Table A

a   b   mount
a0  b0  0.0001
a0  b1 开发者_开发问答 0.0002

Table B

c   d   weight
c0  d0  0.99998
c0  d1  0.99996

Each table has 10,000 - 100000 records.

I want to get all combination that mount+weight >= 0.9998 and mount+weight <= 0.9999, for example:

a   b   c   d    sum
a0  b0  c0  d0   0.9999
a0  b1  c0  d1   0.9998 

But if takes a lot of time when i try these ways:

Method 1

SELECT a b c d mount+weight
FROM A,B 
WHERE mount+weight >= 0.9998 and mount+weight <= 0.9999

A table have index of mount, B table have index of weight

Method 2

Create A+B table, but it takes more time than method 1.

Is there any ways to improve?


try

SELECT a, b, c, d, mount+weight as mw
FROM A,B 
WHERE mount+weight between 0.9998 and 0.9999

This may produce a slightly different execution plan

Edit: I just realised this is for Derby. Not even sure if BETWEEN is available in Derby

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜