Oracle Vs SQLServer

I couldn't care less about those multi TB databases and whose dad has larger one, but I've just came accross this article on oracle web site that touches the topic discused here lately: http://www.oracle.com/features/insid...nvanie_04.html

Greetings all,
I worked in both Oracle and SQL server 7.0. I have seen oracle from 6.0 till 8.1.6 while I saw SQL Server just for a year. In one word, SQL Server is the worst database I have seen. In my learnings, I have found SQLServer to have many undesirable critical architecture. The first thing I do to evaluate a database is understanding the way it deals with a transaction, which anyone would agree, is the spinal cord of a database. SQL Server fails to address this issue hence the classical problem of readers waiting for writers to finish. Before, the SQL Server people start saying NOLOCK, use of NOLOCK in select would enable you to read what the writers are writing and not read the last committed state. So there is no commit unless you explicitly want to have but at the cost of locking the readers waiting to read or letting them read what you are unsure of. There is no business world transaction like this. This has a huge impact on your application design. Its a failure from the start. Then there are a number of such architectural stuff that makes SQLServer a really screwed up DB. I have never called SQLServer support or visit SQLServer forums, 'cos there is a universal answer to most problems. Add 'more memory, more disks, more processors'. Now why is it popular, the reason is that the decision makers go by immediate financial figures. 'Add more memory blah blah' is still cheaper than Oracle and by the time things explode one could always spend a few million dollars in re-engineering efforts. So there is no reason it shouldn't be popular either. Even AT&T's DW databases are in SQL Server approx 500GB. (I do get free stuffs from AT&T so I assume their dw works somehow .. or maybe not).
Oracle on the other hand is designed to be less OS dependent. But they all rely on random access disks which are optimised for OLTP. Since in DW world it is common to access huge chunks of data, the disks are not optmised, I need a white board to explain this. So even for Oracle scaleability is as far as the OS and disks can take. So a few terrabytes of data would cost a few million dollars in EMC storage solution alone when NCR teradata solved this problem and their storage solutions cost far less than EMC storage solutions. I am not aware if EMC is coming up with anything like teradata. So NCR scales up to anywhere, 100TB or even more. I don't have experience with NCR but I have heard good things abt them. In my experience in database design, everything works. it takes a long time to realize poor design and then its expensive. My cousin is a Most Valued Professional for SQL Server and has co-authored SQL Server Unleashed for a few chapters and he claims SQL2K to scale a terrabyte. Well he doesn't like it when I call him. Recently I made some decisions to continue being a DBA and entered into a managereal position in a 3 terrabyte DW team. Still this forum is in my bookmark and enjoy and learn from the threads. BTW, someone mentioned abt SQL Server on Unix. That would never happen 'cos they (MS) think win2K is better than Unix .. thats another story...

Waaw!!!
http://www.plauder-smilies.de/person/bandit.gifing with Oracle Pros. :D


Sam


[Edited by sambavan on 06-19-2001 at 11:45 AM]

Since MSSQLSERVER is not available on UNIX. I don't really understand how 24/7 machines will run on WINDOWS machines. As for no reason we needed to reboot the machine. Where as UNIX machine we are running without rebooting for more than 2 years.

And how to convince CTO, tell him whether he can afford rebooting the server when his busniess is running and let him make the decision.

may be 24x7 Windows servers are on Cluster so they can reboot one per day lol

Should one recommend a RDBMS based on the DBA's Salary
or for the Technology with which it is made up off.

Bye,
gopi





[Edited by sambavan on 06-19-2001 at 11:47 AM]

No wonder you are affended. Be happy with your SQL Server DBA

Dragon

[Edited by sambavan on 06-19-2001 at 11:48 AM]

Folks,

I have gone ahead and commented some of the offending remarks directed towards individuals. Please, make your arguments towards products, subjects and not towards individuals ...

Hope you can u.stand. I've only commented those remarks, and now would leave it to the individuals to remove it through editing. If any of you have any questions or remarks, I'ld be happy to answer to...

Keep going ...
Sam

Quote:

---

Originally posted by sambavan
Folks,

I have gone ahead and commented some of the offending remarks directed towards individuals. Please, make your arguments towards products, subjects and not towards individuals ...

---

sambavan: the "PC" police...

http://www.plauder-smilies.de/person/pfiade.gif

Sam

Hi Friends..

Didn't realise my question would spark so much controversy.

I have to admit that I have been involved with SQLServer a little lately due to one of my projects and have noticed how 'easy?' it is to operate, purely because it is a GUI - but you cannot be as anlytical with SQLServer as you can with Oracle and I have only been looking into it for the past few days.

From what I have been reading lately and although I am 100% Pro Oracle - I have decided to look into SQLServer a little more as a contingency - especially as I have around 35-40 working years in me and technology waits for no-one.

Thanks for your input into this discussion.. I put the sqlserver guy in his place for now but for how long, only time will tell.................

Cheers

Suresh

Suresh,

I am promoting neither SQL Server nor Oracle. As a Tech Guy I am pointing out the differences between SQL Server and Oracle. They are:
1 Dirty Reads
SQL Server supports dirty reads. It means data changed by first transaction can be seen in the 2nd transaction even though the first transaction has not committed or rolled back the entire transaction. Example in SQL server:
In the Query Analyzer, enter the first transaction:
Begin transaction
Update EMP set deptno = 60 where deptno = 10
Now open another Query Analyzer for the 2nd transaction:
Select * from EMP
If you try to run 2nd SQL statement, then it won’t be executed because the previous transaction has not yet committed or rolled back. SQL server will wait till the completion of 1st transaction because of the isolation level is set to read committed.

Now, add (NOLOCK) to the Select statement.
Select * from EMP (NOLOCK)
This statement is immediately executed and the result set would display the updated rows from the first transaction. This is called dirty read in SQL Server.
Where as Oracle did not support dirty reads. For the 2nd transaction Oracle uses pre-images of the data being modified by first transaction from the rollback segments.
Bank transaction needs dirty reads that are possible in SQL Server but not in Oracle.
2 Isolation Level
SQL Server is designed for higher isolation level.
The level at which a transaction is prepared to accept inconsistent data is termed the isolation level. A lower isolation level increases concurrency but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can negatively affect concurrency. Oracle is just opposite of SQL Server.


3 Partitioning Table.
Table level Partition is not supported in SQL Server 7. If you have a big table (say 1 B rows), then you have to manually create small tables (say 10 tables with 10M rows), and use a view that has UNION of all small tables to access the full table. This is not the case in Oracle. Oracle supports Range, Hash and composite partitioning.
4 Image data (BLOB, CLOB etc )
Maximum size is SQL Server 2 GB
Maximum size is Oracle 4 GB
5 Columns per Index
In SQL Server 16
In Oracle 32

6 Outer Join
SQL server supports full outer joins (left and right) where as Oracle supports one way outer join only. More over it works just opposite in both RDBMS. For example, in SQL Server the left outer joins statement looks like:
Select * from emp a, dept b where a.deptno *= b.deptno
If you want the same result set in Oracle, you need to write as given below:
Select * from emp a, dept b where a.deptno = b.deptno (+)

7 Set Operator DIVIDEBY (DIVISION)
Both RDBMS do not support DIVISION set operator. I have been waiting for this facility since 1985. I do not know why it is not supported in Oracle as well as in SQL Server.

8 Platform availability
SQL Server is available only on NT / W2K OS where as Oracle is available in almost all platforms.

9 Administration
It is easy to administer SQL server because of its size and its GUI tool. Oracle needs experienced DBA to administer a very large database.

10 HOT BACKUP
Both RDBMS support hot backup.

11 Replication
It is supported in both RDBMS.

12 SEQUENCE
Sequence is defined at the table level in SQL Server, where as it is a separate object in Oracle.

13 24x7 Availability
Both RDBMS can be run 24x7 as long as the hardware is good. I had seen many big Oracle databases crashed because of the poor hardware setup. At the same time I had seen medium sized SQL Server databases running continuously (24 x 7) on a very good Compaq and IBM servers.
14 Rollback Segment
No more Snapshot too old error message. Oracle finally is going to get rid of this concept in 9i. Has any one tested? In 9I, only a tablespace would be designated for rollback area. No need of creating Rollback segments. There is NO Rollback Segment concept in SQL Server. It uses LOG file for its roll back activity.

I have pointed out the tech details of both RDBMS. Okay. Which one is superior? I leave the answer to DBAs. If your database is going to be small, and only few concurrent users use the database, and you do not want to spend more money, then go with SQL Server. On the other hand, if your database is going to be big, and you have a large user population, then choose Oracle. If you want to build a Data Warehouse of size over 3 TB, then go with Teradata.

PS: I am currently working on a conversion project from SQL Server to Oracle.

[Edited by tamilselvan on 06-20-2001 at 12:54 PM]

Excellent, "non-partisan" comparison!

That was one good comparison. http://www.dbasupport.com/forums/


I'm awaiting for this thread become stable, such that I can go ahead and document it.

Keep going ...
Sam

In the early days of SQL Server they had page level locks instead of record-level locks. When you went to update a record, it locked all the other records on that same "page" (similar to Oracle's db block). Does anybody know if this is still true?

It has been changed now. SQL Server 7 supports row level lock as well as page lock and table lock.

That's a indepth reply Tamil.

Cheers.

As tamilselvan wrote :
14 Rollback Segment

No more Snapshot too old error message. Oracle finally is going to get rid of this concept in 9i. Has any one tested? In 9I, only a tablespace would be designated for rollback area. No need of creating Rollback segments. There is NO Rollback Segment concept in SQL Server. It uses LOG file for its roll back activity.

In 9i you can use SMU ( System Managed Undo ), which uses an undo-tablespace instead of RBS.

But I think there is a hidden danger.
If you have a transaction that generates lots of RBS-data, only the transactions that use that RBS will maybe fail if the RBS becomes to big ( no more space in TS, max_extents reached... )
If we use 9i SMU, will it not be possible that 1 transaction fills up the entire UNDO-TS, and so blocking the whole db ?
This could also happen when using undo_retention= for using flashback query's.

I also have the feeling that lot's of my fellow dba's are a bit sceptic about SMU. They don't trust it quiete well.

Anyone an idea about this ?

Regards
Gert

PS if more suitable, a moderator can make a new thread of this reply. ( but I put it here because it's a reply to an answer in this thread. )