[This article series was first published in www.sswug.org in November-December 2009. I will publish the next part of this article next week. Hope you enjoy reading this article as much as my other readers did!]
Both Oracle and SQL are relational database systems (RDBMS). The data is stored in form of tables and columns. Both SQL and Oracle include programming languages, facilities and utilities that support enterprise level applications like ERP and data warehousing. So, both these applications can be described as a set of processes that run on an operating system. The processes define how the data is stored and how it is accessed. ODBC is supported in both.
Having said that, let us now examine the differences:
Multiplatform vs Single platform
Oracle is multiplatform; SQL server works only on Windows. This limits the use of SQL in enterprise solutions. Oracle can be used with Windows, all flavors of UNIX and with vendor machines like IBM, Sun, Digital, HP, Sequent and so on.
The advantage of using SQL server is that SQL requires only a specific operating system that supports multithreading and no special hardware. Oracle requires special hardware configurations. On the other hand Oracle supports unlimited number of user connections. SQL Server has a limitation to the number of user connections that are possible.
The language of data access
The data access in Oracle and SQL is done using a Structured Query language or SQL. SQL Standard was created to provide a means for coders to access and manipulate databases. It is an ANSI or American National Standards Institute standard. SQL allows coders to execute queries against a database; retrieve, insert, update and delete data from a database; create new databases and tables; create stored procedures, views and set permissions on tables, procedures and views.
While the SQL of Oracle and SQL Server implement the ANSI standard requirements, they are really very different versions of the SQL language.
SQL for SQL server uses a number of supportive tools (eg.SQL Plus) to build its applications. Basically SQL for SQL Server is composed of two components–DML and DDL. While DML is a data manipulation language DDL is a data definition language. DML includes commands like SELECT, UPDATE, INSERT, MERGE and DELETE. DDL commands are CREATE TALE, ALTER VIEW, CREATE VIEW or REPLACE PROCEDURE.
PL/SQL for Oracle is a procedural language that is an extension of SQL. SQL can be embedded in PL/SQL. It is used to create applications. It is a fourth generation programming language and has software engineering features such as data encapsulation, overloading, collection types, exceptions and information hiding. Rapid prototyping and development through tight integration of SQL with the Oracle database is a feature of this language.
One advantage of SQL over Oracle that is often touted is that SQL is ANSI-SQL ’92 compliant. This makes it very easy to convert the SQL database into another ANSI compliant database (at least theoretically). Oracle is more proprietary and the conversion is not possible.
Oracle uses multiprocessors; SQL uses multithreading.
Oracle uses multiprocessing to support more than one process simultaneously and this enables a number of programs run concurrently. Different users with different permissions may run different processes simultaneously. This is possible because the application is organized across multiple OS level processes. The processes are insulated from each other by the OS an error in one process will not impact the other process that are running concurrently.
SQL uses multi-threading and creates processes that have multiple threads running simultaneously. Each thread is a stream of instructions in the process with instruction pointers, stack of register and stack memory specific to the thread. The address space is also process specific or common to all threads within a process. This makes the data heap easily accessible. The multithreading process is more light weight than Multi processing. There are fewer contexts per thread than per process. Space sharing by the threads also makes for less work. Therefore costs are also lower.
However on the flip side, multithreading is more complex. The coder has to take care that the code is reentrant and thread safe. Often multi-version consistency is absent and “writers block readers and readers block writers” to make sure data integrity is maintained. This results in delays and waits in a heavy OLTP environment. SQL also will escalate row locks to page level locks if too many rows are locked on a page and this may even involve rows that are not being updated. So, long uncommitted transactions in SQL Server can halt all activity on the database and space allocated to committed transactions is not released till the recycling of the transaction log is complete.
In Oracle, this does not happen. Oracle dynamically re-creates a read consistent image for a reader of any requested data whose changes have not yet been committed. Uncommitted transactions will only halt the transaction itself when it runs out of rollback space.
In other words, the trade off is between speed and efficiency versus code complexity.
A natural corollary is that SQL is highly rated for Cost and performance.Oracle is priced higher.
Differences in Performance and Tuning
SQL server does not offer much control over sorting or memory allocation and Cache allocation unlike Oracle. The sort area size and cache area size can be set by the DBA in Oracle whereas the sort area size and cache area size is predefined in SQL Server.
The DBA also does not have any facility to prevent fragmentation in SQL Server. All pages are set to 8K and all extents are set to 8 pages. Larger extents for contiguous space cannot be specified for large objects. In Oracle this is fully configurable.
Range partitioning of large tables and indexes are not possible in SQL Server. In Oracle a large table can be seamlessly partitioned at the database level in range partitions. The performance and maintenance benefits derived from this are tremendous.
Log miner facility is unknown in SQL Server. Oracle 8i and 9i have a Log Miner inbuilt. This facility enables inspection of archived redo logs. However, in SQL server this facility can be integrated with third party tools.
SQL Server requires fully qualifying the name of an object to achieve 7-10% performance gains. Oracle too has some gains if the names are fully qualified, but the gain would be as small as 0.01% as Oracle uses its internal dictionary to resolve names.
Missing object types in SQL Server
SQL Server has
a. No public or private synonyms; no independent sequences
b. No collection of procedures and functions.
c. There is no such thing as independent sequence objects.
d. There are no packages; i.e., collections of procedures and functions.
e. No “before” event triggers (only “after” event triggers) and no row triggers (only statement)
To summarize:
Oracle is multiplatform and SQL is single platform application
Oracle uses SQL Plus and SQL uses SQL
Oracle is a multiprocessor application and SQL is a multithreading application
Performances tuning in Oracle and SQL server are different
There are several object types that are missing in SQL Server.
Stay tuned in for the next part of the review!