Oracle vs MySQL

[WolfShade]

Hello, all,

We are currently using Oracle as our database in both DEVELOPMENT and PRODUCTION environments.  However, since Oracle is so STUPID expensive, the higher-ups are considering switching our DEV to MySQL.

I have a bad feeling about this, so I'm putting this out there:  How much of a difference is there in query between Oracle and MySQL?  Am I going to have to write conditionals to detect the environment and use different queries for each?  Or are they similar enough that I don't have to worry about it?

V/r,

^ _ ^

[EddieLotter]

As long as you stick to ANSI SQL you will have no problem. It's only when you use features specific to Oracle that you will run into problems.

I'm guessing the vast majority of the SQL statements you use in your apps are generic enough to work for both engines.

That said, I would recommend posting this question in an Oracle forum where there are probably others who are already doing this and might have some vital information that will affect your decision.

[WolfShade]

Hi, Eddie,

For the most part, our queries are simple SELECT colA, colB FROM tableA WHERE... and whatnot.

However, we do have queries like:

SELECT colA, colB, colC FROM tableA ORDER BY colA, colB, colC FETCH FIRST 4 ROWS ONLY, which is not universal, I believe.  (Or is it?)  I see that MySQL uses LIMIT at the end.

V/r,

^ _ ^

[EddieLotter]

FETCH FIRST is ANSI SQL, but I realise I don't know to what level MySQL is ANSI compliant.

You're going to have to do some more research.

[TaylorFan_1]

Greetings,

I think that it's much more convenient to use ready database solutions for both development and production. You might be interested in this: https://www.enteros.com/solutions/ since they offer great options for Oracle, SQL and integration with other database types. We use it in delelopment and we're pleased that it's much more easier to organize all the processes within their cloud storage.

[WolfShade]

Hi, TaylorFan,

That looks like a cloud solution.  While the US government is working its way towards the cloud, we are fighting against it for our projects, trying to keep everything we do in-house.  While we do understand the benefits of cloud computing, we are also much more aware of the risks of placing our data in the hands of a third party, and prefer to keep our paranoid-level, STIG-based, super strict security environment.

V/r,

^ _ ^

[Dave Watts]

They will definitely have differences. For example, the code you pointed out to get the first n rows will vary on each platform. ANSI will only get you so far.

But you might want to consider PostgreSQL, which is free, open source, reliable, and specifically designed to provide Oracle-compatible functionality.

Finally, I don't want to turn this into pro- vs anti-cloud, but there are clear security benefits to moving to the cloud. You do have to make sure you utilize those benefits when the time comes, but they're there. I would probably recommend going with one of the "big three" cloud providers to make the most out of those benefits, though. And of course, you know your environment better than I do, so you have to be the final judge of that.

Dave Watts, Eidolon LLC

[WolfShade]

Thanks for your wisdom, Dave.  I will mention PostgreSQL to my boss and GS, see what they think.  I like the idea, and even with a commercial license it would most likely be less expensive than Oracle.  We recently had to convert an Oracle VM back to a physical server because Oracle bills the license based upon how many cores could potentially be used, not what is actually used, and it would have cost an arm and a leg to keep the VM.

And Oracle wonders why they are no longer being considered for a huge US Govt contract.

V/r,

^ _ ^

[Dave Watts]

Oracle's pricing scheme is basically to hold you upside down by your ankles and see what comes out of your pockets. So I'm not surprised by another example of what looks to me like predatory licensing.

The contract in question, is it DoD JEDI?

Dave Watts, Eidolon LLC

[WolfShade]

https://forums.adobe.com/people/Dave+Watts  wrote

The contract in question, is it DoD JEDI?

Indeed, the very one I was referring to.  Micro$oft and Amazon are the contenders, now, and Oracle has been eliminated.

I seem to remember an article, about a month or two ago, where the Oracle CEO made some kind of statement about "no intelligent person would ever use Amazon" or some such.  CEO probably is in denial, right about now.

V/r,

^ _ ^