Take the 2-minute tour ×

Our application supports SQL Server and Oracle as database backend.

During investigation stuff database tuning advisor on SQL Server suggested to create an index on table which resulted significant improvement of query execution time against this table. I'd like to try to create the same index on oracle but a problem with understanding how to interpret a part of index creation statement.

So on SQL Server the index creation statement is:

CREATE NONCLUSTERED INDEX [_dta_index_K2_4_10] ON [products] 
    [ID] ASC

My question is how would look on oracle the WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] part of the index creation statement?

share|improve this question
If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! –  marc_s Oct 10 '10 at 11:12

1 Answer 1

up vote 0 down vote accepted

All those options are SQL Server-specific and will not be needed to create the index on an Oracle database; I imagine they are all at their default setting anyway and are just included because the advisor tool is verbose.

The more significant part is the INCLUDE clause which is described in this SO answer. Oracle has no counterpart to this, and so you will have to choose between one of the following:

create index idx_name on products(id);

create index idx_name on products(id, alias, prod_type);

The first of these is surely the primary key of the table and so is (surely?) already indexed, and uniquely.

The second indexes the primary key plus some additional columns, and might be beneficial in a query such as:

select alias, prod_type -- and no other columns
from products
where id = 123;

I suspect it is this latter index that the tuning advisor was suggesting, in addition to the presumably already present UNIQUE index on (id).

NOTE: Of course, it is not necessarily appropriate to add an index to an Oracle database on the advice of a SQL Server tuning advisor!

share|improve this answer
Thanks a lot Tony for the answer. You are right the ID is uniquely indexed with clustered index. Also there is second index which also has indexed ID but that index is non clustered. And third index has been suffested by database tuning advisor and it looks like I will have to cut at least of existent indexes. –  Andy Oct 10 '10 at 15:18

Your Answer


