An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.
Syntax of Index Creation.
The syntax for creating a index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
UNIQUE indicates that the combination of values in the indexed columns must be unique.
Create a Function-Based Index: In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes too.
The syntax for creating a function-based index is:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
Types of index data structures can you have
An index helps to faster search values in tables. The three most commonly used index-types are: -
B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
Other Important Topics in SQL:
Other Important Topics in SQL:
This post describes Index and its types in detail. Index is very important concept to know. The syntax for creating Index is easy so you can understand very easily or you can see the description below the syntax written in post.
ReplyDeleteindex helps us in faster retrieval of data.. Its used frequently in performance tuning of databases.Thanks for sharing..
ReplyDelete