Database

Clustering

MSSQL provides solid high availability (HA) failover clustering support. However, MSSQL performance, in share nothing clustering is very limited, its administration and design is very complex. All clustering views should be redesigned and rebuild in order to scale the cluster out.

We discussed in previous paragraph the major MSSQL limitations in Clustering and High availability and also how MSSQL server share nothing and view based cluster works.

Oracle 10g adds RAC/Grid based clustering that can be easily scaled out even in standard edition. This is one of the notable advantages of Oracle 10g compared with MSSQL 2005.

Oracle provides a new, built-in software storage management solution called ASM (Automatic Storage Management) that assists with RAC/Grid based cluster deployment. In addition, Oracle provides proprietary and free (!) cluster file system on Linux and Windows platforms that helps in avoiding the use of raw devices.

©2004 WisdomForce Technologies, Inc. All rights reserved. 16

Tuning

Function based indexes via indexed computed column

Oracle provides an option to create an index on a function applied to a specific column. MSSQL provides with ability to create a so-called "computed column" and the ability to create an index on this column. These features are basically equivalent. Yukon introduces the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. Marking a computed column as PERSISTED allows creating an index on a computed column that is deterministic, but not guaranteed to be precise.

MSSQL 2005 Indexed View vs. Oracle Materialized Views

Starting from MSSQL 2000 and from Oracle 8i, both databases started to provide Materialized Views. Those views help in avoiding the expensive heavy join of multiple tables, in particular in Data Warehouse environments.