I have a very large table which has a primary index (x, y). I am trying to perform a search on multiple areas; e.g. area 1 is located between x=1, y=1 and x=100, y=100. My aim is to retrieve the first available row for each searching area as fast as possible.
What I have done so far is using a multi-statement SELECT request for each searching area; each query is against a singe x and y value which performs a single AMP step without fully scanning all rows. I didn't want to use a SELECT statement which has something like "where x between 1 and 100 and y between 1 and 100;" as this will perform a scan on all rows.
1. How do I know how many SQL commands I can send per multi-statement request?
2. Is there a faster way to perform this task?
Thanks a lot.
For your existing DDL:
Instead of MultiStatement with single values better use "where x in (1,2,3,4,5) and y in (1,2,3,4,5)".
The optimizer will create all possible combinations and use PI-access, but you have to test what's the maximum number of values/combinations before it switches to a Full Table Scan. E.g. if you can use 1..10 it's down to 100 queries vs. 10000 for single value.
Another (probably faster way) would be partitioning the table, e.g.:
partition by range_n (x between 1 and 100000 each 1000) , range_n(y between 1 and 100000 each 1000))