Create table, Partition strategy, and pre-planning

[SimonSays]

Hello to all, thanks for having me on your forum.

I am here doing some pre-planning research and after a few days am left still scratching my head. Rather than go on and on about where i went wrong, i am hoping to explain what it is we are trying to do and get a little help from someone to point us in the right direction.

First i would like to comment that we had done this in microsoft SQL already and found that MSSQL just does not fit the bill for what we need. My only DB experience is with MSSQL and even that is only basic knowledge so as far as oracle is concerned please consider me a complete idiot. I will NOT be offended if you draw it out in crayon like i am two years old. That is more of what i need at this point.

First the basic table design

pk	address	privk	balance
Primary Key	(34) Varchar	(51) Varchar	(20) int (can be null)

Next: Scalability

This database is HUGE and getting bigger by the minute (well it was until MSSQL couldn't handle it). For lack of spending a paragraph explaining, we will just say for sanity sake the size of this DB is Infinite as it could grow at 1TB a day for all of time and never stop.

Platform: Hardware and Software
The old Config was Windows server 2012R2 with MSSQL 2012 EE Irrelevant now

Hardware: Several of these machines

360TB storage array (60 x 6tb hard wired HDD'S) with a redundant 320gb boot drive
64 Gig ram
4 x E5 processors

Software: Ubuntu with Oracle 12c

Planning: things we are trying to consider

1: Due to the massive size of the DB, we need to optimize it for queries, I have read a little about partitioning options and particularly a partition key, it lead me to think we could increase query time by storing data in a partition base on the first 2 characters of the address hex. But I am unclear if that is how it works.

2: Overflow onto other servers, as this thing continues to scale we will fill beyond the 360TB we can store on a single “node” or server and need to consider how / if we can spread the table / partitions across several networked drives

3: Indexing, I dont know even where to start with this in terms of scaling.

4: one click deployment. Would it be possible once we figure out the DB design to add everything we need for the design into a create table document so we can simply copy / paste and exicute and at the end everything is ready to go.

5: anything else a novice would overlook when designing the database that you pros might be able to think of.

All in all at this point I am not looking for anyone to do any work or to hire anyone yet, I am simply looking for a little friendly advice from the pros as sometime soon we will hire someone to get it set up and I don’t want to be sold anything that is useless, doesn’t need to be done, or have anything skipped because someone was lazy.

You know how it goes, you need plumbing advice talk to some plumbers, your roof is leaking talk to some contractors......... getting ready to build a massive Oracle 12c DB? Talk to the pro DBA's to get input.

Thanks in advance for your replies guys, from what I have read over the past few days there really is a great group of helpful guys here. If I left something important out, please point it out and I will try and reply quickly.

[LKBrwn_DBA]

You can start by reading the fine manual: Oracle® Database VLDB and Partitioning Guide which will answer many of your questions.

On personal experience my advice would be: when partitioning a table do not overlook archiving (or removing) older information, otherwise your database will continue to grow unchecked.

Good luck!

[SimonSays]

You can start by reading the fine manual: Oracle® Database VLDB and Partitioning Guide which will answer many of your questions.

On personal experience my advice would be: when partitioning a table do not overlook archiving (or removing) older information, otherwise your database will continue to grow unchecked.

Good luck!

Thanks LKB,

I have read the guide a few days ago and to me it might as well of been in a foreign language. I guess the problem was, it explains a lot of different types of partitioning, but really didnt help to describe which would be the best methodology for our particular data set. I have been poking my nose around a lot of forums, chat rooms, and other places DBA's hang out and i have gotten different answers or directions to go from all of them. I guess Oracle DB design is kind of like quantum physics where a lot is theory, and research. I have the feeling i am going to spend almost 5x more time deciding on the best approach to take than it will actually take to have someone implement it when the time comes to get it done.

Thanks for the info about arching, well suggesting it. I am not sure what is considered archiving in Oracle. But 95% of the queries we will be doing check a small data set (10-15 "terms") checked against the entire database. I am not sure if having data used as part of a query just to see if exists in the database excludes it from being "archived" but if so then we dont get to archive anything. Although once each row is written it will next to never be changed, modified, moved or otherwise touched short of querying to see if it exists in the database.

[LKBrwn_DBA]

Archiving just means to get rid of old (obsolete, unused) data.

Basically partitioning among other things would depend on the access path to the data and how you wish to cluster it.

For example, how would the 95% of the queries map to the partitions? you would want that each query set map to one partition (if possible) and/or the result for each query be returned with minimum I/O gets.

Range Partitioning provides the means to cluster your data by a range of values, like for example "dates". Excellent for queries which conditions have the partitioned column as "BETWEEN a AND b".

Hash Partitioning is that Oracle takes the partition key divides it by the number of partitions and using the modulus evenly distributes rows among those partitions.
Good for "direct-read" queries

List Partitioning allows you to partition by a list of "values", in your case it could perhaps be "terms"?
HTH

[SimonSays]

LKBRWN,

I have read a ton of white papers and documentation over the past week and none of them made as much sense to me as what you just said. Clean, cut, and to the point. Thank you !

One of the biggest problems we are going to face is that nothing is ever obsolete, the data is only ever "complete" meaning it will never be overwritten or modified.

we have not gotten as far a queries yet as it is a complex problem and we have not settled on what we should be doing for design yet. The query problems are going to be a whole different issue as we are checking a small data set, against the very large data set constantly. I will try and outline it below in an understandable fastion

Small data set of maybe a 50 entries ---- query to see if the entry already exists in ------(MASTER DB W/ QUADRILLIONS OF RECORDS) ---- return only the values from query that already exist in master DB.

With that, the data could be on any partition i believe although we can narrow it down with the key. We are first looking into the most logical way to partition the database

[LKBrwn_DBA]

. . .

Small data set of maybe a 50 entries ---- query to see if the entry already exists in ------(MASTER DB W/ QUADRILLIONS OF RECORDS) ---- return only the values from query that already exist in master DB.

With that, the data could be on any partition i believe although we can narrow it down with the key. We are first looking into the most logical way to partition the database

At first glance, testing for "existence" would be more efficient partitioning by "hash" on the entry value, but not that efficient if you want to return a set of rows related to each entry from the query list. For example if each entry returns more than one row (vg. 50 entries return 75 rows).

[SimonSays]

I think i understand what you are saying, and if the search has 10 hashes to check for, it only returns 10 or less than 10 results as technically the result should always be "0" or no results found.

Not to sound dumb but if i am off base could you explain it to me like i am 2, i dont like to assume and when i read a lot of this i can only assume that the things you are talking about are just like MSSQL in which i am finding out Oracle is nothing like MSSQL

[LKBrwn_DBA]

. . . if the search has 10 hashes to check for, it only returns 10 or less than 10 results as technically the result should always be "0" or no results found.
. . .

Exactly, to take advantage of hash partitioning, each hash to check should correspond to one result = exist or not exist ... That is the question.

[SimonSays]

Ok, thanks a million for clearing that up.