table partition strategy for data loading performance & data retreval
Hi there, need your suggestions.
we are using the Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
We have a HR data approximately of about 270 mill for 100,000 persons in an SAP module, & we need to load this data into an oracle table.
This is an accumulated data for the past 2 yrs starting from Jan 2011. & with the assumptions that the data will be incrementally loaded every day is about 350,000.
The data granularity is available at the fraction of the day known as TIME_TYPE for a given day.
for example an person can have multiple records on a given day, depending upon the TIME_TYP.
sample data:
Pers_ID Payroll_date Time_Type Day_Hrs
1960 1-Jan-11 Maximum Vacation 4
1960 1-Jan-11 Vacation Quota Maximum 2
1960 1-Jan-11 Maximum Sick 2
1960 2-Jan-11 Paid Eligible OT Hrs 3
1960 2-Jan-11 Paid Hours 3
1960 2-Jan-11 WW Eligible OT Hrs 2
1960 5-Jan-11 Daily Overtime Hours 2
1960 5-Jan-11 Weekly Additional Hours 2
1960 5-Jan-11 Personal Quota Balance 2
1960 5-Jan-11 Total Overtime Hours 2
The above data is of an individual person, his time spent on a particular day over a 3 day period.
My question is how best I can design the table (partitioned table), so that the data loading process can be fast (for the initial load as well as the daily incremental load)
also we have lot of reporting on this table, few reports such as total no. of hrs utilized by a particular employee, whats the most time_typ used by a group of employees, & so on.
please let me know your suggestions & thoughts.
Edited by: user3084749 on Feb 7, 2013 9:56 AM
Edited by: user3084749 on Feb 7, 2013 10:00 AM
Edited by: user3084749 on Feb 7, 2013 10:01 AM