What is Index ?
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.
Let’s say you wanted to find information for a specific topic: Labrador dogs. You could do this in a few ways:
Start at page 1 and look through each page until you found Labrador dogs
Flip to a random page and see if you can find Labrador dogs, and repeat until you find it.
This would be very time consuming and not efficient at all.
Luckily, encyclopedias use a few different methods for making it easier to find information. The topics are often sorted alphabetically.
There is often an index at the back of the book as well, which includes all of the topics in the book. If we look for Labrador dogs in the index, we could see:
Labrador dogs… pg 100, 125, 217
This tells us that Labrador dogs are mentioned on these three pages. We can simply go to those pages to find out more about Labrador dogs.
This is a similar method to how indexes in databases work. They take up a bit more space, but they can help you find what you are looking for.
Types of Indexes :-
There are 6 different types of indexes in oracle.
1) B-Tree
2) Compressed B-Tree
3) Bitmap
4) Function-Based
5) Reverse Key (RKI)
6) Index organized table (IOT)
1) B-Tree
B-tree indexes are used to avoid large sorting operations. For example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a b-tree index to avoid the very large sort required to deliver the data to the end user.
>B-Tree Indexes (balanced tree) are the most common type of index.
>B-Tree index stored the ROWID and the index key value in a tree structure.
>When creating an index, a ROOT block is created, then BRANCH blocks are created and finally LEAF blocks.
>Each branch holds the range of data its leaf blocks hold, and each root holds the range of data its branches hold:
>B-Tree indexes are most useful on columns that appear in the where clause (SELECT … WHERE EMPNO=1).
>The Oracle server, keeps the tree balanced by splitting index blocks, when new data is inserted to the table.
>Whenever a DML statement is performed on the index’s table, index activity occurs, making the index to grow (add leaf and branches).
CREATE {UNIQUE|NON UNIQUE} INDEX {index_name}
ON {table_name} ({column_name},{column_name}…)
TABLESPACE {tablespace_name};
Example
Create index scott.exp_idx on table scott.example( name)
Tablespace TOOLS;
2) Compressed B-Tree
Compressed B-Tree Indexes are built on large tables, in a data warehouse environment. In this type of index, duplicate occurrences of the same value are eliminated, thus reducing the amount of storage space, the index requires. In a compressed B-Tree index, for each key value, a list of ROWIDs are kept:
Specifying the COMPRESS keyword when creating an index (CREATE INDEX … COMPRESS) will create a compressed B-Tree index. A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it.
CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS;
3) Bitmap
Another index type that is common in database systems is a bitmap index.
This is represented in the system as a two-dimensional table, which is often called a “map” of values.
For each row, there is a ROWID, which is a unique identifier for the row. For each column, there is a separate value of a specific column.
At the intersection of rows and columns is a single bit. This bit indicates whether that ROWID has the value mentioned in the column.
For example, we have a table that stores clothes and each row has a size. a bitmap index could look like this:
ROWID | S | M | L |
AA01 | 1 | ||
AA02 | 1 | ||
AA03 | 1 | ||
AA04 | 1 |
Why does this matter? Why is it useful?
It’s a different way of accessing the data more efficiently. If there is a small range of values (such as S/M/L shown above), then it’s often better to use this kind of index.
When you create an index, you don’t need to specify how each value maps to each row. This is all done automatically by the database system.
To create a bitmap index (in Oracle, anyway), the syntax is:
CREATE BITMAP INDEX idxname ON tabname(col1, col2, col3);
The only difference between the syntax for this bitmap index and a b-tree index is the addition of the word BITMAP.
4) Function-Based
Function-Based Indexes are indexes created on columns that a function is usually applied on.
When using a function on an indexed column, the index is ignored, therefore a function-based index is very useful for these operations.
CREATE INDEX {index_name} ON {table_name} [Function({column_name},{column_name.}];
Example
CREATE INDEX EMP_IDX on EMP(UPPER(ENAME));
SELECT * FROM Emp WHERE UPPER(Ename) like ‘JOHN`;
5) Reverse Key (RKI)
You also may see these indexes or want to use them from time to time. Consider a column, which includes names like "restaurant A", "restaurant B", "restaurant C" and so on. Perhaps a not very glamorous example, but the point is a column with many unique values but not much variation at the front. Using a reverse-key index would be ideal here, because Oracle will simple REVERSE the string before throwing it into the b-tree. So, the result will be a more balanced, useful, and ultimately fast index.
CREATE INDEX {index_name} ON {table_name} ({column_name})
TABLESPACE {tablespace_name}
REVERSE;
Example
CREATE INDEX emp_idx i ON emp_table (firstname,lastname) REVERSE;
6) Index organized table (IOT)
The index-organized table is like an ordinary table with an index on one or more of its columns, but instead of maintaining two separate storages for the table and the B-tree index, the database system maintains only a single B-tree index which contains both the encoded key value and the associated column values for the corresponding row. Rather than having a row’s rowid as the second element of the index entry, the actual data row is stored in the B-tree index. The data rows are built on the primary key for the table, and each B-tree index entry contains
There is no duplication of key values because only non-key column values are stored with the key. You can build secondary indexes to provide efficient access by other columns. Applications manipulate the index-organized table just like an ordinary table, using SQL statements. However, the database system performs all operations by manipulating the corresponding B-tree index.
Features of Index organized table
· Primary key uniquely identifies a row; primary key must be specified
· Primary key based access
· Logical rowid in ROWID pseudo column allows building secondary indexes
· UNIQUE constraint not allowed but triggers are allowed
· Cannot be stored in a cluster
· Can contain LOB columns but not LONG columns
· Distribution and replication not supported
There are 2 benefits of using IOT: 1. table rows are indexes, access to table is done using its primary key, the row is returned quickly from IOT than heap tables.
When Should I Create an Index?
Often, the first thing a developer will do when looking to improve a slow-running query is to create an index. I’ve been guilty of this in the past as well.
However, once you understand how indexes work, then you’ll know when the right time to create an index is, and how it can help you. Understanding your data and table structure is also important.
So, you should consider a few things when creating indexes:
->Creating indexes on columns in WHERE clauses or JOINs is a good idea.
->If a column has a large percentage of unique records, a b-tree index is usually more suitable.
->If a column has a low percentage of unique records, a bitmap index is usually better.
->Consider creating an index on foreign key columns (which are often used in joins anyway), as they are not created automatically (in Oracle anyway).
->Consider creating a function-based index if the column in your WHERE clause uses a function or expression in the criteria.
There are advantages and disadvantages to creating indexes. We’ve mentioned the advantages in this article. The disadvantages are:
->Indexes take up more space on the database. This can be quite significant, depending on your table, and if you’re restricted by space, it’s something to consider.
->Indexes can slow INSERT, UPDATE, and DELETE queries, because the index and the table need to be updated whenever these statements are run.
So, overall, indexes can be helpful and are a great way to improve the performance of your queries. There are a few different types, which are suitable in different situations, and they are pretty easy to create. Understanding the pros and cons of each, and your data structure, is helpful when deciding when and if to create indexes.
How to recreate the Indexes/rebuild index in oracle
We can Use the ALTER INDEX … REBUILD statement to reorganize or compact an existing index or to change its storage characteristics
The REBUILD statement uses the existing index as the basis for the new one.
ALTER INDEX … REBUILD is usually faster than dropping and re-creating an index.
It reads all the index blocks using multiblock I/O then discards the branch blocks.
A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.
Alter index {index name} rebuild ;
Alter index {index name} rebuild tablespace
No comments:
Post a Comment
Note: only a member of this blog may post a comment.