Improving perfomance in view

Database
Enthusiast

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.

2 REPLIES
Enthusiast

Re: Improving perfomance in view

Just my thought: How about a join index?

Also a  a row-key based merge join A, B. You can check the explain.

Enthusiast

Re: Improving perfomance in view

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.