(Un)biased views on Oracle vs SQL Server

Table Design

If there’s one fundamental data structure a relational database cannot live without, it is tree. Indexes that are used to fast-fetch a specific record are made using trees.

When Oracle creates a table, the table is organized as a heap — a sequential dump of records with no specific order but usually stored in the order as it is inserted.This is why Oracle documentation says order of records is not guaranteed in SELECT statements unless you have an ORDER BY clause for ordering by specific columns on the result set. Because of this flexibility, returning random set of records is fast.

If you intend to read a small portion of the heap and want to create optimized access paths based on value of a specific column, Oracle lets you create an index on that column. Index is implemented as a b-tree that points to the address of records on heap.

In SQL Server, a table with Primary Key (PK)is stored on an index by default, not on a heap. The whole table, not just an indexed column, lives on a b-tree. The records in index is saved in the order of Primary Key. This at first seems intuitive. Query plans involving sort-merge (or even nested loops) should be faster now. But I haven’t observed or read of any relative advantage over Oracle in terms of join performance.

Regardless this imposes a particular design pattern. I for one hate all kinds of imposition.

Oracle let’s you do what SQL Server imposes if you choose to specify so on your CREATE TABLE command. It’s called Index Organized Table (IOT) in Oracle. It’s up to the designer to choose if the whole table should sit on an index, or just the keys he wants to be indexed.

Application Isolation

Oracle has a novel method to isolate applications that access the same database — schema. You put database objects in separate schemas (under a given database). Objects can repeat across schemas. This helps achieve many things — application boundaries, test environment boundaries, access for groups of users to specific objects etc. SQL writing is clean and simple regardless of which schema the object belongs to. The DBA still has only one database to manage while architects and application developers are able to accomplish isolation.

Although SQL Server has the concept of schemas, it favors creating databases for isolation. The challenge is writing SQLs that can access objects across database. You are dependent on synonyms to do that, and synonyms are a loose end. There is also a maintenance hassle of managing multiple full-fledged databases.

Hash Joins

The terms “relational” and “normalization” in databases mean one dreaded thing to the developer: JOINs. Both Oracle and SQL Server commonly uses the same three strategies for joining tables: Nested Loops, Sort-Merge, Hash Joins. You can see which one is used for your SQL by looking at the Query Plan.

To summarize, Nested Loops take the first key from left leg, look that key up on the right leg for a match. Then take the second key from left leg, look that key up on the right leg for a match, and so on. Sort-Merge sorts both left and right leg before trying to match. Then it proceeds to quickly match without having to exhaustively look up the other leg. This is how it is done in both products.

Hash joins are different, and for the better in Oracle. Oracle hashes the key of one of the tables and creates an in-memory hash table. Usually this is the shortest of the tables to reduce memory footprint. Then as it picks up each key from the other leg, hashes that key on-the-fly and does an in-memory lookup with the hash table. This is a highly performant joining algorithm in Oracle. In SQL Server however this is the least performing of all the three joining algorithms. SQL Server hashes both legs at once, and then tries to find a match. That leaves us with just two well performing algorithms.

Oracle has quite a few storied database implementations — Amazon retail site ran on Oracle; Salesforce, a multi-tenant CRM Cloud, runs on Oracle. Back when IBM sold server hardware they used to benchmark servers for database performance on Oracle database. Oracle of course ran trash ads that said “IBM benchmarks their own servers on Oracle not DB2".