January 6, 2008 at 11:13 am
OK, now that we have picked on Oracle let's point out a few of its good or unique features.
1. Spatial data is an interesting feature hardly anybody uses. Storing geographical data with a time dimension is cool. I'm sure it is critical for all of our weather system and many more system like that. How would we know how much snow ground cover exists so we can go skiing or snowmobiling.
2. Oracle's partitioning, parralelism, and bitmap indexes make very large tables and lookup indexes easy to managing. They require less disk space then the SQL Server indexes and stats.
3. Nobody but the Oracle DBA can get into your system and do anything. I know everyone thinks this is a limitation, but Oracle took this design path to keep the novice out of the system. This lowers the number of calls into their support centers.
4. Oracle's Enterprise Manager will show you how many blocks still need to be processed for a sessions current statement. When running long statements it is nice to see the estimated time of completion as well as an indication if things are hung or still processing. Basically you don't have to just wait forever or always kill the session and start over when you are developing/testing different approaches or managing very large tables.
5. Did I mention nobody else will really knows Oracle and you will be the Oracle god everyone has to come to. Oh, ya that part actually sucks because then you have no time to do anything really important.
6. Oracle's internal table compression is nice if your data is read only and you sort it accordingly.
January 6, 2008 at 2:31 pm
Oracle objects can be given Mixed Case names by using quotes when creating them e.g. "MyTableName". This seems to be frowned upon in the Oracle community - there are some references here:
http://forums.oracle.com/forums/thread.jspa?threadID=500298
The Oracle (tm) Users' Co-Operative FAQ
http://www.jlcomp.demon.co.uk/faq/access_problem.html
Schema Object Names and Qualifiers
http://www.itk.ilstu.edu/docs/oracle/server.101/b10759/sql_elements008.htm
Is Oracle case sensitive or not?
January 6, 2008 at 3:09 pm
I work in a mixed shop, one team on SQL Server and the other on Oracle, I am on the SQL side of the house. From all the moaning, support calls and general Harry Potter like mystery, that goes into Oracle, that I hear from the Oracle staff cubes I will stick with SQL Server.
Although the $ appears to be some what better on that side of the Oracle front, the quirks of the product and the black magic way of resolving issues makes me not want to cross train. Add to that Oracles pricing, even for dev and stage instances, and I think going forward SQL is the way to go.
That said, I would say that, given I am an MS man through and through.
Andrew
January 6, 2008 at 3:15 pm
"5. Just TRY to write a set based trigger in Oracle... just go ahead and try!" This can be done.
1. For the triggering table, create two temporary tables, one for the new rows and one for the old rows, using the ISO "create temporary table" syntax. For a triggering table of "foo", temporary tables named "foo_inserted" and "foo_deleted" can be used in a similar fashion to the T-SQL trigger tables.
2. In the trigger for each row, insert into the appropriate temporary table from the :new and :old variables.
3. In the after trigger, you can now use the set based SQL versus the temporary tables similarly to T-SQL.
SQL = Scarcely Qualifies as a Language
January 6, 2008 at 3:19 pm
Tony Mungor (1/6/2008)
OK, now that we have picked on Oracle let's point out a few of its good or unique features.1. Spatial data is an interesting feature hardly anybody uses. Storing geographical data with a time dimension is cool. I'm sure it is critical for all of our weather system and many more system like that. How would we know how much snow ground cover exists so we can go skiing or snowmobiling.
2. Oracle's partitioning, parralelism, and bitmap indexes make very large tables and lookup indexes easy to managing. They require less disk space then the SQL Server indexes and stats.
3. Nobody but the Oracle DBA can get into your system and do anything. I know everyone thinks this is a limitation, but Oracle took this design path to keep the novice out of the system. This lowers the number of calls into their support centers.
4. Oracle's Enterprise Manager will show you how many blocks still need to be processed for a sessions current statement. When running long statements it is nice to see the estimated time of completion as well as an indication if things are hung or still processing. Basically you don't have to just wait forever or always kill the session and start over when you are developing/testing different approaches or managing very large tables.
5. Did I mention nobody else will really knows Oracle and you will be the Oracle god everyone has to come to. Oh, ya that part actually sucks because then you have no time to do anything really important.
6. Oracle's internal table compression is nice if your data is read only and you sort it accordingly.
Let me be the first to redress the issues above from the SQL Server point of view.
1) Pretty much nobody using the spatial features is point enough about this one. Those that do need it however tend to use a specialized GIS system. I will also add that SQL 2008 is adding in some spatial features. More of a "me too" thing than a "lot-o-need-out-there" thing. Kinda like the CLR!! 🙂
2) I don't know jack about parallelism or partitioning features of oracle, but SQL Server does have these and has for many years. They just keep getting better too (as I am sure Oracle's does as well). Bitmap indexes - you are right there. I have wished for these for quite some time now for high-end warehousing needs.
4) I would like to see this feature added for SQL Server, and wouldn't be surprised to see it since a good bit of SQL 2005's admin and tuning information system (i.e. DMVs) came from the Oracle world so I have been told. SQL has made great strides in this area but still has a ways to go.
3&5) If all the devs in the world had to use Oracle we would see maybe 10% of the database-using applications in existence that we have now. Ease-of-use is HUGELY important for getting product to market in a timely and cost-effective manner. Sure, once you throw real amounts of data at most systems that were developed WITHOUT the use of a competent DBA it falls on it's _ss. But most systems never get to that point or are 'fixed' by simply throwing more relatively inexpensive hardware at it. And when that doesn't work expert tuners such as myself can quickly snap some performance into poorly built/maintained systems.
6) SQL 2008 will have internal compression. SQL 2005 sp2 has vardecimal compression which some systems are gaining good benefit from.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 6, 2008 at 3:45 pm
Carl Federl (1/6/2008)
"5. Just TRY to write a set based trigger in Oracle... just go ahead and try!" This can be done.1. For the triggering table, create two temporary tables, one for the new rows and one for the old rows, using the ISO "create temporary table" syntax. For a triggering table of "foo", temporary tables named "foo_inserted" and "foo_deleted" can be used in a similar fashion to the T-SQL trigger tables.
2. In the trigger for each row, insert into the appropriate temporary table from the :new and :old variables.
3. In the after trigger, you can now use the set based SQL versus the temporary tables similarly to T-SQL.
Carl, if I am understanding what you wrote then the solution is truly laughable from a performance (and coding) standpoint!! You have to create two tables and populate them both row-by-row (which I presume would incur logging overhead as well as write overhead as well as the obvious cursor overhead) just to use set-based queries?? Sheesh. Pretty steep price to pay to use set-based logic. Although I can actually think of cases where this might be faster than the alternative, which is really sad.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2008 at 6:04 am
"f I am understanding what you wrote then the solution is truly laughable from a performance (and coding) standpoint!! You have to create two tables and populate them both row-by-row (which I presume would incur logging overhead as well as write overhead as well as the obvious cursor overhead) just to use set-based queries?? Sheesh. Pretty steep price to pay to use set-based logic. Although I can actually think of cases where this might be faster than the alternative, which is really sad. "
Yes, the solution is cumbersome but it is the only method available under Oracle and much of the code can be automatically generated.
It is also the only work-around to the mutating table error that will occur if a trigger attempts to select from the table being updated.
An example is when attempting to implement a business rule such as "no employee may have a base salary greater than the base salary of their manager".
SQL = Scarcely Qualifies as a Language
January 7, 2008 at 6:12 am
No, no... it's not that... in SQL Server, you have a Deleted and Inserted table... in Oracle, they are not tables... they are essentially variables that contain 1 row... you have to write RBAR and then tell it "FOR EACH ROW"... it cannot be made set based, period. Yes, you can address all rows using "FOR EACH ROW", but, I repeat, it cannot be made 100% set based.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2008 at 6:21 am
Whatta discussion. Kind of like going on a Ford message board and asking what's better, a Taurus or a Camry? Makes me want to go on Metalink or OraFaq and post the virtues of SQL Server... just kidding.
January 7, 2008 at 6:24 am
Andrew Whettam (1/6/2008)
http://forums.oracle.com/forums/thread.jspa?threadID=500298
The Oracle (tm) Users' Co-Operative FAQ
http://www.jlcomp.demon.co.uk/faq/access_problem.html
Schema Object Names and Qualifiers
http://www.itk.ilstu.edu/docs/oracle/server.101/b10759/sql_elements008.htm
Is Oracle case sensitive or not?
Yes, yes... but, you missed my point... SQL Server does not have case sensitive names but will still name the object with any case used in the Create statement which is great for documentation and readability purposes... when you generate the code automatically using native or 3rd party tools, the case used originally is preserved without doing anything special.
To do the same thing in Oracle, you have to use quoted identifiers AND they also become case sensitive... and that's bad.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2008 at 6:26 am
Carl Federl (1/7/2008)
"f I am understanding what you wrote then the solution is truly laughable from a performance (and coding) standpoint!! You have to create two tables and populate them both row-by-row (which I presume would incur logging overhead as well as write overhead as well as the obvious cursor overhead) just to use set-based queries?? Sheesh. Pretty steep price to pay to use set-based logic. Although I can actually think of cases where this might be faster than the alternative, which is really sad. "Yes, the solution is cumbersome but it is the only method available under Oracle and much of the code can be automatically generated.
It is also the only work-around to the mutating table error that will occur if a trigger attempts to select from the table being updated.
An example is when attempting to implement a business rule such as "no employee may have a base salary greater than the base salary of their manager".
Heh... sorry Carl... didn't see this post... that's exactly what I was talking about and you're spot on!
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2008 at 7:55 am
OK, now we're onto a religious debate.
Having worked since 1984 with relational databases (see bio) -- initially with IBM's DB2 when it first came out (early 1980s) then working with Oracle (versions 5 through 10) and SQL Server (versions 6.5 through 2005) and often building applications that work on more than one all I can say is: they all get the job done. However you may need to temper your choice based upon your company, budget, skill set, etc.
As for feature/functionality differences (leaving all $$ out of the equations) I've found the following:
- PL/SQL is miles ahead of T-SQL and always has been. Having just built an application that will work on both Oracle and SQL Server I could write an entire paper on the benefits of this alone.
- Oracle's full-text support is also far superior to SQL Server. In particular, when the query contains a mix of full-text and regular predicates. Material here for another lengthy article.
- SQL language: SQL Server is always playing "catch-up" to Oracle.
- Scalability: Oracle's RAC clustering is supurb. I wonder when Microsoft will offer the same?
January 7, 2008 at 8:08 am
I'm not an Oracle guy, but isn't Oracle optimized for cursors? Isn't that it's strength when working through sets of data?
If so, then does it matter if you program that way if that's how the software is optimized?
January 7, 2008 at 9:21 am
Steve Jones - Editor (1/7/2008)
I'm not an Oracle guy, but isn't Oracle optimized for cursors? Isn't that it's strength when working through sets of data?If so, then does it matter if you program that way if that's how the software is optimized?
No, Oracle isn't optimized for cursors. When to use cursors or not is no different than SQL Server. You can properly do set-based operations which will run quite fast.
To wit: I worked on a financial application back in 1994 under Oracle 7 running on a 32-bit UNIX system. I once had a set-based update statement (the "query from hell") containing lots of "CASE" type logic in it with a 12 table join (containing 2 sets of self joins) against tables with 10s and 100s of millions of rows in them that ran in just a few seconds.
I think the confusion here is the fact that you have to use a "reference (REF) cursor", i.e, a "handle" when returning result sets back from a stored procedure. Vs. just returning a "fire hose" server-side result set to the caller. Note that SQL Server has implemented "ref" cursors.
The original debate is that (according to ANSI) Cursor control is supposed to be from the "client" (i.e., caller) and not from the server. E.g., in Oracle with a "ref" cursor, the client can close the cursor (result set) after consuming just a portion of it. With SQL Server, the client must consume the entire result set from a server side cursor.
Believe me, the "ref" cursor has been around since at least Oracle 8 and works just fine with ADO.NET.
January 7, 2008 at 9:30 am
SQL Server does not have case sensitive names but will still name the object with any case used in the Create statement which is great for documentation and readability purposes
The case sensitivity of the tables and names is governed by the collation of the database.
Most often, SQL Server databases are created as "case insensitive". This means that you can use all upper, all lower, pascal cased, camel cased, etc. names in your objects and code and you can mix them (i.e., Table is MY_TABLE, SELECT clause references mY_TaBLe) and it will work. However, if you take the same code and install it in a SQL Server database with a case-sensitive collation, the code will not work.
In Oracle, by default, all objects are converted to uppercase (if they are not quoted). So you can still code in mixed case if you want. Actually, it is very common (for readability) to code the PL/SQL with all reserved words in UPPERCASE and all table and column names in lowercase.
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply