January 7, 2008 at 10:19 pm
I'm an sql guy too but there r some sites inspite of all this features u all pointed says oracle is a clear winner.......try doing a google.....
Opnions r like a**holes....every1 has one and think others stink the most....
January 7, 2008 at 10:22 pm
- 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.
Ha, fancy giving that a try as an article? I have to work with SQL and Oracle, and Oracle is definitely the red-headed stepchild here (for several reasons). I'd love to see something that would help me use Oracle to it's strengths rather than a conversion of SQL code - even at a very high level to give me areas to investigate further.
January 7, 2008 at 10:25 pm
In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.
In SQL Server, all pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.
In SQL Server, no range partitioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.
There is no partitioning in SQL Server.
There are no bitmap indexes in SQL Server.
There are no reverse key indexes in SQL Server.
There are no function-based indexes in SQL Server.
There is no star query optimization in SQL Server.
January 8, 2008 at 6:25 am
Jerome,
Look for "partitioned tables [SQL Server]" in BOL 2005!
January 8, 2008 at 8:13 am
Jerome (1/7/2008)
In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.In SQL Server, all pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.
In SQL Server, no range partitioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions. Such partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.
There is no partitioning in SQL Server.
There are no bitmap indexes in SQL Server.
There are no reverse key indexes in SQL Server.
There are no function-based indexes in SQL Server.
There is no star query optimization in SQL Server.
For the first two items above these things go to the very heart of my comments about applications being so much easier to develop and USUALLY running just fine on SQL Server even without ANY DBA inputs. Definitely agree that a competent DBA COULD make things go faster at the high end with more internal 'features' exposed to him.
For ranging/partitioning see post just before mine. SQL has had this for years (even in 2000, albeit VERY weakly so).
I addressed bitmap indexes in a prior post.
Not sure what a reverse key index is.
I could see the need for function-based indexes, but isn't this equivalent to creating a computed column and putting an index on that - another feature that has been possible for years.
Star join optimizations ARE available in 2005 and are improved in 2008. See here: http://blogs.msdn.com/sqlqueryprocessing/archive/2007/04/09/how-to-check-whether-the-final-query-plan-is-optimized-for-star-join.aspx
Note that many of the statements above are the main reason that comparative analysis is so difficult. It is REALLY REALLY unusual to find a TRUE EXPERT at both systems when each are as complex and feature-packed as SQL Server and Oracle. You will often find incorrect statements about the opposite product thrown out by the other side's guru simply because he/she isn't aware - or goes on no-longer-true knowledge.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 8, 2008 at 8:22 am
TheSQLGuru (1/8/2008)
Jerome (1/7/2008)
You will often find incorrect statements about the opposite product thrown out by the other side's guru simply because he/she isn't aware - or goes on no-longer-true knowledge.
Good segway into the Myths article. http://www.sqlservercentral.com/articles/Editorial/61854/
January 8, 2008 at 8:31 am
It is REALLY REALLY unusual to find a TRUE EXPERT at both systems when each are as complex and feature-packed as SQL Server and Oracle.
Generally true. You pick your area of expertise and then work with it. Same goes for programming languages, etc.
However, I will state that I am the unusual case (true expert at both).
January 8, 2008 at 8:37 am
You'll also love the fact that "INT" and all the Numeric data types take up the same number of storage bytes no matter how big or small you define them in Oracle.
Definately NOT true. Oracle stores two digits per byte (plus an overhead byte or two for sign, etc.) in a varying storage model. Equivalent to the new Vardecimal in SQL Server 2005 SP2.
Although the declaration and metadata for the column may show a large amount, the physical storage taken is not that value.
You can constrain the sizes of your integers, etc to the number of digits that you desire.
January 8, 2008 at 8:46 pm
Some times we r jus obsessed with the one that we work and the only one we know and feel that this is the best not knowing what the other offers and stuff. I work on sql and I don’t know what oracle has to offer, so I feel sql is the best, but on day to day observations I find that oracle has it's own kinda reputation and market. Most of the banks and big organizations use oracle and when asked y, they say it is more reliable and more secure.
January 8, 2008 at 8:59 pm
I even read in one of the sites making a comparison of the sql and oracle servers in the following way.
Take for example, sql is like Honda cars and oracle is like BMW cars. We all know Honda makes good and reliable cars and we also know that BMWs are better than Hondas any day. Hondas r cheaper but the good things don't come cheap do they?
January 8, 2008 at 9:07 pm
I think it is interesting how when push comes to shove you can sell a SQL Server application/solution to an Oracle shop, but there is no way you could sell an Oracle application/solution to a SQL Server shop. We just stopped supporting Oracle for our application and moved our 5 customers using Oracle over to SQL Server. This is the only SQL Server in their shop now. With 500+ customers on SQL Server it was not cost effective to continue supporting Oracle for 5 customers and none in the pipeline.
January 8, 2008 at 9:57 pm
JohnG (1/8/2008)
Definately NOT true. Oracle stores two digits per byte (plus an overhead byte or two for sign, etc.) in a varying storage model. Equivalent to the new Vardecimal in SQL Server 2005 SP2.
You don't happen to have an Oracle URL for that one, do you? Whether right or wrong, the URL would be a good one to jam in my DBA's nose if he's wrong 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2008 at 5:23 am
January 9, 2008 at 6:56 am
Tony Mungor (1/8/2008)
I think it is interesting how when push comes to shove you can sell a SQL Server application/solution to an Oracle shop, but there is no way you could sell an Oracle application/solution to a SQL Server shop. We just stopped supporting Oracle for our application and moved our 5 customers using Oracle over to SQL Server. This is the only SQL Server in their shop now. With 500+ customers on SQL Server it was not cost effective to continue supporting Oracle for 5 customers and none in the pipeline.
Yes, that is a common business decision based upon ROI. In much the same way that not all software is available for the Apple Mac.
A [substantial] number of years ago, the software product that I was working on was offered on IBM's DB2 (mainframe) and Oracle (multiple platforms including SUN Unix, VAX VMS and the IBM mainframe). The other players at the time were Ingres, Sybase, and Informix. SQL Server wasn't even around.
We had about two customers (of our other products) that wanted an Ingres version of the product. We couldn't justify the porting costs (i.e., ROI) to do it.
January 9, 2008 at 7:21 am
Jeff Moden (1/8/2008)
JohnG (1/8/2008)
Definately NOT true. Oracle stores two digits per byte (plus an overhead byte or two for sign, etc.) in a varying storage model. Equivalent to the new Vardecimal in SQL Server 2005 SP2.You don't happen to have an Oracle URL for that one, do you? Whether right or wrong, the URL would be a good one to jam in my DBA's nose if he's wrong 😉
Without the manual you can always the use equiv of the SQL Server DATALENGTH function to see the number of bytes occupied by any column.
From the Oracle Database Concepts manual (10g Release 2 (10.2), part number B14220-01) under NUMBER Datatype on page 26-7:
Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.
Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a given value, can be calculated using the following formula:
ROUND((length(p)+s)/2))+1
where s equals zero if the number is positive, and s equals 1 if the number is negative.
Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require 1 byte; positive infinity requires 2 bytes.
http://www.jlcomp.demon.co.uk/faq/num_size.html
http://www.kevinloney.com/free/howlong.htm
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply