Index/Partition design for Transaction Tables

Database
Enthusiast

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.

1 REPLY
Junior Contributor

Re: Index/Partition design for Transaction Tables

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 :-)