I'm a database purist. This means that I like, no, adore database theory and the technology behind databases. Databases are one of the most high-tech software systems we all work with today, although not a lot of people will recognize them as such. As a developer using Microsoft software for years, I used SqlServer, starting with v6.5 till today with SqlServer 2000. Lately, I had to work with Oracle 9i to write the Oracle driver for LLBLGen Pro. This required me to learn more about Oracle than the usual SELECT * FROM Foo material. What I discovered was something I didn't expect: Oracle 9i is amazing.
Some people I talked to about Oracle were all telling me the same things: the tools were crap, the documentation sucks, it's a pain to work with, the run-time optimizer is not up to par with SqlServer 2000's and so on and so on. For the tools and the documentation, I agree. But are these mandatory for judging a database's value? I don't think so. For the run-time optimizer, I don't think that the 9i optimizer is really that bad, about as good as SqlServer 2000's. I wasn't impressed by these "but Oracle is not that great"-FUD. What impressed me was Oracle's feature set. Looking at that feature-set you can only conclude: what Oracle 9i offers today is generations ahead of SqlServer 2000 and we (that is, the .NET developers) can only hope Yukon gives us what Oracle 9i already offers. Oracle 9i also showed me a severe flaw in .NET which can only be solved by Yukon which is a year away.
The reasons why I find Oracle generations ahead are listed below. Keep in mind that I'm a database purist and you're probably a database pragmatist. I'll write about the differences between purists and pragmatists in another article.
Sequences. Oracle doesn't have 'Identity' columns. It has sequences. Sequences are objects which control a sequence of numbers in an atomic way. Although the Identity functionality can be seen as a sequence, it is not, for the reason that the 'sequence' of numbers an Identity column creates is bound to the table instance the column is in. Re-create the table, the existing 'sequence' of Identity values is gone. With sequences as Oracle defines them, you don't have that problem. Another problem with Identity columns is that they're part of the DDL of the table. A sequence in Oracle is an object in the database and is not part of the table. This means that the DDL is portable because the values for the column(s) that are supplied by the sequence are retrieved from an object outside the table, i.e. the sequence, and thus can be replaced by another object outside the table, for example a stored procedure, a trigger.
MVCC. Multi-version-concurrency-control (MVCC) is a feature that is something a good database needs to have however SqlServer doesn't have it. In short, it modifies data in a table field completely outside the table field and replaces the data in the table field when the transaction is committed. For other threads working with the same table field it is completely transparent how many transactions currently are working on that table field. This has the advantage that no thread is blocked by a lock from another transaction. In SqlServer this isn't possible without specifying NOLOCK as select hint or simply starting a transaction that allows uncommitted reads, which is bad (because what to do when the transaction rolls back? A read of uncommitted data might have happened). When you don't do all this, you can run into dead locks.
Schema driven. Oracle doesn't know the 'database' concept as SqlServer does. When you have only worked with SqlServer and are then confronted with Oracle, you probably will be very confused with Oracle's different view of the world. "Where are the databases?". Oracle is the database. You have schema's and these schema's are bound to a user, the user you login with. This is amazingly simple and yet very powerful: when you log in as user U, you see the objects you are allowed to see. That's your world. This way you can work with 'views' on the data in a pragmatic way: application A needs table A, B and Z. Create a schema and add these tables to that schema (using synonyms, see below). Login with the user equivalent to the schema and you're done. No requirements of cross-database references. To me this is what database systems are all about: offering different views on the data they contain and maintain. Database systems should extend this further and offer multi-paradigm views on the same data as well: relational and Object Oriented.
Synonyms. One truly great feature is synonyms. Synonyms are used to hide away the real objects you work with. This can be very helpful when you want to construct a new schema for a new user. Just create synonyms and you're set. It also helps with maintenance, because when objects are renamed/moved you just have to alter the synonyms, not the programs targeting these objects.
Truly user definable types. We now come to the core of this article: user definable types (UDTs). UDTs are what makes Oracle truly generations ahead of SqlServer. UDTs in Oracle are not just silly redefines of existing native types, but are true new types, with properties and methods. If I'm not mistaken, Yukon doesn't support these, it will fall back on XML. UDTs are limiting portability of the DDL, but are extending the DDL inside Oracle with great power. This power really shines when you take into account that you can use these types as real objects inside Oracle by using Java. No more external check constraints, you can define these right inside the UDT and access the UDT using Java (or PL/SQL!) which is called from your Java application. .NET can't work with these UDTs. It simply can't work with them. No current .NET provider for Oracle (ODP.NET nor MS' Oracle provider) can work with tables with UDT columns. The reason for this is that for example the DataSet can't deal with custom types: a DataColumn has to be of a type in a range of a select group of native .NET types. This is a serious flaw. Because the DataSet can't cope with UDTs, why bother implementing UDT support in the .NET provider? This however limits Oracle usage from .NET and not because Oracle lacks some functionality, but .NET does.
Support in .NET for UDTs is important. Not because everybody needs them, but because when .NET has support for UDTs, the framework's design is flexible enough to face the future and that design is done with that future in mind. In Java this problem is non-existent: an n-tier application can be implemented inside and outside of Oracle (or DB2 for that matter), using the full potential of Oracle (and DB2). On .NET we never will because the design of .NET is not targeted on flexibility, but on integration. Integration of Microsoft software: .NET will offer somewhat the same functionality as Java will with .NET 2.0 and Yukon, however this still doesn't make it possible to target Oracle and DB2 with full power. I hope I don't have to remind the reader what the total market share of DB2 and Oracle is compared to SqlServer.
As a database purist, that makes me sad and I'm sorry to say it, but all those hail and praise stories about .NET compared to Java... forget it. .NET is great, but it has a lot to learn when it comes to databases and I only hope it is not too late.
Previous Topic
