Partitioning

[Ahmad Sakhi]

Hello everyone,

I have an interesting scenario, and I was hoping someone could give an advise/suggestion on how to I would go about proceeding with the data model design, with use of PARTITION.

I have a large amount of data currently setting in a set of CSV files (~10 MLLION), and each CSV file can have anywhere between, say 5 to 1000; so on average it would be (if we take 500 rows per CSV) somewhere 10 MIL X 500 records. The (J2EE) application will perform a SELECT based on one ROW <see screenshot>. There’s no JOIN with other tables and I want to avoid that and keep the table denormalized.

Following is a sample of the data <See attached please>. The application will pass a particular URI value, in this example, i.e. MC001. I am thinking to create a (LIST?) PARTITION on URI_CM field. This would create around ~1.5 MIL partitions, one for each CSV file. Of course I will have to think about creating indexes on URI (or any other fields if I need to select). Do you see anything off on this? Or would you have any other (better) suggestion how do I go about modeling this? I will really appreciate your input.

Thanks.


+---+-------+--------+-------+----------+--------+--------+--------+
|ID | SCORE | URI_CM | URI | START_CM | END_CM | TEXT_1 | TEXT_2 |
+---+-------+--------+-------+----------+--------+--------+--------+
|1 | 0.1 | IL001_1| MC001 | 1 | 12 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|2 | 0.2 | IL001_1| MC001 | 2 | 13 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|2 | 0.6 | IL001_1| MC001 | 3 | 14 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|4 | 0.9 | IL001_1| MC002 | 4 | 15 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|5 | 0.7 | IL001_1| MC002 | 5 | 16 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|7 | 0.1 | IL001_2| MC003 | 7 | 17 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|8 | 0.2 | IL001_2| MC003 | 8 | 18 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|9 | 0.6 | IL001_2| MC003 | 9 | 19 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|10 | 0.9 | IL001_2| MC003 | 10 | 20 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+
|11 | 0.7 | IL001_2| MC005 | 11 | 21 | Sample1| Sample2|
+---+-------+--------+-------+----------+--------+--------+--------+