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:

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… 🙂

Yes, I could have just used RowNum, but I was trying to kill a second bird, (testing task) with this one stone, so an trigger with a sequence it is… 🙂

CREATE TABLE ORA_INDEX_TST
(
C1 NUMBER NOT NULL
,C2 VARCHAR2(255)
,CREATEDATE TIMESTAMP
);

CREATE INDEX PK_INDEXPS ON ORA_INDEX_TST (C1);
ALTER TABLE ORA_INDEX_TST ADD CONSTRAINT OIT_PK PRIMARY KEY(C1) USING INDEX PK_INDEXPS;
CREATE UNIQUE INDEX IDX_INDEXPS ON ORA_INDEX_TST(C2);
ALTER INDEX PK_INDEXPS REBUILD PCTFREE 90 INITRANS 5;
ALTER INDEX IDX_INDEXPS REBUILD PCTFREE 90 INITRANS 5;
CREATE SEQUENCE C1_SEQ START WITH 1;

CREATE OR REPLACE TRIGGER C1_BIR 
BEFORE INSERT ON ORA_INDEX_TST 
FOR EACH ROW
BEGIN
  SELECT C1_SEQ.NEXTVAL
  INTO   :new.C1
  FROM   DUAL;
END;
/

We’ll need to manually insert just enough data to fill up one block, which is 8KB in this database, (and default.)

INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('A', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('B', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('C', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('D', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('E', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('F', 200), SYSDATE);
INSERT INTO ORA_INDEX_TST (C2, CREATEDATE) 
VALUES (dbms_random.string('G', 200), SYSDATE);
COMMIT;

We’ll now verify that our data is inserted into one block:

SQL> ANALYZE INDEX PK_INDEXPS VALIDATE STRUCTURE;

SQL> SELECT LF_BLKS, LF_BLK_LEN, DEL_LF_ROWS,USED_SPACE, PCT_USED FROM INDEX_STATS where NAME='PK_INDEXPS';

   LF_BLKS LF_BLK_LEN DEL_LF_ROWS USED_SPACE   PCT_USED
---------- ---------- ----------- ---------- ----------
  1        7924       0           1491        19

Code for SQL Server Objects and Support.  Since I didn’t have the same secondary project request, this one will appear simpler:

• Table with PK with constraint
• alter index and change fill factor
• Second Table to use for data population

CREATE TABLE SQL_INDEX_TST (c1 INT NOT NULL, c2 CHAR (255), createdate datetime);
CREATE INDEX CL2_INDEX_TST ON SQL_INDEX_TST(C2);
GO

ALTER TABLE SQL_INDEX_TST 
ADD CONSTRAINT PK_CLINDX_TST PRIMARY KEY CLUSTERED (c1);
GO

First, in SQL Server, a page will hold around 8KB of data, so let’s test out our index storage:

INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (1, 'a');
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (2, 'a'); 
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (3, 'a'); 
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (4, 'a');
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (5, 'a'); 
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (6, 'a'); 
INSERT INTO SQL_INDEX_TST(c1,c2) VALUES (7, 'a'); 
GO

We now have officially “filled” the first page as much as possible and we should see this if we query the information schema:

SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name, i.name as index_name, leaf_allocation_count, nonleaf_allocation_count, fill_factor, type_desc
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.SQL_INDEX_TST'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;

 

Data Loads

Oracle:

SQL> Begin
For IDS in 1..1000000
Loop
INSERT INTO ORA_INDEX_TST (C2) 
VALUES (dbms_random.string('X', 200));
Commit;
End loop;
End; 
  /

10% PCT Free- Time Elapsed 2 minutes, 12 seconds

90% PCT Free- Time Elapsed 7 minutes, 3 seconds

I’ll have both the initial test data and the new 10000 rows I’ve added:

SQL> select count(*) from ora_index_tst;

  COUNT(*)
----------
     1000008

Let’s delete some of this data load to create deleted leaf blocks:

SQL> delete from ora_index_tst
2 where c2 like '%200%';

4179 rows deleted.

SQL> commit;
Commit complete.

Now let’s analyze and take a look at the stats again:

SELECT LF_BLKS, LF_BLK_LEN, DEL_LF_ROWS,USED_SPACE, PCT_USED FROM INDEX_STATS where NAME='PK_INDEXPS';

   LF_BLKS LF_BLK_LEN DEL_LF_ROWS USED_SPACE   PCT_USED

---------- ---------- ----------- ---------- ----------

     41227  7924       121 212596009       19

There’s a substantial difference in number of leaf blocks vs. when the pct_used is allowed to fill up:

   LF_BLKS LF_BLK_LEN DEL_LF_ROWS USED_SPACE   PCT_USED

---------- ---------- ----------- ---------- ----------

     2004  7996       531   15985741     100

Oracle wasn’t impacted by PCTFREE that much, but there was some impact. Rebuilds were required to clean up some wait, but it wasn’t a true “requirement”, just a preferences if consistent deletes, updates where data was different sized than original and poor storage choices.  The differences in performance weren’t that significant.

Now that we know we have deleted rows, let’s do the same on the SQL Server side:

SQL Server

declare @id int

select @id = 9 --already inserted 8 rows
while @id >= 0 and @id <= 1000000
begin
   insert into sql_index_tst (c1,c2) values(@id, 'DKUELKJ' + convert(varchar(7), @id))
   select @id = @id + 1
end

Default Fill Factor- Elapsed Time: 4 minutes, 43 seconds

10% Fill Factor- Elapsed time: 23 minutes, 18 seconds

Delete some rows to test similar to Oracle:

DELETE FROM SQL_INDEX_TST WHERE c2 LIKE ‘%200%’;

Now there are a few ways we can look at how the indexes were impacted.  We’ll first check for page splits, which as we’ve discussed, cause extra work to the transaction log and fragmentation in the index:

SELECT
OBJECT_SCHEMA_NAME(ios.object_id) + '.' + OBJECT_NAME(ios.object_id) as table_name
,i.name as index_name
,leaf_allocation_count
,nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.SQL_Index_tst'),NULL, NULL) ios
INNER JOIN sys.indexes i ON i.object_id = ios.object_id AND i.index_id = ios.index_id;

Next, we’ll look at the physical fragmentation of the index:

SELECT
OBJECT_SCHEMA_NAME(ips.object_id) + '.' + OBJECT_NAME(ips.object_id) as table_name
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.SQL_Index_tst')
GO

There’s significant fragmentation and it also impacted performance as we viewed above.

Index Maintenance

USE AS_test;  

GO

DBCC DBREINDEX ('SQL_INDEX_TST', CL2_INDEX_TST,100);
DBCC DBREINDEX ('SQL_INDEX_TST', PK_CLINDX_TST,100);
GO

We’ve now rebuilt our indexes and moved the fillfactor to 100%.  Queries using each index column in where clause improved over 20%.  Insert and updates increased to perform similarly to Oracle, unless….

Sorts on data for C1 column on a clustered index in SQL Server increased dramatically and out-performed Oracle’s PK. Only IOT tables could compete, but the use case was very small where it was beneficial.

And The Winner Is…

So who won out in my comparison at Oak Table World?  As we always hear from the DBA,

It Depends…

Some of the benefits of clustered indexes in SQL Server are superior to Oracle:

• Data is physically sorted in the clustered index
• Optimizer usage specific- clustered index seek
• Work best with sequential data, identity columns and order dates
• Option to randomize the writes on the index can deter from hot spots.

There are negatives that leave this debate still open for me:

• Vulnerable to fragmentation
• Inserts are added at end and introduce “Last Page Insert Latch Contention” wait event.
• Subject to hot spots, (see above…)
• Page Splits- hit performance hard, especially to the transaction log, (requires much more work than standard insert.)
• Fillfactor can be a hit or miss configuration for some systems.

There was a lot of other tests and queries I used than what is presented here, but this is the main focus of the test.  I need to thank those that have contributed to the deep index knowledge that offered me the research to then want to research on my own.  Shout out to Richard Foote, Mr. Oracle Index and Paul Randal and Jason Strate for the SQL Server expertise!