Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL.

My company is nearly always choosing Oracle (XE where possible), even for small projects where there is just a single simple Windows server box running the database without any dedicated DB administration. (Note that small does not mean the data will always fit the rather small size constraints of Oracle XE.)

I have always questioned this choice, but it has the benefit that we at least are exposed to only one database product.

Still, given a new project, where you need a RDBMS, but the project and scope of the database is pretty small, based on which unique features of Oracle running on simple Windows server boxes (without too much dedicated administration) would you choose Oracle over another RDBMS?

Additional Context: A lot of our database deployments run at customer sites in a, lets call it, "low-administration" mode. That is, the database is set up once. There is some initial testing on its correct behavior and performance on site. After this, the database just run's on its on. No regular administration done. Only if somethings broken will a technician (not a dedicated DBA) check the database, trying to figure out what's up. Backup is mostly done as offline backup. In some projects, the customers don't even care there's a RDBMS involved. They just see their app as a black box that works (or not).

[a] : Where I work, it took several project managers repeatedly months to get proper licensing for small projects as the local Oracle representatives are simply not very interested in selling their product if the revenue is smallish.

share|improve this question
    
How are they choosing XE for data that does not "always fit the rather small size constraints of XE"? –  Jack Douglas May 5 '11 at 8:33
    
@Jack: if it fits we use XE, if it doesn't ... well we don't :-) –  Martin May 5 '11 at 10:39
    
The 11.2 Express Edition is in beta and the user data limit has been expanded from 4 GB to 11 GB. See oracle.com/technetwork/database/express-edition/… –  Leigh Riffel May 5 '11 at 12:59
    
Oracle has PIVOT, MySQL and Postgres don't. That's a big plus in some situations. –  Phil Lello May 5 '11 at 22:18
1  
@ Phil Lello: PostgreSQL does have PIVOT, check the contrib: postgresql.org/docs/current/static/tablefunc.html –  Frank Heikens Sep 28 '11 at 18:39
add comment

5 Answers

I can only attempt to answer for Oracle and postgres. After using Oracle exclusively for years, and postgres only for the last two years or so, I love postgres. There are so many small ways it is more convenient to use than Oracle, and it shares many of the crucial benefits (such as MVCC). It is easier to administer, reliable, has excellent documentation, and of course, it is free.

However postgres is no match for Oracle in some areas, such as:

  • RAC - as far as I know there is no better clustering technology for any database
  • RMAN - far superior to postgres's basic backup and recovery features, especially with block change tracking and incremental backups (which you can apply to other backups to keep an up to date full backup)
  • Oracle support - exists. postgres support? Not so much
  • many other features such as IOTs, 'Secure Files' and compression technology that have no analogue in the postgres world

Interestingly almost all af these features are absent or crippled in XE. I think I'd choose postgres over XE, all else being equal, but...

...none of this addresses the two biggest reasons for choosing Oracle:

  1. You are already using Oracle and you have a huge investment in it (database independence is a silly myth)
  2. Your developers and DBAs know Oracle and use its features to the full (why wouldn't they as database independence is a silly myth anyway?)

EDIT:

The one situation where I'd choose postgres over XE every time is if security is a concern. If your database or any of it's applications are exposed to the public internet I am not sure if XE is a good idea at all.

share|improve this answer
    
rgd. RMAN - So you would say that the backup features of Oracle are "better" than those of Postgre? Would this hold in a low-administration environment (see my Q edit)? –  Martin May 5 '11 at 10:45
    
@Martin - RMAN is powerful and very mature, I trust it as it has seen me through several different recovery situations. One thing it isn't is 'simple'. It sounds like you are making do without expensive DBAs, are you using RMAN for your offline backups at the moment? How are you shipping your (daily?) multiple Gb backups off the client sites? –  Jack Douglas May 5 '11 at 11:54
    
SQL server has HA clustering which would be the equivalent of Oracle's RAC. –  StanleyJohns May 6 '11 at 16:16
    
@stan the dba From what I've read of the clustering options for SQL Server, they are not really comparable to RAC at all, most of them being fail over solutions, not true scale out solutions like rac. –  Matthew Watson May 8 '11 at 9:54
    
Your 2nd reason for choosing Oracle is pretty much the same as the 1st, IMO. Basically that you're committed. -- As for support, that certainly exists for Postgres as well. If you're not happy with the excellent mailing lists, check out this list for companies offering paid support in your region. –  eevar May 9 '11 at 13:44
show 1 more comment

Oracle Apex. A handy, easy-to-use web application environment built right into the database. Quite simply it makes it very simple to deploy 'single-box' applications with the web ui/application logic/database in a single integrated package.

PS. 11g XE (currently in beta) expands the storage to over 10GB.

share|improve this answer
    
+1 for APEX. While it has its issues and gremlins, I LOVE this for getting applications off the ground quickly and easily. As RAD for Oracle as one can get, I think. –  Kerri Shotts Aug 16 '11 at 21:37
add comment

Although Oracle's other flashback options are not available in Express Edition, Flashback Query is. According to the question on it, no other database has this feature that allows a select statement to query data as of a point in time in the past. Flashback data can be joined with current data and inserted into current tables making it useful for undo type operations, temporary changes, and comparing changes made by a method in one place.

Some other things that some other databases do not have that Oracle Express Edition does.

share|improve this answer
5  
postgres ticks the 'AuthID Current User vs. Definer', 'Check Constraints', 'Deferrable Constraints', 'Date addition and subtraction', 'Hierarchical Queries', non-schema triggers, most 'Analytic Functions', some 'Collection Functions', 'XML functions', clob up to 1Gb, 'Create or Replace' and 'Multi-Platform' boxes too (at least) –  Jack Douglas May 5 '11 at 14:13
    
@JackPDouglas It is difficult to create a pros list when the competition is "everything else". Thanks for the info. –  Leigh Riffel May 5 '11 at 14:21
    
@Leigh - agreed, I'm guessing your list is helpful reference for Martin. You don't mention RMAN, at least in the current (v10) XE it is included although not used by default. Just one nit-pick: if XE is limited to 4 or 11Gb then Clobs up to 128Tb are not really supported :) –  Jack Douglas May 5 '11 at 14:27
    
@JackPDouglas You had already mentioned RMAN and I didn't intend to supplant your answer (or any others). –  Leigh Riffel May 5 '11 at 15:38
    
@JackPDouglas Good catch on the clob. I left it on the list because smaller clobs can be useful in XE and if the database grows then being on a platform that supports extremely large clobs may be useful. –  Leigh Riffel May 5 '11 at 15:43
show 5 more comments

First of all, I think you should not underestimate the factor that there is already Oracle Know-How present but not for other RDBMS. Building up knowledge for the others takes time & money and may be also accompanied by some mistakes at the start.

Furthermore, you never know whether a small project will not grow somewhen. Then, you can very smoothly & fast upgrade from XE to Standard Edition One to Standard Edition to Enterprise Edition. A migration from another RDBMS will again take much more time & money. Bottom line: I think your company does it right - ok, I may be a little biased :-)

share|improve this answer
    
Weeell. I'd say the Oracle know how around here is about ... hmm ... I'd probably need 3 hours playing around with Postgre plus a little googleing to get to the point where I could do just as much with it as I can with Oracle. (Except for PL/SQL) And I like to think I'm one of the Oracle power users here :-) -- But your argument about an upgrade path is certainly valid. –  Martin Sep 29 '11 at 22:02
    
@Martin : that's not a good sign ... I've been using postgres for one of our projects (as part of a packaged application) for ~2 years, and I still can't get it to perform well (in part because the parts of the application that I'm using relies heavily on count(), which seems to be postgres's achilles heel). I had to ask on stackoverflow how to influence the planner –  Joe Sep 30 '11 at 14:06
add comment

One of the nice features of Oracle are those like Oracle Resource Manager that make consolidation of applications in one database a lot easier than having a separate database for every application. It looks like this is not used in your organization. I have designed consolidated databases in the past for projects just like you mention.

  • Every project had it's own hardware
  • Every project had it's own licenses
  • Every project had it's own storage

This caused that even the simplest of all projects took months to even get started and in a time where time to market is extremely important, this is killing. For some reason this is missed by many, often even neglected for political reasons.

The solution for this is quite simple. Create one serious database, give every project/application their own schema[s] and access user[s] and get running in hours, instead of months. If you are going to do something like this, it could prove beneficial to combine applications that have similar up-time requirements. Oracle gets more and more on-line maintenance options but for sometimes, getting a few hours downtime is a lot easier. Having time windows for this defined beforehand could prevent a lot of problems. You are going to need some downtime.

Don't allow applications to connect to the database, make them connect to services dedicated to the application using their own tns-aliasses. Doing so enables you to move the application to an other database, without having to re-configure the application.

BTW: the companies that used this way of consolidation saved a lot of cash yearly, more than the licenses required to start rolling.

share|improve this answer
    
Thanks for this input. Note that a lot of the projects have the database running at the customer site(s) so it doesn't really make any sense to consolidate them. (If I understand you correctly wrt. to consolidation.) –  Martin May 5 '11 at 10:44
    
It could still be advised to have it consolidated by the customer where the package is ending up. If it is supposed to be an embedded database it might be a little different. –  ik_zelf May 5 '11 at 11:04
    
To be clear, Oracle XE does not have the Resource Manager feature, so this would only be useful consolidating into a licensed server. @ik_zelf I know it was not your intention to indicate otherwise. –  Leigh Riffel May 6 '11 at 13:42
    
@Leigh Riffel yes, I was assuming there was an Oracle installation available, in such a consolidated scenario I would prefer EE. The question appeared to be looking more towards an embedded solution. –  ik_zelf May 6 '11 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.