CODEWITHSUNDEEP
javadatabaseperformancenosqlamazon-dynamodb
Prasad K
Prasad K

Reputation: 153

MAX operations in Amazon DynamoDB

I am new to using NoSQL databases. I have a table in DynamoDB with more than 100k items in it. Also, this table gets refreshed frequently. On this table, I want to be able to do something similar to this in the relation database world:

Select * 
from tableName 
where attributeName = (Select MAX(attributeName) from tableName);

Is there any inexpensive way of doing this without having to fetch all the rows into my Java code? Any inputs/pointers would be appreciated. Thanks.

Upvotes: 4

Views: 3787

Answers (1)

Cory Kendall
Cory Kendall

Reputation: 7324

There is no cheap way to do this without adding space or complexity.

The expensive way would be to scan the entire table, retrieving only the key attributes and the attributeName attribute, compute the max, and then get all of the (complete) items that you found with that max.

If you have a compound key (hash and range) and few hash keys relative total items in the table, Local secondary indexes would help, and cost only a little little space. You could have an index on the attributeName attribute, and then query against each hash key using that index, and the "isScanIndexForward:false" and "limit:1" to get the "max" from that hashKey. Then you compute the max of all results (1 result from each hashkey), and you know the value to retrieve. You can retrieve them all in much the same way, with backwards scans and an 'EQ' condition on the attributeName.

If you are willing to add complexity, you can store this information in an additional table. Something like tableName.extraInfo, which has an item like this: {hashKey: "maxOfAttributeName", "value":5}. Then when you put or update the main table you update this field as well, and voila, you have your value. Beware the transactional complexity that comes with this approach if your data is changing rapidly.

Upvotes: 2

Related Questions