All Forums Database
abin 11 posts Joined 04/11
09 Jan 2014
Improving perfomance in view

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
From A
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.
 
Regards,
Abin.
 
 
 

Raja_KT 1246 posts Joined 07/09
09 Jan 2014

Just my thought: How about a join index?
Also a  a row-key based merge join A, B. You can check the explain.
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

abin 11 posts Joined 04/11
09 Jan 2014

Hi Raja,
 
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.
Regards,
Abin.

You must sign in to leave a comment.