[jbooth]

Why would someone run Oracle in this day and age? "We have 100k lines of established PL/SQL running our business" is a reason, of course, but you could make a similar argument that COBOL development is alive. PL/SQL pretty much always leads to an unmaintainable mess, and if you need BIG DATA, you can go much bigger without Oracle than you can with it, and cheaper to boot.

[mattzito]

Guh - every few months this comes up, and it's fairly hilarious to me how much anti-oracle dogma there is here.

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.

[jbooth]

Well, I'll admit that I haven't used Oracle in 4 years, but back then, the automatic-sharding thing (goldengate IIRC?) just plain didn't work.

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.

[mattzito]

GoldenGate is for multi-master replication and shares a lot of the challenges you'll find with any multi-master replication solution.

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?

[falcolas]

Because at a certain size of business, paying developers to make patches against your favorite OSS database doesn't scale. It's cheaper to pay for a, let's be honest, damned fine piece of software.

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.

[jbooth]

Eh. Different strokes for different folks, I guess. Most of those built-in features are anti-features IMO, and none of them make up for having to maintain long-ass PL/SQL functions as opposed to a reasonable programming language. And as far as performance goes, SSDs are cheaper than Oracle licenses, by a lot.

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.

[Shorel]

I converted one of these ugly PL/SQL unmaintainable mess from Oracle to the MySQL query language.

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.

[jbooth]

Well, IMO, you're saying that you basically trapped yourself in a box there. If you're completely determined to embed your business logic into a relational query language, then PL/SQL is probably the best there is.

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.

[Shorel]

The stored procedures are not actually big ugly queries. But they surely have business logic.

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.