About database indexes 1

Why are indexes used?

Indexes are used to increase the performance of retrieval. We can make use of one or more rows in order to create the index. Indexes can increase the performance of retrieval and slows down the performance of insertion into the tables.

Who is responsible for updating the indexes?

Oracle automatically maintains and uses indexes and when any change is made in the table data, Oracle automatically distributes it into relevant indexes.

Indexes

An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries.

Indexes can be created against a table to make searches more efficient. A database index is similar to an index of a book – a book index allows you to find information without having to read through the whole book. A database index enables the database application to find data quickly without having to scan the whole table.

Remember, indexes can have a slight impact on performance so you should only create indexes against tables and columns that will be frequently searched against. For example, if users of your application often search against the LastName field then that field is a great candidate for an index.

You create an index using the CREATE INDEX command.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.


CREATE INDEX Example

The SQL statement below creates an index named “PIndex” on the “LastName” column in the “Persons” table:

CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

 

  • Hi, this is a comment.
    To delete a comment, just log in and view the post's comments. There you will have the option to edit or delete them.

Follow

Get every new post delivered to your Inbox

Join other followers: