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
精彩评论