Oracle is the most feature-rich database in the industry, and the most expensive, and the most complicated. This comes with benefits and curses.
It is freakishly powerful. If you want, you can roll back just your view of the database to three hours ago. You can show different versions of the same schemas to different clients. You can dynamically scale out your database to hundreds of servers without having to manually shard. You can do multi-master replication, master-to-slave, master-to-slave-to-master, any combination you can think of.
Your database clients can automatically detect when a primary has gone down and fail over to a standby, which will automatically be brought online, no admin intervention necessary.
If you give Oracle raw disks, you can tell it to automatically use the faster parts of the spindles for more commonly used data. Or give it some SSDs, and it'll use it for cache. Or, if you buy Oracle storage servers, it'll actually offload basic query execution to the storage.
With that power comes great cost and complexity, which is why many web companies don't bother with it - when you get to google or facebook scale you build these types of capabilities into your application tier.
But I know of a ton of multi-petabyte Oracle implementations at big traditional companies, and they love it. Because they don't want to have to build all of that functionality at the application tier, and they trust Oracle's reliability.
I guess I'm just biased towards solving scalability problems at the application level. It seems like an uphill battle to take a declarative/descriptive language like SQL and tune it to execute a query the way you want it executed -- it seems a lot more straightforward to just write code that does what you tell it to.
No, I was referring to Oracle RAC, which does away the need for sharding. All of your nodes see a complete and comprehensive picture of the data on a shared set of disks.
> I guess I'm just biased towards solving scalability problems at the application level.
That's a totally valid strategy, and indeed, an option that many companies go for. But you're just shifting complexity from one place to another. Either you're going to be building in complexity to make your application aware of data distribution, movement, sharding, and so on - or you're going to use a more complex data storage platform like Oracle.
And SQL tuning is a skillset, much like writing good code. If you are good at SQL tuning, it's not that hard.
My point is just that it depends on your business requirements. I personally think Oracle's price point is so high that I would never use it. But if money were no object, and I was designing an application, why would I want to have my application have to think about where the data lives?
Wouldn't it be a lot simpler to just say, "go here for your data", and let the dedicated application deal with that?
Or put another way, in the same way it seems insane to shove 100k lines of business logic into the database layer, why doesn't it seem insane to shove 100k lines of data management logic into the application tier?
Oracle, despite being the very definition of evil, has one hell of a software product. It is performant, has built-in features that makes Postgres look like BerkelyDB, HA solutions, and perhaps most importantly, the backing and support of a multi-billion dollar company.
Oracle doesn't have any magic pixie dust that changes your underlying hardware. If you want raw performance, BerkeleyDB will wipe the floor with Oracle, because it's that much simpler. You've just got a much smaller feature-set as a result.
You probably see things differently than me, though. Related question since you're a DBA -- what are the best practices for testing PL/SQL, you have a link or anything? Every place I've seen it done was a nightmare and involved a lot of crossed fingers during releases.
Because the boss told us MySQL was cheaper.
Because of MySQL limitations, I think it is even more unmaintainable now. In real world terms, it is ugly and about ten people out of 500 can fix stuff there.
I'm saying that your business logic shouldn't be there. You've got a whole universe of programming languages, potential disk formats and all that available to you. If you're writing huge ugly queries, and I have, that's when I generally step away from the computer for a bit and think if that complexity is better managed someplace else.
So far, besides the ugly MySQL workarounds for some missing features, it has been a good decision.
And well, it was a company decision, they are the ones trapped. I'm free from maintaining it, while I had to maintain the first version with all business logic outside the DB.