A Comparative Study of Indexing using Oracle and MS-SQL Server for Relational Database Management Systems

Relational Database Management Systems (RDBMS) maintain a collection of huge data files to provide fast and efficient methods in order to access and modify data which is necessary (Martin et al., 1992). Therefore, RDBMS have supported indexing techniques in order to access the data efficiently in static and dynamic manners. The majority of commercial (RDBMS) Relational database management systems performance is relied on I/O operations rather than other computing resources. This is because the performance cost of I/O is expensive and there are other costs such as memory allocations and CPU consumption. The most important factor to consider is whether the I/O subsystem of a given (RDBMS) will support a reliable performance as time passes. This article describes a methodology for evaluating indexing techniques for relational databases. The methodology is based on a number of experiments to test a set of indexing techniques on two different platforms (Oracle and MS-SQL Server) with different data sizes (small, medium, and very large) over the same technical environment (Multiple processors, memory, and I/O devices). This factor is necessary to satisfy the real results on different platforms: Oracle and MS-SQL Server. To run the experiments, we have taken the following indexing techniques in Oracle: B-tree, Bitmap, Reversed, and organization index. In the meanwhile, we have taken the following indexing techniques in MS-SQL Server: B-tree, Clustered index, and unique non-clustered index and Primary Key Clustered index. The methodology includes the technical environment, platforms, table schema, table sizes, and a number of indexing techniques. We have also established a number of test scenarios to achieve the real results. The methodology procedure and flowchart include an ordered set of steps that have been taken to run the experiments in this article. The aim of the methodology contribution is to measure overall performance and behavior of indexing techniques that are performed against the same set of data: As a non-clustered index on a specified set of columns, and as a clustered index on the same set of columns. Note that, we have measured the performance of SELECT operation over Oracle 10g and MS SQL Server on data sizes (5000K).