All Forums Database
Boopathi15 11 posts Joined 12/13
07 Nov 2014
Index/Partition design for Transaction Tables

I've two transaction tables which will hold millions of data. I need help in designing the index and partition for these tables.
Following is the structure.. Table 01 will hold a row per transaction ( total volume 50 million)
Table 02 is SCD type 2 table and will have around 70 rows per policy key and transaction.. All the 70 records will get inserted for 1st time then only SCD 2 will happen.(approx volume 5 billion)

 

Create MULTISET table table01

(

policy_key integer,

Transaction_dttm timestamp(6),

transaction_type varchar(10)

.

.

.)

Index (policy_Key)

;

 

Create MULTISET table table02

(

policy_key integer,

Key_Type varchar(50),

Key_Value varchar(100),

Start_dttm timestamp(6),

End_Dttm timestamp(6),

 

.)

Index (policy_Key)

;

 

The join between the tables will happen in the following way.

 

 

Select 

t1.Policy_Key

,t1.Transaction_Dttm

,MAx(case when key_Type='Location' then Key_value) as Policy_Location

,MAx(case when key_Type='Address' then Key_value) as Policy_Address

,MAx(case when key_Type='Email' then Key_value) as Policy_Email

,MAx(case when key_Type='Phone' then Key_value) as Policy_Phone

.

.

From 

table_01  t1 join table_02 t2

on t1.policy_Key=t2.policy_Key

and t1.Transaction_Dttm between t1.Start_dttm and t2.End_dttm;

 

 

This above join takes hours to run even if i want to retreive data for some 500K policies. Everyday I'll be extracting data from these tables for that corresponding date/Transaction_date for loading marts.

 

Please help me in assigning partitions so that the data retreival will be faster.

 

 

 

 

dnoeth 4628 posts Joined 11/04
07 Nov 2014

You have to pay a price when you store data in name-value pairs instead ot rows...
 
You'll find a lot pros and cons (well, mainly cons) when you search for the data model called EAV, Entity-Attribute-Value.
Good luck trying to implement this monster :-)

Dieter

You must sign in to leave a comment.