I have a table A which contains millions of records, this tables is versioned based on two numeric fields Open_From and Open_To. These fields will tell me when record was open from and untill when it was valid. The numbers corresponds to a date valuesaved in table B.
Table B has two fields
Number and Date
Now I have to craete a view which will give me all records which were open at a certain point.
To do this I have to join my main table A with the table B with contion.
Sel A.*, B.Date
Inner Join B
On A.Open_From <= B.Number
And A.Open_To > = B.Number
Users can select records based on the Date field from table B. The problem I am facing is that as the number of records in table B increase the join produces more results. When table B has one record I get same number of records as in table A, with two records in table B, I get 2 * number of records in table A. This causes a perfomance issue for my view.
Are there smarter ways to improve perfomance? I tought creating PPI on table A so that only wanted records will be selected from table for join. But the PPI has to be of two level first on Open_From and then on Open_To, with EACH 350. But this means I have a PPI where all records from a year is stored in one partition, which is not helping much.
The usage of view is not extensive, so overhead of creating index is not justified.
Merge join works with equality joins. I have a non equal join condition.