Learning “New and Exciting” Things About Really Old Stuff
…that’s what Max Kanat-Alexander seems to be doing based upon his recent Oracle-bashing rant. Now, I’m not calling Max to the mat because I’ve learned that the Web grants virtual get out of jail free cards to people earning a living developing free stuff, and I have in the recent past been a user of bugzilla (Max is the primary developer), albeit not by choice. I never could get too excited about a bug tracking system that wasn’t integrated with customer support, contracts, field logistics and other general CRM. There certainly is no shortage of bug tracking software, but I’m not blogging about that.
Hey, Old Dogs: Time For New Tricks
The bit I don’t like about Max’s rant is the absurd assertion that Oracle professionals must certainly have never used any other database. I quote:
Most Oracle DBAs, it seems, have never used any other database system. Or they have, but it was in Ancient Times before there was a SQL Standard or something. (By the way, that would have to have been before 1992, when SQL-92 was made. Hi, welcome to the 90’s!)
The nineties? Please! The first SQL ANSI standard was 1986–7 years after Oracle made the first SQL-based commercial RDBMS with lessons taken from the System/R and other playbooks. Yes, 1986, which according to Max’s profile coincided with his days in elementary school. That coincided with the time period in which I was developing and maintaining Informix ACE/ALL applications that fronted IBM 370 mainframes. No, Max, Oracle professionals are not, by and large, rdbms-xenophobes. In fact, the opposite is true. Most shops that deploy Oracle also deploy other products because they have real data centers. By the way, using databases before there was a SQL standard (1986, not 1992) wouldn’t have much to do with SQL because it was, uh, quite scarce.
Max quickly throws us a bone:
I don’t think Oracle is a totally worthless product.
But seemingly recants with the following red-herring:
I know that my Oracle install stopped working once just because I had added, oh, a fifth database to it. Apparently you have to explicitly tell Oracle (with a very cryptic command that’s specific to just your system, because it involves filesystem paths) that you want to have more than about five databases.
I’m not even going to validate that assertion by discussing it. Wait, I changed my mind. No, I’m not going to do it-the assertion is absurd. Just because one tries to base more than 5 databases from a single ORACLE_HOME and had a filesystem-related problem certainly doesn’t mean it can’t be done or isn’t supported. It’s all about configuration resources. How about 80 databases from a single shared ORACLE_HOME in a cluster?
No, Max, we don’t like Oracle because we are ignorant. We use it to solve problems. Can some of those problems be solved by free stuff? I suppose, but I don’t care.
Max continues:
Okay, so I’m biased and I have an unusual viewpoint…[text deleted]…Most people aren’t porting a shipping ANSI SQL application to many different databases. But I am, which means I’ve learned a lot about all the databases.
Huh? Applications supported on all of Oracle, DB2, SQL Server, etc, etc? Avante Garde!
Finally, Max lays it all out there in true protest style:
- In every other database out there, an empty string and NULL are not the same thing. The Oracle SQL Reference tells you not to treat an empty string like a NULL (because they might change that behavior in the future), but they don’t actually give you any way to not treat it like a NULL!
- You can’t SELECT a CLOB (that’s a TEXT field to the rest of the world) if there’s a GROUP BY clause. What?
- Subtracting one month from March 29, 2007 gives you…February 29, 2007, a day that never existed. In fact, because it never existed, Oracle throws an error if you do that. Other databases just give you February 28 (or March 1 if you’re adding, I think).
- Oracle doesn’t support the ANSI SQL “LIMIT” clause, it uses something weird in the WHERE clause instead.
- Oracle has a hard limit on IN clauses of 1000 items. But it doesn’t complain if you OR together multiple IN clauses with 1000 items each…
- Oracle doesn’t allow identifiers to be longer than 32 characters (index names, column names, etc.).
- Oracle doesn’t support ON UPDATE CASCADE for foreign keys. Even MySQL supports that, nowadays.
And each of these 7 points have not been churned over and over about a million times on the Web?
Readers, have anything to say about this?
Kevin Closson's Blog: Platforms, Databases and Storage 
I had no idea I was using such a useless database engine. Lucky for me I only wasted 14 years of my life. It could have been a lot worse. I shall endeavor to ditch Oracle and switch to one of the myriad of competitors that do everything better and have no drawbacks at all.
Please thank Max Kanat-Alexander for bringing this to my attention. Thank you Kevin for passing on these words of wisdom.
Where would we be without reasoned and unbiased arguments… 🙂
Cheers
Tim…
PS. I like Bugzilla. I use it all the time with a mySQL back end. 🙂
Would it make any difference to him if we did?
In dutch we have an expression: wat baten kaars en bril als de uil niet ziene wil.
I believe the english equivalent is “You can lead a horse to water, but you cannot make it drink”
Tim,
It took me four years from 1986 to become less biased…biased that Informix was better (on Unix/Xenix) than Oracle was. All those 12-step programs are behind me now.
FWIW, here is a partial rebuttal to Max’s assertion that “subtracting a month from 29-Mar 2007 gives 29-Feb 2007 in Oracle, which is a date that doesn’t exist”…
SQL> select add_months(to_date(’29-MAR-2007′,’DD-MON-YYYY’), -1)
2 from dual;
ADD_MONTH
———
28-FEB-07
Not sure what Max was doing, but it’s pretty clear the problem is EBKAC…
Also FWIW, I worked extensively on Informix C-ISAM (non-SQL) starting in 1984, then moved to UNIFY (SQL-based) in 1985, B-Trieve (non-SQL) in 1987, VMS RMS (non-SQL) starting in 1987, Sybase v4.x (SQL-based) in 1989, Ingres (SQL-based) in 1990, Oracle (SQL-based) starting in 1990, DB2 and SQL Server along the way too. I work entirely on Oracle these days because it pays — no religion, no bias, no passion — just trying to pay bills.
I moved from Ingres to Oracle. There were things about Ingres I preferred over Oracle, and vice versa.
The problem with focusing on the ‘SQL syntax’ level is that it misses out on differences in the underlying engine regarding concurrency/consistency/scalability mechanisms, which aren’t obvious when doing a conversion from one DB to another (ie they don’t throw out syntax errors).
If you have 1000 values in your in list, let alone multiple in lists of 1000 values concatenated with an OR, you should seriously consider using a temporary table and using a join. Even MySQL has had them since version 3.23.
Tim,
I think Max was doing this:
SQL> select date ‘2007-03-29’
2 , interval ‘1’ month
3 from dual
4 /
DATE’2007-03-29′ INTERVAL’1’MONTH
——————- —————————
29-03-2007 00:00:00 +00-01
1 row selected.
SQL> select date ‘2007-03-29’
2 – interval ‘1’ month
3 from dual
4 /
– interval ‘1’ month
*
ERROR at line 2:
ORA-01839: date not valid for month specified
Regards,
Rob.
I provide Oracle applications support and software to all of my clients. With regards to your blog. I have been using Oracle for many years now and the reason that i am still using it because i think apart from theodd problems (coding and script) i still think it is the best database engine outthere. The scope of the possibilities are endless and if you take time to get the coding right and look at the script in detail you will be able to see the possiblities.
What do you guys think in regards to: Bugzilla?
I left a comment over there. After all, DB independence is a myth (see Tom Kyte for example). Apparently Max is grumpy because Oracle made his life harder than expected. Presumably he was not prepared to expect differences in light of an existing standard… Welcome to reality!
looks like he missed a few,
oracle has a hard limit of 1000 columns per table, honestly how am I to impliment my generic concrete elephant datamodel with this kind of limitation?
When I take the code I developed an performance tuned on SQL Server/Mysql/Sybase/whatever and run it in Oracle its really slow and buggy this is because oracle is crap.
When I take the code I developed an performance tuned on Oracle and run it in SQL Server/Mysql/Sybase/whatever its really slow and buggy this is because oracle is crap.
How come I pay £20,000 per CPU for oracle, and its slower than SQL server, I don’t want to use all these features I’m just an ANSI sql guy.
Why won’t oracle let me read inconsistant data like SQL server will? whats this undo and rollback for?
Seriously though trying for database independance is usually harder than you think, generally your going to need to customise for each database anyway.
I think we should rename Max to Min.
Mogens
Holy Smokes, Mogens, I’m so glad to see you stop by. I miss our routine chats, but I promise after OOW I’ll be back in standard form!
— Quote
When I take the code I developed an performance tuned on SQL Server/Mysql/Sybase/whatever and run it in Oracle its really slow and buggy this is because oracle is crap.
When I take the code I developed an performance tuned on Oracle and run it in SQL Server/Mysql/Sybase/whatever its really slow and buggy this is because oracle is crap.
— Unquote
Based on the symmetry in these statements, one could just as well conclude that that SQL Server/Mysql/Sybase/whatever is crap.
I guess developing database independant software is crap.
Shakespeare
I think Min is making a couple of (bad) assumptions.
1. SQL 92 is the be all and end all of the SQL standard.
2. Not recognizing that most db’s don’t comply to ALL levels of that Standard.
3. Just complying with the syntax doesn’t mean you will get what you want. There is still latitude for interpretation. SO not all db’s will work the same.
Having used Oracle now for 10 years and having only recently been exposed to MS SQL Server (1 year), I can say that one thing Oracle really needs to improve is the patching process. Asking the DBA to run a script that generates a 1000+ line log file with hundreds of errors and warnings that needs to be manually compared against a list of “ignorable” errors and warnings seems a little primitive. In 2008.
I started on non-SQL relational databases in 1980. I first encountered Oracle in 1983. I disliked SQL for a long time as a result – I used to cite those Celko puzzles as a defacto proof some ordinary business processes are hard in SQL. But love it or hate it, SQL has become the defacto data interchange standard in the database world. Is that what it was designed for? Would we rather be doing everything in XML? Yechh.
I also became quite the RMS pdp/VMS expert, since that’s what paid in the ’80s. And hit up unify, sybase, inatleast3somethingorothers and db-independence in the ’90’s, though by 1989 I had explicitly decided Oracle/unix was the big payday. So I often wound up being the Oracle guy in db-independent shops. The last decade, enterprise apps.
But as everyone now knows, Larry is a genius. Because, it’s the apps.
Whoever posted about isolation levels and locking hit the nail on the head about database independence, as far as I’m concerned. Why is it that db-independent developers never know about that?
I feel trapped in a Vonnegut novel as I write again essentially the same first program I wrote in Basic+ as a jr. programmer. Only now it is some complicated DW ETL and download to Excel, hitting Oracle subquery bugs along on the way.
more that 4 databases per Oracle home get real..
Here are my counts.. Each line is a separate host.
“VSN” “COUNT(*)”
“11.1.0.7.0” 15
“11.1.0.7.0” 3
“11.1.0.7.0” 1
“11.1.0.7.0” 7
“11.1.0.7.0” 19
“11.1.0.7.0” 14
“11.1.0.7.0” 3
“11.1.0.7.0” 37
“11.1.0.7.0” 34
“11.1.0.7.0” 24
“11.1.0.7.0” 6
“11.1.0.7.0” 1
“11.1.0.7.0” 3
“11.1.0.7.0” 1
“11.1.0.7.0” 2
“11.1.0.7.0” 6
“11.1.0.7.0” 3
“11.1.0.7.0” 4
“11.1.0.7.0” 6
“11.1.0.7.0” 2
“11.1.0.7.0” 2
“11.1.0.7.0” 4
“11.1.0.7.0” 8
“11.1.0.7.0” 5
and this is only the 11.x databases, if I include my 10.2 databases, some of my hosts would have 70+ databases on them.
Steve D,
That’s quite a consolidation environment! So, does even this sort of system exhibit really high kernel mode overhead? What platform is that? AIX? Solaris?