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

27 Comments

• 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".
  
  
  
  Cheers.
  
  

• 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";
  
  }
  
  else
  
  {
  
  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.