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:
|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 steps of this test: