Hi TOM Team,

I am having a Huge table containing more the 100 Billion Records . The table is containing the transactions of more than 6 years and the table Structure is as follows :

Table Name : FCT_TRANS

Table Structure :

ACID varchar(20), TRAN_DATE date, TRAN_TYPE varchar(10), TRAN_SUB_TYPE varchar2(10), PART_TRAN_TYPE varchar2(10), BRCH_ID varchar2(10), TRAN_RMKS varchar2(100), TRANID number, TRAN_AMT number etc.

Indexed Columns : Composite Index ( ACID, TRAN_DATE), Normal Index - TRAN_TYPE.

There is one more table having around 25 Millions Record and is the master table of accouts live in the system.

Table Name : DIM_ACCT

Table Structure :
ACID varchar(20), ACT_OPN_DATE date, SCHM_TYPE varchar(10), SCHM_CODE varchar2(10), ACT_NAME varchar2(50), BRCH_ID varchar2(10), etc.

Indexe Columns : ACID

Now both the tables are system tables and ACID is the mapping key between the two tables.

I want to extract Monthly Summary for Transaction Type-Wise, Sub-type wise , SCHM_TYPE wise Transaction Count and Transaction Amout for the Last Two Years.

Sample OP Format :

MONTH , TRAN_TYPE, TRAN_SUB_TYPE, PART_TRAN_TYPE, SCHM_TYPE, SUM_OF_TRAN_AMT, Count_of_Transactions, Count_of_Uniq_Transacting_Acts

We will have to join the two tables on the basis of ACID for the requirement an will have to take monthly summary for the last two years. Query executed for the requirement is provided below :

Current Query Execution :

select to_char(a.tran_date,'MONYY') TRAN_DT,
a.TRAN_TYPE,a.TRAN_SUB_TYPE, a.PART_TRAN_TYPE, b.SCHM_TYPE,
sum(a.TRAN_AMT) SUM_OF_TRAN_AMT,
count(a.acid) Count_of_Transactions,
count(distinct a.acid) Count_of_Uniq_Transacting_Acts
from FCT_TRANS A, DIM_ACCT B
where a.acid=b.acid
and a.tran_date between '01-11-2016' and '30-11-2016'
group by to_char(a.tran_date,'MONYY'),a.TRAN_TYPE,a.TRAN_SUB_TYPE, a.PART_TRAN_TYPE, b.SCHM_TYPE;

We have tried to extract these data by using simple join and group by but it is taking more than 6-7 Hours for on month only. So, the complete data extraction would take around 6-7 days.

Please help with an optimized process for extracting such data requirement at the earliest .

Also, please provide some sample scripts and optimization tricks to tackle such requirements as I am new to the Oracle Technology and want to learn.