Oracle and SQL Server Index Comparison

This post has a lot of the support code and data for my Oak Table Talk that I’ll be giving at IOUG Collaborate 2017 in Las Vegas on April 5th, 2017.  

One of the Iceland 2017 SQL Saturday sessions got me thinking about indexing and how similar and different it all is in Oracle vs. SQL Server.  There was some really fun, (well, at least what I call fun…) test cases built out and referenced by Paul Randal.  After looking through some of it, I decided it might be interesting to try to replicate it to Oracle, (as close as possible) and compare how the two database platforms deal with index storage and specifically- SQL Server’s Fill Factor vs. Oracle PctIncrease index percentage filled.

B-tree indexing is the cornerstone of physically optimizing searches on data.  No consensus exists on what the “B” stands for, (some think its from Bayer, for one of the main gentlemen who did the research and many more believe it’s for Boeing, for the Research Center the research was done at.)

The choice in how the data is organized, leafs and such are pretty standard, but database platforms have created some unique indexing that enhances queries on RDBMS vs. just having heap tables.

Using Oracle and SQL Server as our choice for a comparison today, there are a few translations I need for readers of this blog:

Oracle SQL Server Description
Index Organized Table, (IOT) Clustered Index physical index storing data in their key values. In SQL Server, there can be only one Clustered index per table.
Pctfree of block FillFactor of page Percent of storage that is allowed filled. There are different times when this is used for each platform.
Sequence TOP Ability to populate data with a sequential number
dbms_random.string Replicate Ability to populate data with string values
block page unit of storage
Automatic Workload Repository, (AWR) Dynamic Management Views, (DMV) Performance data collection

Now that we have that out of the way, you can use this trusty, little graph for common terms that require a “translation” from one database platform to the other.

The next thing to remember is that PCTFree and FillFactor aren’t adhered to at all times.  Appending a row to an index is different than updating a row in an index and each platform has it’s own set of criteria to decide if it follows the rule of percentage of a block or page to fill or not.

The Test

The steps of this test:

  1. Create a table with three columns and two indexes-  SQL Server having its trusted clustered index.
  2. Populate data of different sizes to each of the tables.
  3. check the storage of our index “health”
  4. Remove data
  5. Repeat step 2 and also remove data
  6. Check the storage again to see how it has changed-  page splits in SQL Server, leaf block splits in Oracle

Goal is:

  1. Inspect the differences and similarities of indexing in both platforms
  2. The pros and cons of how index data is stored and used in both platforms

Oracle Code for Creation of Objects and Support

  • Table and PK with constraint
  • Alter index statement to decrease pctfree
  • Sequence to populate c1 column
  • Trigger to do this in simple way
  • Rinse, repeat, more “shampoo”, and do it all again… <br />
</li></ul></div></div>

    	  	<div class=