Copying indexes from Oracle to SQL Server

I’m working with a client that has access to an Oracle 10g database containing transactional data that they copy to their new SQL Server 2012 as a data warehouse. (We recently migrated the straight from SQL 2000 into SQL 2012, but that’s another story.) They already had scripts to copy the tables and data to a staging database, and then copy new records to their data warehouse. Queries were slow against this data, as no indexes were created on the target tables.

I wanted to copy the Oracle indexes to SQL Server, but found no simple way to do this, so I created a script to do so.

I used Toad for Data Analysts to connect to the Oracle database, which is a really fantastic program for working with Oracle.

After perusing online Oracle documentation (which is an adventure in itself), I determined that DBA_INDEXES and DBA_IND_COLUMNS have the data I needed.

The DBA_* tables roughly correspond to the sys.* tables in SQL server, these are the tables that hold the meta data for the Oracle database.

These tables hold more info than I wanted. Here’s a query you can run to see the index columns in your Oracle database. You’ll want replace OWNERNAME with the table owner in your environment.

Also note that I limit to 100 records in this example.

 SQL |  copy code |? 
--Example 1, show index columns for tables owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION,DESCEND
FROM DBA_IND_COLUMNS I
WHERE ROWNUM< 100 --just for display purposes, the Oracle equivalent of SELECT TOP 100 
--ignore internal indexes I don't want
AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'

Some things to note about this query:

  • Each index column has a DESCEND value which indicates ASC or DESC, determining the index sort. In my environment all columns were ‘ASC’ so I didn’t need logic to handle this, but added it anyway to the script for your use :)
  • The COLUMN_POSITION determines where the column is in the index. The script accounts for up to 16 indexed columns, so it needs 16 table aliases in Example 5.

To determine the largest value for COLUMN_POSITION, I ran this query:

 SQL |  copy code |? 
--Example 2, show maximum number of columns in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT TABLE_OWNER,MAX(COLUMN_POSITION) AS LARGEST_INDEX_COLUMN_COUNT
FROM DBA_IND_COLUMNS I
WHERE SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
AND I.TABLE_OWNER='OWNERNAME'
GROUP BY TABLE_OWNER
ORDER BY MAX(COLUMN_POSITION) DESC

In my case the largest column count for any index was 13, but I went ahead and scripted for a maximum of 16 columns, which is the maximum number of index columns in SQL Server.

I considered that this task is an ideal use case for a SQL PIVOT statement, but Oracle 10g lacks support for this very useful feature. So I needed to brute force it with 16 table aliases, one for each column of the index.

 SQL |  copy code |? 
--Example 3, show indexes and columns in order, in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT I.TABLE_OWNER
, IX.INDEX_TYPE
, I.TABLE_NAME
, I.INDEX_NAME
, I.COLUMN_NAME AS COL1
, I2.COLUMN_NAME AS COL2
, I3.COLUMN_NAME AS COL3
, I4.COLUMN_NAME AS COL4
, I5.COLUMN_NAME AS COL5
, I6.COLUMN_NAME AS COL6
, I7.COLUMN_NAME AS COL7
, I8.COLUMN_NAME AS COL8
, I9.COLUMN_NAME AS COL9
, I10.COLUMN_NAME AS COL10
, I11.COLUMN_NAME AS COL11
, I12.COLUMN_NAME AS COL12
, I13.COLUMN_NAME AS COL12
, I14.COLUMN_NAME AS COL14
, I15.COLUMN_NAME AS COL15
, I16.COLUMN_NAME AS COL16
, I.COLUMN_POSITION
--, DESCEND
--, I.*
FROM DBA_INDEXES IX
JOIN DBA_IND_COLUMNS I ON IX.INDEX_NAME = I.INDEX_NAME
--note the LEFT JOIN for all subsequent columns, which may not exist
LEFT JOIN DBA_IND_COLUMNS I2 ON I.INDEX_NAME = I2.INDEX_NAME AND I2.COLUMN_POSITION = 2
LEFT JOIN DBA_IND_COLUMNS I3 ON I.INDEX_NAME = I3.INDEX_NAME AND I3.COLUMN_POSITION = 3
LEFT JOIN DBA_IND_COLUMNS I4 ON I.INDEX_NAME = I4.INDEX_NAME AND I4.COLUMN_POSITION = 4
LEFT JOIN DBA_IND_COLUMNS I5 ON I.INDEX_NAME = I5.INDEX_NAME AND I5.COLUMN_POSITION = 5
LEFT JOIN DBA_IND_COLUMNS I6 ON I.INDEX_NAME = I6.INDEX_NAME AND I6.COLUMN_POSITION = 6
LEFT JOIN DBA_IND_COLUMNS I7 ON I.INDEX_NAME = I7.INDEX_NAME AND I7.COLUMN_POSITION = 7
LEFT JOIN DBA_IND_COLUMNS I8 ON I.INDEX_NAME = I8.INDEX_NAME AND I8.COLUMN_POSITION = 8
LEFT JOIN DBA_IND_COLUMNS I9 ON I.INDEX_NAME = I9.INDEX_NAME AND I9.COLUMN_POSITION = 9
LEFT JOIN DBA_IND_COLUMNS I10 ON I.INDEX_NAME = I10.INDEX_NAME AND I10.COLUMN_POSITION = 10
LEFT JOIN DBA_IND_COLUMNS I11 ON I.INDEX_NAME = I11.INDEX_NAME AND I11.COLUMN_POSITION = 11
LEFT JOIN DBA_IND_COLUMNS I12 ON I.INDEX_NAME = I12.INDEX_NAME AND I12.COLUMN_POSITION = 12
LEFT JOIN DBA_IND_COLUMNS I13 ON I.INDEX_NAME = I13.INDEX_NAME AND I13.COLUMN_POSITION = 13
LEFT JOIN DBA_IND_COLUMNS I14 ON I.INDEX_NAME = I14.INDEX_NAME AND I14.COLUMN_POSITION = 14
LEFT JOIN DBA_IND_COLUMNS I15 ON I.INDEX_NAME = I15.INDEX_NAME AND I15.COLUMN_POSITION = 15
LEFT JOIN DBA_IND_COLUMNS I16 ON I.INDEX_NAME = I16.INDEX_NAME AND I16.COLUMN_POSITION = 16
WHERE I.COLUMN_POSITION = 1
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'
--hide system indexes
AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
ORDER BY I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, I.COLUMN_POSITION;

I then switched over to SQL Server Management Studio, created a single column index and scripted it, so I’d have the template code I need to build indexes. This is done in SQL 2012, but is adaptable to any SQL version. You can do this from Object Explorer in SSMS by opening the tree under Databases, Your Database, Tables, Your Table, Indexes, Right-Click, New Index, Choose Non-Clustered Index.

 SQL |  copy code |? 
--Example 4, CREATE INDEX TEMPLATE
CREATE NONCLUSTERED INDEX [IX_MYTABLE] ON [dbo].[MYCOLUMN1],[MYCOLUMN2]--can add
(
[MYCOLUMN1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

You may want to modify the settings in the WITH clause. They are documented here.

So I took the results from Example 3 and modified them to generate the text of the statement in Example 4. Here’s what I came up with:

 SQL |  copy code |? 
--Example 5, Generate CREATE INDEX script for Oracle indexes in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT
'CREATE NONCLUSTERED INDEX '
|| I.INDEX_NAME -- You may want to prefix this with 'IX_', I decided to preserve the index names.
|| ' ON dbo.' --Assumes all target objects in the DBO schema
|| I.TABLE_NAME
|| '('
|| I.COLUMN_NAME || ' ' || I.DESCEND
|| DECODE(I2.COLUMN_NAME,NULL,'',',' || I2.COLUMN_NAME || ' ' || I2.DESCEND)
|| DECODE(I3.COLUMN_NAME,NULL,'',',' || I3.COLUMN_NAME || ' ' || I3.DESCEND)
|| DECODE(I4.COLUMN_NAME,NULL,'',',' || I4.COLUMN_NAME || ' ' || I4.DESCEND)
|| DECODE(I5.COLUMN_NAME,NULL,'',',' || I5.COLUMN_NAME || ' ' || I5.DESCEND)
|| DECODE(I6.COLUMN_NAME,NULL,'',',' || I6.COLUMN_NAME || ' ' || I6.DESCEND)
|| DECODE(I7.COLUMN_NAME,NULL,'',',' || I7.COLUMN_NAME || ' ' || I7.DESCEND)
|| DECODE(I8.COLUMN_NAME,NULL,'',',' || I8.COLUMN_NAME || ' ' || I8.DESCEND)
|| DECODE(I9.COLUMN_NAME,NULL,'',',' || I9.COLUMN_NAME || ' ' || I9.DESCEND)
|| DECODE(I10.COLUMN_NAME,NULL,'',',' || I10.COLUMN_NAME || ' ' || I10.DESCEND)
|| DECODE(I11.COLUMN_NAME,NULL,'',',' || I11.COLUMN_NAME || ' ' || I11.DESCEND)
|| DECODE(I12.COLUMN_NAME,NULL,'',',' || I12.COLUMN_NAME || ' ' || I12.DESCEND)
|| DECODE(I13.COLUMN_NAME,NULL,'',',' || I13.COLUMN_NAME || ' ' || I13.DESCEND)
|| DECODE(I14.COLUMN_NAME,NULL,'',',' || I14.COLUMN_NAME || ' ' || I14.DESCEND)
|| DECODE(I15.COLUMN_NAME,NULL,'',',' || I15.COLUMN_NAME || ' ' || I15.DESCEND)
|| DECODE(I16.COLUMN_NAME,NULL,'',',' || I16.COLUMN_NAME || ' ' || I16.DESCEND)
|| ') WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' -- Your values in the WITH clause may vary.
AS STATEMENT
FROM DBA_IND_COLUMNS I
LEFT JOIN DBA_IND_COLUMNS I2 ON I.INDEX_NAME = I2.INDEX_NAME AND I2.COLUMN_POSITION = 2
LEFT JOIN DBA_IND_COLUMNS I3 ON I.INDEX_NAME = I3.INDEX_NAME AND I3.COLUMN_POSITION = 3
LEFT JOIN DBA_IND_COLUMNS I4 ON I.INDEX_NAME = I4.INDEX_NAME AND I4.COLUMN_POSITION = 4
LEFT JOIN DBA_IND_COLUMNS I5 ON I.INDEX_NAME = I5.INDEX_NAME AND I5.COLUMN_POSITION = 5
LEFT JOIN DBA_IND_COLUMNS I6 ON I.INDEX_NAME = I6.INDEX_NAME AND I6.COLUMN_POSITION = 6
LEFT JOIN DBA_IND_COLUMNS I7 ON I.INDEX_NAME = I7.INDEX_NAME AND I7.COLUMN_POSITION = 7
LEFT JOIN DBA_IND_COLUMNS I8 ON I.INDEX_NAME = I8.INDEX_NAME AND I8.COLUMN_POSITION = 8
LEFT JOIN DBA_IND_COLUMNS I9 ON I.INDEX_NAME = I9.INDEX_NAME AND I9.COLUMN_POSITION = 9
LEFT JOIN DBA_IND_COLUMNS I10 ON I.INDEX_NAME = I10.INDEX_NAME AND I10.COLUMN_POSITION = 10
LEFT JOIN DBA_IND_COLUMNS I11 ON I.INDEX_NAME = I11.INDEX_NAME AND I11.COLUMN_POSITION = 11
LEFT JOIN DBA_IND_COLUMNS I12 ON I.INDEX_NAME = I12.INDEX_NAME AND I12.COLUMN_POSITION = 12
LEFT JOIN DBA_IND_COLUMNS I13 ON I.INDEX_NAME = I13.INDEX_NAME AND I13.COLUMN_POSITION = 13
LEFT JOIN DBA_IND_COLUMNS I14 ON I.INDEX_NAME = I14.INDEX_NAME AND I14.COLUMN_POSITION = 14
LEFT JOIN DBA_IND_COLUMNS I15 ON I.INDEX_NAME = I15.INDEX_NAME AND I15.COLUMN_POSITION = 15
LEFT JOIN DBA_IND_COLUMNS I16 ON I.INDEX_NAME = I16.INDEX_NAME AND I16.COLUMN_POSITION = 16
WHERE I.COLUMN_POSITION = 1
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'
--hide system indexes
AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
ORDER BY I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, STATEMENT

Some things to note about this statement:

  • ‘||’ is the Oracle equivalent of ‘+’ in SQL Server for concatenating string values
  • DECODE is a powerful Oracle function similar to IIF in MS Access, or the CASE statement in SQL Server. I use it to return the column name if it is NOT a null value, with a comma in the proper place.
  • In my environment I created a new filegroup to hold the indexes so I could manage their storage separately. If you do this, you’ll replace the [PRIMARY] keyword in the above statement with the name of your filegroup.

I used the above statement to generate over 400 CREATE INDEX statements, which I copied and pasted to a SQL script to run in my SQL Server.

I did a small amount of hand editing in Notepad++ to add a GO statement between each statement in case one failed, but amazingly the statement completed in under 30 minutes. The client reported that all the queries that used to take 10-20 minutes all ran in just a few seconds, so the indexes are working. I’ll check whether SQL Server recommends any missing indexes and create them in the near future.

And that’s how I copied indexes from Oracle 10g to SQL Server 2012.

I’ve neglected to mention that prior to this step, I also scripted the primary keys as clustered primary keys, and foreign keys as well. If there’s any interest I’ll share those in a future post.

Any questions, or better approaches? I would love to hear them.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>