Database design
-
Guys, In a few days I have a meeting internally about redesigning the Oracle database from scratch. The downside is that none of the meeting participants is a real DBA (IMHO we should hire a DBA consultant for advice), but we do have some knowledge about Oracle. one of my major concerns is that someone opted for multiple smaller databases (that should communicate if necessary) I can think of multiple reasons why this is a bad idea, but I couldn't find any satisfactory links to proove it. (Maybe it isn't that bad after all?) I did write some stuff down already (never delete, but rather 'inactivate', use history mechanism etc..) In short: What do I need to watch out for when designing a new database ? If it can help, we're an insurance company, but we also do call taking (could result in some tables having rapid changes when a lot of calls come in) Also we will start with adding one project to it, then a second, a third etc... so no one time porting to another database. Many thanks in advance.
V.
V. wrote:
someone opted for multiple smaller databases
sure, why keep things simple when it is so easy to make them complex? you could as well store each data item in a separate file... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
What's the reasoning behind getting several databases? If it's getting slow because of large dataamounts you should have a look at partitioning instead. Get a good DBA for the project to give you proper advice. It'll pay back later.
"When did ignorance become a point of view" - Dilbert
-
V. wrote:
someone opted for multiple smaller databases
sure, why keep things simple when it is so easy to make them complex? you could as well store each data item in a separate file... :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Mycroft Holmes wrote:
For one, I'm told Oracle does not like multiple databases on a server, the design is to use schemas, I thought this was pure bullsh*t but it came from a DBA so it may be right.
It's BS. It works quite fine. It's just pointless. Multiple schemas is the way to go.
Mycroft Holmes wrote:
For another Oracle is case sensitive when dealing with data so where name = 'Johny' will miss 'johny' IMHO this and the ongoing support requirement enough reason to use another database (sql server).
And the problem is what? Use a function based index and you got the best of both worlds. Example:
CREATE INDEX foo_bar_ix
ON foo (
LOWER("bar")
)
/And then you can search the table with
Select * from Foo where lower(bar) = lower('Johnny');"When did ignorance become a point of view" - Dilbert
Jörgen Andersson wrote:
Use a function based index and you got the best of both worlds
Nice. I didn't know you could do that. A 5 just for that trick.
Jörgen Andersson wrote:
It's BS. It works quite fine. It's just pointless
I suspect he's referring to the load on having multiple instances running as opposed to a single instance running multiple schema.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
Jörgen Andersson wrote:
Use a function based index and you got the best of both worlds
Nice. I didn't know you could do that. A 5 just for that trick.
Jörgen Andersson wrote:
It's BS. It works quite fine. It's just pointless
I suspect he's referring to the load on having multiple instances running as opposed to a single instance running multiple schema.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
Pete O'Hanlon wrote:
Nice. I didn't know you could do that. A 5 just for that trick.
That single trick alone is worth the extra trouble of using Oracle
"When did ignorance become a point of view" - Dilbert
-
Pete O'Hanlon wrote:
Nice. I didn't know you could do that. A 5 just for that trick.
That single trick alone is worth the extra trouble of using Oracle
"When did ignorance become a point of view" - Dilbert
Materialised Views. They are worth it as well. Mind you, we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
Materialised Views. They are worth it as well. Mind you, we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
Pete O'Hanlon wrote:
Materialised Views. They are worth it as well
Agreed. But sqlserver has indexed views.
Pete O'Hanlon wrote:
Mind you, we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I guess that's the major problem with Oracle, that you need one.
"When did ignorance become a point of view" - Dilbert
-
Pete O'Hanlon wrote:
Materialised Views. They are worth it as well
Agreed. But sqlserver has indexed views.
Pete O'Hanlon wrote:
Mind you, we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I guess that's the major problem with Oracle, that you need one.
"When did ignorance become a point of view" - Dilbert
My biggest problem with materialised views is that we can't use any SDO geometry types in them (something that would be really useful for us).
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
Luc Pattyn wrote:
you could as well store each data item in a separate file...
Of course! That's it. Let's save everything in a nice (bloated) XML file complete with metadata, triggers, constraints, stored procedures, the works ... see how that works ;P
V.
V. wrote:
nice (bloated) XML
Know a guy who said XML was the next thing for storing data, a year later he deigned he said it.
-
My biggest problem with materialised views is that we can't use any SDO geometry types in them (something that would be really useful for us).
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
Are you sure about that? I understand that it's severely limited[^]. But not impossible.
"When did ignorance become a point of view" - Dilbert
-
Are you sure about that? I understand that it's severely limited[^]. But not impossible.
"When did ignorance become a point of view" - Dilbert
It's point 9 that's the killer. We have some data that comes in as x,y coords and we want our materialised views to be spatial, so you get screwed up when you try to construct a location sdo geometry.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
-
Mycroft Holmes wrote:
For one, I'm told Oracle does not like multiple databases on a server, the design is to use schemas, I thought this was pure bullsh*t but it came from a DBA so it may be right.
It's BS. It works quite fine. It's just pointless. Multiple schemas is the way to go.
Mycroft Holmes wrote:
For another Oracle is case sensitive when dealing with data so where name = 'Johny' will miss 'johny' IMHO this and the ongoing support requirement enough reason to use another database (sql server).
And the problem is what? Use a function based index and you got the best of both worlds. Example:
CREATE INDEX foo_bar_ix
ON foo (
LOWER("bar")
)
/And then you can search the table with
Select * from Foo where lower(bar) = lower('Johnny');"When did ignorance become a point of view" - Dilbert
Jörgen Andersson wrote:
It's BS. It works quite fine. It's just pointless. Multiple schemas is the way to go.
I suspected as much, it was probably a DBA enforcing the schema requirement.
Jörgen Andersson wrote:
And then you can search the table with Select * from Foo where lower(bar) = lower('Johnny');
I will need to do some work to understand the relevance on the function based index. However I'm used to SS where case is irrelevant when comparing text, there for the lower() is not required. This will continue to bite me until I get used to it. My real peeve with Oracle is the all upper case objects, I hate underscores so my names look like FILENAMEDSOMETHING instead of FileNamedSomeThing.
Never underestimate the power of human stupidity RAH
-
Materialised Views. They are worth it as well. Mind you, we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
Pete O'Hanlon wrote:
we have a top notch Oracle DBA to make sure that I don't have to know how to do this.
I wish. Difficult to find and expensive to retain. I'm in the throes of getting to know Oracle again after more than a decade in SS, I think SS must be like VB, lots of work to make it easy for the developer. Then I aggregated 150m records in minutes where SS was taking hours and remembered why we are using Oracle.
Never underestimate the power of human stupidity RAH
-
V. wrote:
nice (bloated) XML
Know a guy who said XML was the next thing for storing data, a year later he deigned he said it.
I know a company who used xml as the format for etl between major banking systems, stupidest decision they ever made, it worked well with the test system then we introduced them to a production size file.
Never underestimate the power of human stupidity RAH
-
Guys, In a few days I have a meeting internally about redesigning the Oracle database from scratch. The downside is that none of the meeting participants is a real DBA (IMHO we should hire a DBA consultant for advice), but we do have some knowledge about Oracle. one of my major concerns is that someone opted for multiple smaller databases (that should communicate if necessary) I can think of multiple reasons why this is a bad idea, but I couldn't find any satisfactory links to proove it. (Maybe it isn't that bad after all?) I did write some stuff down already (never delete, but rather 'inactivate', use history mechanism etc..) In short: What do I need to watch out for when designing a new database ? If it can help, we're an insurance company, but we also do call taking (could result in some tables having rapid changes when a lot of calls come in) Also we will start with adding one project to it, then a second, a third etc... so no one time porting to another database. Many thanks in advance.
V.
V. wrote:
What do I need to watch out for when designing a new database ?
Managers. I've never seen Oracle used that way (though I've seen it done with Rdb), but I'd need more detail. Are you talking multiple databases, but on the same box and disks (spindles)? I don't think you'd gain much. Can you write SQL statements that cross database boundaries like you can with SQL Server? Can you link servers like you can with SQL Server?
-
Jörgen Andersson wrote:
It's BS. It works quite fine. It's just pointless. Multiple schemas is the way to go.
I suspected as much, it was probably a DBA enforcing the schema requirement.
Jörgen Andersson wrote:
And then you can search the table with Select * from Foo where lower(bar) = lower('Johnny');
I will need to do some work to understand the relevance on the function based index. However I'm used to SS where case is irrelevant when comparing text, there for the lower() is not required. This will continue to bite me until I get used to it. My real peeve with Oracle is the all upper case objects, I hate underscores so my names look like FILENAMEDSOMETHING instead of FileNamedSomeThing.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
I will need to do some work to understand the relevance on the function based index
Say for example that you have a log table where one of the columns has the date datatype which carries both date and time, and you want to get all occurences from one specific day. A query for trunc(logdate) would make a full table scan, unless you have an index on trunc(logdate). Another example, You have a really large table where you have a status column, and the only value you ever make a search on is 'PENDING'. Then an index on "
Case When status = 'PENDING' Then 1 Else Null" will be very small and fast, as only those entries where the status is PENDING will be stored in the index as null values are not indexed. The backside is that function based indexes is costing more to maintain."When did ignorance become a point of view" - Dilbert
-
It's point 9 that's the killer. We have some data that comes in as x,y coords and we want our materialised views to be spatial, so you get screwed up when you try to construct a location sdo geometry.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads
Spatial is totally out of my line of work, but I would have thought point one or ten would have been the real killer. I'm curious, what would have been the purpose of the mv if it had been possible?
"When did ignorance become a point of view" - Dilbert
-
Mycroft Holmes wrote:
I will need to do some work to understand the relevance on the function based index
Say for example that you have a log table where one of the columns has the date datatype which carries both date and time, and you want to get all occurences from one specific day. A query for trunc(logdate) would make a full table scan, unless you have an index on trunc(logdate). Another example, You have a really large table where you have a status column, and the only value you ever make a search on is 'PENDING'. Then an index on "
Case When status = 'PENDING' Then 1 Else Null" will be very small and fast, as only those entries where the status is PENDING will be stored in the index as null values are not indexed. The backside is that function based indexes is costing more to maintain."When did ignorance become a point of view" - Dilbert
Thanks for that Jorgen While the ability to do these tweaks is very good, the requirement to do them is painful. I beging to understand why a full time DBA is a requirement, I consider this type of tuning beyond the requirements of a developer.
Never underestimate the power of human stupidity RAH
-
Thanks for that Jorgen While the ability to do these tweaks is very good, the requirement to do them is painful. I beging to understand why a full time DBA is a requirement, I consider this type of tuning beyond the requirements of a developer.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
, the requirement to do them is painful
Having to use Oracle?
Mycroft Holmes wrote:
beyond the requirements of a developer
The learning threshold is indeed high, especially if you come from the world of SqlServer. BTW, I liked your comparison with VB, had it been more catchy I would have stolen it for a sig ;)
"When did ignorance become a point of view" - Dilbert
-
Spatial is totally out of my line of work, but I would have thought point one or ten would have been the real killer. I'm curious, what would have been the purpose of the mv if it had been possible?
"When did ignorance become a point of view" - Dilbert
We are reliant on a separate supplier for a standardised gazetteer implementation in one particular project (the data's provided by the government), but we need it in a different format (a spatial one for spatial searches). We need the view to be updated on import of data into the master gazetteer. We've worked around the issue, but it would be nice not to have to work around the issue.
I'm not a stalker, I just know things. Oh by the way, you're out of milk.
Forgive your enemies - it messes with their heads