Indexes

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Exploring and Changing the Database Structure >

Indexes

Indexes can make queries of a database considerably faster since only the indexed columns have to be queried. Some indexes are created automatically when you define a primary or unique key on a column. In addition to automatically-generated indexes, you can also add indexes explicitly, either at table design time, or later. Where supported by the database, DatabaseSpy allows for the definition of clustered, non-clustered, and XML indexes.

 

Database kind

Clustered index

Non-clustered index

XML index

Access

-

Yes, non-unique

-

Firebird

-

Yes

unique
non-unique

-

IBM DB2

Yes

Yes

unique
non-unique

Yes

IBM DB2 for i

Yes

Yes

unique
non-unique

-

Informix

-

Yes, non-unique

-

MariaDB

-

Yes

unique
non-unique

-

MySQL

-

Yes

unique
non-unique

-

Oracle

-

Yes

unique
non-unique
bitmap

-

PostgreSQL

-

Yes

unique
non-unique

 

The access method can be defined (such as btree, hash, gist, spgist, gin, and brin).

-

Progress OpenEdge

-

Yes

unique
non-unique

-

SQLite

-

Yes

unique
non-unique

-

SQL Server

Yes

unique
non-unique

Yes

unique
non-unique

Yes

Sybase

Yes

Yes

unique
non-unique

-

Teradata

-

Yes, see also the note below

-

 

Teradata databases

For Teradata databases, you can manually create from Design Editor only secondary unique and non-unique indexes. To do this, right-click a table column in Design Editor, and select Make index | Non Clustered Index from the context menu. To specify that the index should be unique, select the Unique check box in the Properties window.
 
In addition, with Teradata databases, a primary non-partitioned index is always created automatically for the first column in the table, even if you disabled the option Automatically create primary key for new tables from Tools | Options | Design Editor. Therefore, if you create a primary key manually from Design Editor, do not additionally create an index for that column; otherwise, the database change script will fail.


© 2019 Altova GmbH