Oracle 9i is already generations ahead and shows a .NET flaw.

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.


  • Interesting read. I never thought about .NET from this point of view.

    Although you are probably right about .NET the lack of support for vendors other then MS, but I would think it’s fair to say “WE” could fill in the gaps ourselves with a strong open-source initiative.

  • Is Scott McNealy blackmailing you? :)

    But seriously, I agree with most of your points, although I think stuff like MVCC and synonyms will probably reach Yukon.

    I'm not sure about the rest though. Also, I must admit I've never used UDTs, but I agree that for large-scale db applications, they're probably extremely important. Also, they tie in well with the whole schema approach, that basically encourages (or rather forces, but in a good way :)) to just make a single environment housing all your data and then creating schemas for individual applications. If you use UDTs there it can save you a lot of time if you create those custom types with generalized functionality in mind for all the different applications that might have a table in their schema containing one of those UDTs. In SQLServer people often don't bother because they'll have another database for "that other" application anyway. Which in turn encourages long-term chaos.

  • Jeroen: Larry didn't need to, he just gave away a free version of Oracle 9i for developers to download :)

    You are very correct about the UDTs and the chaos and the simplicity of maintenance. It really worries me why I haven't read anything about them in the Yukon hype that is available.

    Paul: The limitation is in the DataColumn type in .NET, it doesn't support custom types. That pretty much kills UDT support, unless you are using an O/R mapper with custom classes. At the moment these tools are left in the cold by both Oracle and MS because both their providers are not supporting UDTs. (Don't know about the commercial providers though, but these are extremely expensive). Nevertheless, to unleach the true power of DB2 and Oracle in an OO fashion, you have to write code in Java inside the database and call that code from .NET. You then have to be a very very pro-.NET person to pick .NET for the logic outside the database. I'd then opt for Java / J2EE also.

  • ::Schema driven

    I disagree on this one.

    I mean, you get another product from someone. Having this product's stuff in a separate database is a great way to separate different "modules" (like complete applications systems) from each other.

    What do you do if by chance you need to install two applications having a table with the same name? The message board on your website has a Table named "User", as well as the content management system?

    These are two different systems. How does oracle handle this? I think I prefare the "separate database" way of thinking.

  • Interesting that you claim to be a database purist and yet you like sequences and MVCC. I like those too, but they seem to be "impure" from the relational theory point of view. As to the other three features, I feel that those are misfeatures. They introduce a great deal of complexity into the design of the database without much in the way of compensating benefits. I suspect they belong to the class of features in Oracle that I like to call the "Oracle DBA Full Employment Protection Act". They exist primarily to protect the jobs of high-wage DBAs.

  • "Interesting that you claim to be a database purist and yet you like sequences and MVCC. I like those too, but they seem to be "impure" from the relational theory point of view"

    Based on what are these impure? Sequences are a way to use synthetic keys without having to add information to the model, as I described: they allow you to set field values without having to rely on DDL specifications, which doesn't seem to be impure to me at all. The same goes vor MVCC: as long as a transaction is not committed, the actions taken by the transaction should not affect other users in the system. This is exactly what MVCC realizes. Without MVCC, users will be blocked by an ongoing transaction even when that transaction can roll back. Enabling dirty reads to circumvent this will allow dirty data to end up in the other data, which can cause data inconsistency. MVCC therefore is very helpful in a relational model, and I definitely don't see why MVCC is impure at all, on the contrary.

    Your other claim about 'complexity' is just that, a claim. Can you elaborate it a bit? Just claiming it adds complexity without benefits is not that useful. Synonyms for example reduce complexity for a great deal. UDTs do to, because you don't need to write check constraints or triggers anymore, two elements which are stored 'deep inside' a schema most of the time and are easily forgotten (especially on sqlserver)

  • "because you don't need to write check constraints or triggers anymore"

    I meant, triggers to check values set for fields. There are other areas where triggers can be helpful and these are of course not solved by UDTs

  • Thomas: you can start different oracle instances if you really want to separate the schemas. However when you look at oracle as a set of schemas it's really not different from a set of 'databases' where you have just one schema per database: the dbo one: after all, you just 'see' what you are allowed to see, so if there are hundreds of other schemas with tables, you can't see them nor use them.

    Andrew: No I haven't used UDTs in a large project and your concern about recompilation/modifications of schemas is similar to the disadvantages of stored procedures, however I still think when you design your UDTs with care, they can be a great benefit and will not be of such a disadvantage as it might seem. Nevertheless: the feature is there, but .NET can't use it. This is important: you can't use a table with a column of a UDT type in .NET, nor can you bind a REF CURSOR to a dataset when that REF CURSOR returns rows with UDT values. This can severily limit the usage of .NET on Oracle.

  • Frans,

    i've worked with both products for years, and even if your statements about SQL Server are correct, i don't consider these strategic in a real sense, but they're so important that MS will implement everything (but sequence) in SQL Server Yukon.

    Honestly speaking, i think that Oracle has 2 real main advantages over SQL Server: it's multiplatform and it's less dependant from the O.S. for functionalities like I/O, for example. This let Oracle designers implement stuff like "per User" or "per Group" resource management.

    SQL Server has a main drawback, and it's related to Windows I/O subsystem and operations priority.

    But technically speaking, SQL Server's Query Processor and Optimizer are more advanced than Oracle's one, and the ease in management and operations is not a point of discussion between the two products.

    Usually, i'm not a "taliban" comparing this two different approaches, and i recognize that they're leaders in this market, but please, use more important points to judge who's better.

  • Silvano: I used these points because they are important for developers. SqlServer's fiber option (it's an NT kernel option really) plus the careful placement of datafiles and logfiles on separate disks on separate controllers can eliminate a big chunk of the I/O bottleneck. Per 'user' resources can be implemented in SqlServer too, when you look at users in Oracle as 'databases' in SqlServer: in SqlServer too you can manage where which filegroups are stored, but of course not how many memory / cache that database is allowed to use.

    The multi-platform feature of Oracle is perhaps important to some people, I don't think that it is an important feature to have, mostly because Oracle and also SqlServer are used as standalone servers in a lot of situations (especially Oracle), which eliminates the importance of a platform.

    And seriously: what will come with Yukon is fine, but it is at least 1 year away. Today, I can only opt for SqlServer, which is still the database I know best and use most, but it is aged. The features I listed are important to me as a developer and are not available in .NET nor SqlServer for at least a year. I think people should be aware of what is available today instead of looking at f.e. Yukon and 'what will be'.

  • IMHO, the most important difference between SQL Server and Oracle is its clustering support.

    In SQL, if you want to cluster, you need to partition your database, and perhaps change your application logic. Each cluster is a different database server. In Oracle, you can have any number of database servers accessing the same data. This implies that you can scale out the database very easily, so running business logic in the database is a good option. SQL developers are always looking for ways to remove processing from the database because is the single point of lack of scalability. Oracle developers don't need to be that worried about that.

    BTW, Yukon supports UDTs.

  • Hmmm, how about using the best of both worlds? I work for a firm that up until very recently was exclusively Oracle. We have found the mix of using Oracle for data services and logic (business rules, etc.) mixed with .Net for presentation to be, in a couple of wors, brutally powerful.

  • SQL Server "database" = Oracle "tablespace". This would be a more effective segregation of data over creating a separate Oracel instance, as a completely separate instance would double the resource load on the machine. Creating an additional tablespace for new objects would essentially achieve the same end without the additional overhead.

  • Frans: as a Oracle newbie at a already grown-up technology like Oracle RDBMS you should know better. I mean: Oracle is an ocean where you should have a nice boat to sail into before stating any decent comments. I'm Oracle certified and have 8 year experience at it, and I don't think the Oracle-Unix (or Linux) combination has anything comparable in the Industry (nor even DB2).

    Dig a coupple of years more onto Oracle and you'll find yourself amazed a bit more, even with the tools you so dumbly classified as "Crap".


  • anyone who he says that Oracle docs are better than Microsoft's is whacked. Everyone I know gripes about them. The other day I downloaded a Java sample which wouldn't even compile due to a host of syntax errors. This was on OTN. It's a joke. Every morning, the forums are down.

  • Has Microsoft bothered to fix the feature/bug that you can only have one LONG per table, and that you must code your sql select statements (in ADO etc) in the correct order so that the LONG field is at the end of the query?

    I was geniunely amazed when I ran into this bug myself, and after doing some research online, it turned out that a LOT of people had run into this bug, and that Microsoft even acknowledged it in a Knowledge Base article, but didn't feel it deserved a fix/patch. What a great attitude.

    Oracle is a dream to work with vs SQL Server having used them both for 5+ years as developer and frequent Sysadmin/DBA on Windows, Linux & Solaris.

  • Blah blah blah

    SQL Server is better... No, Oracle is better, No...

    Get over it. There're pros and cons for both products. But since you started arguing: I'll take easy integration over a couple of features anytime. While you're trying to figure out the docs and tools, I can do my work and move on, and don't have to shell out $90k for Oracle DBA.

    To wayne: If you think this is the worst bug in SQL Server(with such an easy workaround) then you should dump Oracle and start using SQL Server right away.

  • public string opinionator(string DiscussionTopic)


    if(DiscussionTopic.IndexOf("Microsoft") > 0)


    return "bad";




    return "good";



  • forgot my smiley... ;)

  • Frans has it right "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..." It's true!! They are amazing when you think about how fast they find what you're asking of them.

    Most people (esp. developers) think of a database as a repository for data, not realizing all the benefits it can give them. Naturally, they like to fix problems with code. To that end, developers would rather not admit there's a need for a DBA, which is why *some* hate Oracle.

    Oracle is complex and overboard for most applications. The MVCC alone makes me run screaming from SQL Server, but that's because I expect accurate results when I run SQL. Most can live without it because they're systems don't see that much action. For these systems, I see no reason to pay for Oracle either.

    The bottom line is that if a system doesn't require a full-time person to manage the database systems, then it probably doesn't need those features. Enter SQL Server: good, fast but not made for super high-end systems. It also seems like GUI folks prefer the "self-managing" type of software, which Oracle is truly not though they are beginning to claim it. Command-liners don't mind getting their hands a little dirtier and typically don't gripe so much about Oracle's complexity.

    Oh yeah, and the Oracle docs are actually good although they have come a LONG way in a short time.

  • Good points, I have been a Sybase/Microsoft user for years (sybase version 4.9.1 was my first...right b4 microsoft oem'd it). Recently I had the chance to work with Oracle pretty hard-core for about 6 months and I liked it. It took a few days of figuring what went where but the programming language (plsql) was overall much better an more intuitive the t-sql (it will be interesting to write c# with yukon but that's another train of thought).

    I also don't buy enjoy the flamewars b/w the developers Oracle versus Microsoft. It makes great print but they are both very nice systems. A good friend of mine once said that to sell a lot of software you need 1,000 people loving you and 10,000 people hating you. I am sure the debates help both sides sell a lot of software for Microsoft and Oracle. (consider pepsi versus coke).

    Frans, you make some good points but I don't buy the UDT argument. To me it appeared a poor mans implementation of an Object database. True. .Net does not handle UDT's so there benefit is truly limited and therefore they were difficult to explore....but does Java (i really don't know). If they are so nice why doesn't Oracle provide a means to serialize .Net objects in and out of the database (not a blob but an object)? That would be a killer application that would make me consider it a valuable service and possibly switching over to Oracle....

    Per the comment made by "re" about Microsoft not being good for super high-end systems. I don't by that either. There are too many numbers out there that state otherwise. The MVCC debate is an interesting one though and one I will read up on.

    -Mathew Nolton

  • I recently had a deep dive into the MS SQL Yukon pre-beta. This can reabilitate it a little:

    1. It supports schemas independent of users; users can be granted various schema access

    2. It supports snapshot MVCC, and a little better than Oracle does - while Oracle deals with versions on page level, MS SQL works with individual records. This means less data being copied when a record is being split

    3. It supports synonyms

    4. It supports UDTs. In the complete sense of 4GL languages - fields, methods and properties. It also allows, or course, CLR functions (scalar and table-valued), custom aggregate functions, stored procedures and triggers (both on DML and DDL statements). The promised optimizations in processing all those CLR stuff inside SQL queries are not ready yet, but I was looking to the pre-beta only, so I guess they will make it before the final MS SQL Server 9.0 is released. Thanks.

  • Sinclair, thanks for the update! It really looks promissing!

  • "Oracle 9i also showed me a severe flaw in .NET which can only be solved by Yukon which is a year away" - What is this flaw?

  • User defined types can't be loaded into datasets/tables. Oracle development focusses a lot on these custom types, but you can't use them in .NET

  • User defined types - is this like the interbase \ firebird Domains? Arn't these User defined types based on underlying types like strings, intergers ex.?

    I guess it would be nice to know what these types are in .Net so that it could do the validation of the value according to the Data Type before posting it to the Database?

    Frans wrote: "which can only be solved by Yukon which is a year away" - How would Yukon fix this problem - Is it going to support User defined types?

  • User defined types are classes like you'd use in .NET, only you can use these to define a type in a table.

    Yukon will support User defined types.

Comments have been disabled for this content.