The index is a widely discussed subject among the Oracle fraternity. Most prolific use of an index in oracle is the faster retrieval of data. Indexes are filtered by a conditional logic from the table. They are logically and physically independent of data in the table. Without affecting any data, indexes can be created or dropped.
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column1>, <column2>, ... <column_n>) [ COMPUTE STATISTICS ]; Here, Unique : The combination of columns indexed should be unique index_name : A name given to an index table_name : A name given to table Column1, column2...column n : Columns of table The clause in square brackets are optional.
Statistics are collected and stored into data dictionary using Compute Statistics option. This option helps the optimizer to choose execution plan during execution of SQL statement that accesses this table.
CREATE INDEX emp_index0 ON employees (emp_id);
There are various reasons for which we need to alter indexes. Some of it are:
1) Rebuilding indexes in parallel or reverse.
2) Rename an index
3) To compute statistics
4) Making indexes unusable
5) Renaming, splitting or dropping an index partition
ALTER INDEX <index_name> RENAME TO <new_index_name>; ALTER INDEX <index_name> REBUILD COMPUTE STATISTICS; ALTER INDEX <index_name> MONITORING USAGE;
ALTER INDEX emp_index0 RENAME TO emp_index1; ALTER INDEX emp_index1 REBUILD COMPUTE STATISTICS; ALTER INDEX emp_index1 MONITORING USAGE;
Indexes are independent objects without any direct link or attachment to data in the table. Therefore, an index can be dropped anytime without affecting it.
DROP INDEX <index_name>;
DROP INDEX emp_index1;
An index created on the group of columns is referred to as a composite index. You can take any set of columns to create composite index irrespective of the order in which they are defined.
CREATE INDEX emp_index2 ON employees (emp_id, emp_number);
Index created on a function of a column used widely in WHERE clause is referred to as a Function-based index.
CREATE INDEX emp_index3 ON employees (UPPER(emp_name));
Some facts about index in oracle
- By default, Oracle creates B-tree indexes
- NULL values are not stored in indexes
- You can create unlimited indexes on a single table. Unlimited here means limit up to the maximum possible combinations of columns if used differently
CREATE INDEX emp_index1 ON employees (emp_name, dept_id); CREATE INDEX emp_index2 ON employees (dept_id, emp_name);
- Invisible index is the feature added in version 11g and later. It tells an optimizer to not consider the indexes while optimizing.
- The maximum number of columns you can take in a single composite index is 32.
If you liked the above post, please leave your comments below.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!