Altering Indexes
To alter an index, your schema must contain the index or you must have the ALTER ANY INDEX system privilege. With the ALTER INDEX statement, you can:
Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Specify LOGGING or NOLOGGING
Enable or disable key compression
Mark the index unusable
Make the index invisible
Rename the index
Start or stop the monitoring of index usage
You cannot alter index column structure.
More detailed discussions of some of these operations are contained in the following sections:
Oracle Database SQL Language Reference for details on the ALTER INDEX statement
Altering Storage Characteristics of an Index
Alter the storage parameters of any index, including those created by the database to enforce primary and unique key integrity constraints, using the ALTER INDEX statement. For example, the following statement alters the emp_ename index:
The parameters INITIAL and MINEXTENTS cannot be altered. All new settings for the other storage parameters affect only extents subsequently allocated for the index.
For indexes that implement integrity constraints, you can adjust storage parameters by issuing an ALTER TABLE statement that includes the USING INDEX subclause of the ENABLE clause. For example, the following statement changes the storage options of the index created on table emp to enforce the primary key constraint:
Oracle Database SQL Language Reference for syntax and restrictions on the use of the ALTER INDEX statement
Rebuilding an Existing Index
Before rebuilding an existing index, compare the costs and benefits associated with rebuilding to those associated with coalescing indexes as described in Table 21-1.
When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.
The following statement rebuilds the existing index emp_name :
The REBUILD clause must immediately follow the index name, and precede any other options. It cannot be used in conjunction with the DEALLOCATE UNUSED clause.
You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the emp_name index online:
To rebuild an index in a different user’s schema online, the following additional system privileges are required:
CREATE ANY TABLE
CREATE ANY INDEX
Online index rebuilding has stricter limitations on the maximum key length that can be handled, compared to other methods of rebuilding an index. If an ORA-1450 (maximum key length exceeded) error occurs when rebuilding online, try rebuilding offline, coalescing, or dropping and recreating the index.
If you do not have the space required to rebuild an index, you can choose instead to coalesce the index. Coalescing an index is an online operation.
Making an Index Unusable
When you make an index unusable, it is ignored by the optimizer and is not maintained by DML. When you make one partition of a partitioned index unusable, the other partitions of the index remain valid.
You must rebuild or drop and re-create an unusable index or index partition before using it.
The following procedure illustrates how to make an index and index partition unusable, and how to query the object status.
To make an index unusable:
Query the data dictionary to determine whether an existing index or index partition is usable or unusable.
For example, issue the following query (output truncated to save space):
The preceding output shows that only index partition p1_i_emp_ename is unusable.
Make an index or index partition unusable by specifying the UNUSABLE keyword.
The following example makes index emp_email_uk unusable:
The following example makes index partition p2_i_emp_ename unusable:
Optionally, query the data dictionary to verify the status change.
For example, issue the following query (output truncated to save space):
A query of space consumed by the i_emp_ename and emp_email_uk segments shows that the segments no longer exist:
Oracle Database SQL Language Reference for more information about the UNUSABLE keyword, including restrictions
Making an Index Invisible
An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. You cannot make an individual index partition invisible. Attempting to do so produces an error.
To make an index invisible:
Submit the following SQL statement:
To make an invisible index visible again:
Submit the following SQL statement:
To determine whether an index is visible or invisible:
Query the dictionary views USER_INDEXES , ALL_INDEXES , or DBA_INDEXES .
For example, to determine if the index ind1 is invisible, issue the following query:
Re naming an Index
To rename an index, issue this statement:
Monitoring Index Usage
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.
To start monitoring the usage of an index, issue this statement:
Later, issue the following statement to stop the monitoring:
The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO , depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column ( YES / NO ) to indicate if usage monitoring is currently active.
Each time that you specify MONITORING USAGE , the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE , no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX. MONITORING USAGE statement is issued, the view information is left unchanged.
Oracle Indexes and types of indexes in oracle with example
We will be discussing Oracle indexes/types of indexes in oracle with example in this post. I will through light on all the option on how to create index in oracle.I would also be discussing how to check index size in oracle. I hope you will like this post. I will be looking forward for feedback on this post
Table of Contents
What are Oracle Indexes?
- Just like we have index present in the textbooks to help us find the particular topic in the book, Oracle index behaves the same way.we have different types of indexes in oracle.
- Indexes are used to search the rows in the oracle table quickly. If the index is not present the select query has to read the whole table and returns the rows. With Index, the rows can be retrieved quickly
- We should create Indexes when retrieving a small number of rows from a table. or to retrieve the first set of rows as fast as possible from some query that will ultimately return a large number of rows. It also depends on the data distribution i.e clustering factor
- Indexes are logically and physically independent of the data in the associate table.
- Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table.
- Indexes are the primary means of reducing disk I/O when properly used.
- The query decides at the beginning whether to use index or no
- The best thing with indexes is that retrieval performance of indexed data remains almost constant, even as new rows are inserted. However, the presence of many indexes on a table decreases the performance of updates, deletes, and inserts because Oracle must also update the indexes associated with the table.
- If you are owner of table, you can create index or if you want to create index for table in another schema then you should either have CREATE ANY INDEX system privilege or index privilege on that table
Logical Type of Indexes
It defines the application characteristics of the Index
Unique or Non UniqueIndex can be Unique or non Unique. Oracle create unique index for Primary key and unique key constraints
If non-unique indexes are already present on that column , it will not create new unique index for Primary key in OracleCompositeThe index can be comprised of single of multiple columns. Composite indexes can speed retrieval of data for SELECT statement in which the WHERE clause references all or the leading portion of the columns in the composite index.Function Based indexesThe indexed column’s data is based on a calculationApplication Domain IndexesThis index used in special applications (Spatial, Text).
What is ROWID Pseudo column
ROWID returns the address of each row in the table. Oracle assigns a ROWID to each row.ROWID consists of following
- The data object number of the object
- The data block in the datafile in which the row resides
- The position of the row in the data block (first row is 0)
- The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.
Oracle uses ROWID internally to access rows. For instance, Oracle stores ROWID in index and uses it to access the row in the table.
You can display ROWID of rows using SELECT command as follows:
select rowid, emp_name from emp;
Oracle provides a package called DBMS_ROWID to decode ROWID.
Once a row is assigned a ROWID Oracle does not change ROWID during the lifetime of the row. But it changes when the table is rebuild, When rows are moved across the partition or shrinking of the table
Types of indexes in oracle with example
There are 6 different types of indexes in oracle
(2) Compressed B-Tree
(5) Reverse Key (RKI)
(6) Index organized table (IOT).
Lets find out each of them in detail and how to create index in oracle for each of these types
B — Tree Index:
- 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).
Advantages
- All leaf blocks of the tree are at the same depth.
- B-tree indexes automatically stay balanced.
- All blocks of the B-tree are three-quarters full on the average.
- B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
- Inserts, updates, and deletes are efficient, maintaining key order for fast retrieval.
- B-tree performance is good for both small and large tables, and does not degrade as the size of a table grows.
What is compressed B-tree Indexes
- 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.
What is Bitmap Indexes
- Bitmap Indexes are most appropriate on low distinct cardinality data (as opposed to B-Tree indexes).
- This type of index, creates a binary map of all index values, and store that map in the index blocks, this means that the index will require less space than B-Tree index.
- Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient
- When there are bitmap indexes on tables then updates will take out full table locks.So, Bitmap index is useful on large columns with low-DML(infrequent updates) activity or read only tables. This is reason you often find bitmap indexes are extensively used in the data warehouse environment(DWH).
- Bitmap Index structure contain a map of bits which indicate the value in the column, for example, for the GENDER column, the index block will hold the starting ROWID, the ending ROWID and the bit map:
- Bitmap indexes are very useful when created on columns with low cardinality, used with the AND & OR operator in the query condition:
Example
Advantages Of Bitmap Indexes
- Reduced response time for large classes of queries
- A substantial reduction of space usage compared to other indexing techniques
- Dramatic performance gains even on very low end hardware
- Very efficient parallel DML and loads
Function Based Indexes
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.
What is Reverse-Key Indexes
- They are special types of B-Tree indexes and are very useful when created on columns contain sequential numbers.
- When using a regular B-Tree, the index will grow to have many branches and perhaps several levels, thus causing performance degradation, the RKI solve the problem by reversing the bytes of each column key and indexing the new data.
- This method distributes the data evenly in the index. Creating a RKI is done using the REVERSE keyword: CREATE INDEX … ON … REVERSE;
What is Index Organized Tables (IOT) –
- When we are using B-Tree, Bitmap and Reverse key indexes are used for tables that store data in an un-ordered fashion (Heap Tables).
- These indexes contain the location of the ROWID of required table row, thus allowing direct access to row data
- An index-organized table differs from an ordinary table because the data for the table is held in its associated index. Changes to the table data, such as adding new rows, updating rows, or deleting rows, result in updating the index.
- 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 storage 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 <primary_key_value, non_primary_key_column_values>. Index-organized tables are suitable for accessing data by the primary key or any key that is a valid prefix of the primary key.
- 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 pseudocolumn 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
The Mapping Table maps the index’s physical ROWIDs to logical ROWIDs in the IOT. IOT use logical ROWIDs to manage table access by index because physical ROWIDs are changed whenever data is added to or removed from the table. In order to distinct the IOT from other indexes, query the USER_INDEXES view using the pct_direct_access column. Only IOT will have a non-NULL value for this column.
Application Domain Indexes
Oracle provides extensible indexing to accommodate indexes on complex data types such as documents, spatial data, images, and video clips and to make use of specialized indexing techniques.
With extensible indexing, you can encapsulate application-specific index management routines as an indextype schema object and define a domain index (an application-specific index) on table columns or attributes of an object type. Extensible indexing also provides efficient processing of application-specific operators.
The application software, called the cartridge, controls the structure and content of a domain index. The Oracle server interacts with the application to build, maintain, and search the domain index. The index structure itself can be stored in the Oracle database as an index-organized table or externally as a file.
Using Domain Indexes
Domain indexes are built using the indexing logic supplied by a user-defined indextype. An indextype provides an efficient mechanism to access data that satisfy certain operator predicates. Typically, the user-defined indextype is part of an Oracle option, like the Spatial option.
For example, the SpatialIndextype allows efficient search and retrieval of spatial data that overlap a given bounding box.
The cartridge determines the parameters you can specify in creating and maintaining the domain index. Similarly, the performance and storage characteristics of the domain index are presented in the specific cartridge documentation.
So far we have covered different types of indexes in oracle with example,lets now check how to alter/drop/recreate them
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 multi block 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.
How to Write Statements that Avoid Using Indexes
- You can use the NO_INDEX optimizer hint to give the CBO maximum flexibility while disallowing the use of a certain index.
- You can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan.
- You can use the INDEX, INDEX_COMBINE, or AND_EQUAL hints to force the optimizer to use one index or a set of listed indexes instead of another.
How to gather statistics for Indexes
Index statistics are gathered using the ANALYZE INDEX or dbms_stats statement.
Available options are COMPUTE/ESTIMATE STATISTICS or VALIDATE STRUCTURE.
With 10g onwards, when the index is created, compute statistics is done automatically
When using the validate structure, Oracle populates the INDEX_STATS view with statistics related to analyzed index. The statistics contain number of leaf rows & blocks (LF_ROWS, LF_BLKS), number branch rows & blocks (BR_ROWS, BR_BLKS), number of deleted leaf rows (DEL_LF_ROWS), used space (USED_SPACE), number of distinct keys (DISTINCT_KEYS) etc. These statistics can be used to determine if the index should be rebuild or not
How does Oracle decide about the usage of index?
Oracle automatically decides about whether index should be used by Optimizer engine.
Oracle decides whether to use an index or not depending upon the query.
Oracle can understand whether using an index will improve the performance in the given query. If Oracle thinks using an index will improve performance, it will use the index otherwise it will ignore the index.
Let us understand by this example
We have a table emp which contains emp_name, salary,dept_no ,emp_no,date_of_joining and we have an index on emp_name
The above query will use the index as we are trying to get information about a emp based on the name.
The above query will not use index as we are trying to find all the rows in the table and we don’t have where clause in the query
The above query will not use index as the where clause does not select the column which has index
The above query will not use index as the where clause uses the function on the column and we don’t have functional index on emp_name
How to create or rebuild the index Online?
Oracle used to lock the table on which index is being created throughout creation process in older versions. This makes table unavailable for data manipulation during the creation of index.
Now with 8i , Oracle introduced online rebuilding of index where Oracle doesn’t lock the table on which index is being built.
Online indexing is provided through the keyword ONLINE.
Basically with online rebuild,Oracle locks the table at the start and end of the creation of the index. It allows transaction in between. The mechanism has been quite improved with 11g and 12c
What are the Drawbacks of the Indexes
Indexes increase performance of select query, they can also decrease performance of data manipulation.
Many indexes on a table can slow down INSERTS and DELETES drastically
The more the indexes on the table, the more time inserts and delete will take.
Similarly every change to an indexed column will need a change to index.
So we need to choose the index very carefully and drop which are not in use.
Though the extra space occupied by indexes is also a consideration, it may not matter much since the cost of data storage has declined substantially.
What is Unusable indexes
An unusable index is ignored by the optimizer in deciding the explain plan
It is also not maintained by DML i.e update,insert, delete does the update the index
There could be several for index in unusable state. You did the rebuild of the table but did not rebuild the index, then index will be unusable state. One other reason to make an index unusable is to improve bulk load performance. Another reason might be optimizer picking up the wrong index every time and time is critical so you may decide to make it unusable
An unusable index or index partition must be rebuilt, or dropped and re-created, before it can be used. Truncating a table makes an unusable index valid.
Beginning with Oracle Database 11g Release 2, when you make an existing index unusable, its index segment is dropped.
The functionality of unusable indexes depends on the setting of the SKIP_UNUSABLE_INDEXES initialization parameter.
When SKIP_UNUSABLE_INDEXES is TRUE (the default), then:
DML statements against the table proceed, but unusable indexes are not maintained.
DML statements terminate with an error if there are any unusable indexes that are used to enforce the UNIQUE constraint.
For non partitioned indexes, the optimizer does not consider any unusable indexes when creating an access plan for SELECT statements. The only exception is when an index is explicitly specified with the INDEX() hint.
When SKIP_UNUSABLE_INDEXES is FALSE, then:
If any unusable indexes or index partitions are present, any DML statements that would cause those indexes or index partitions to be updated are terminated with an error.
For SELECT statements, if an unusable index or unusable index partition is present but the optimizer does not choose to use it for the access plan, the statement proceeds. However, if the optimizer does choose to use the unusable index or unusable index partition, the statement terminates with an error.
Yasin Yazıcı's Oracle Blog
I would like to tell differences between unusable and invisible index in this my post.
Unusable Index:When you make an index unusable it is ignored by optimizer and not updated by dml operation. After you make index unusable.It must rebuilded before using it.Let make a example.First of all we create a table and then we create an index for our scenarios.
SQL>CREATE TABLE yasin.indexexp (n, c) NOLOGGING PARALLEL PCTFREE 99 PCTUSED 1
AS SELECT level, CAST (to_char(level) AS varchar2(10)) FROM dual
CONNECT BY level <= 1000
SQL>create index indexexp_x1 on yasin.indexexp(N);
You can see that our query has used existing index.You can query whether index is usable as following.
SQL>SELECT INDEX_NAME AS “INDEX OR PART NAME”, STATUS, SEGMENT_CREATED
FROM USER_INDEXES where index_name=’INDEXEXP_X1′
You can make index unusable as following.After this you can query index status as mentioned above.
SQL>alter index yasin.indexexp_x1 unusable;SQL> SELECT INDEX_NAME AS “INDEX OR PART NAME”, STATUS, SEGMENT_CREATED
FROM USER_INDEXES where index_name=’INDEXEXP_X1′; 2
INDEX OR PART NAME STATUS SEG
—————————— ——– —
INDEXEXP_X1 UNUSABLE NO
After performed this operation you can see that plan is changed.so Optimzer has not used index.
You can make index usable as following or you can drop index and recreate to be usable index.
SQL> alter index yasin.indexexp_x1 rebuild;
Index altered.
After you have rebuld index you can check that what is the index status.When you make an index usable again optimizer will use index.
SQL> SELECT INDEX_NAME AS “INDEX OR PART NAME”, STATUS, SEGMENT_CREATED
FROM USER_INDEXES where index_name=’INDEXEXP_X1′; 2INDEX OR PART NAME STATUS SEG
—————————— ——– —
INDEXEXP_X1 VALID YES
Invisible Index:When you make index invisible it is ignored by optimizer.You can use this for test purpose.you can make index invisible when you create index or with alter index command after creating table . Hovewer if you set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE invisible index can be used by optimizer.This parameter can be set session or system level.
NOTE:When you make index invisible this will result invalidate all sql statement in the shared memory that have execution plan using this index
SQL> create index indexexmp_X2 on indexexp(C) invisible;
Index created.
SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
2 WHERE INDEX_NAME = ‘INDEXEXMP_X2’;
INDEX_NAME VISIBILIT
—————————— ———
INDEXEXMP_X2 INVISIBLE
or you can make index invisible with alter command after you create table
SQL> alter index yasin.indexexp_x1 invisible;
Index altered.
SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘INDEXEXP_X1’ ;
INDEX_NAME VISIBILIT
—————————— ———
INDEXEXP_X1 INVISIBLE
And when you look execution plan you see that optimizer ignored index.
Let set OPTIMIZER_USE_INVISIBLE_INDEXES parameter to TRUE and we execute query again.
Oracle Database Reflections
Это простой запрос, который составляет скрипт для перестройки UNUSABLE индексов.
Наличие таких индексов может являться причиной падения производительности, если параметр БД SKIP_UNUSABLE_INDEXES установлен в TRUE. В этом случае БД не генерирует ошибку при попытке использования битого индекса и пробует обходиться без него — или использует другой индекс (если такой есть), или TABLE FULL SCAN
Проверить его значение можно в SQL*PLUS
SQL> show parameter unusable
Выполните скрипт ниже в TOAD или SQL Navigator, в результате будет одна колонка со списком команд alter index ***** rebuild
where INDEX_TYPE in ( ‘NORMAL’ , ‘BITMAP’ , ‘FUNCTION-BASED NORMAL’ ) and PARTITIONED = ‘NO’ and STATUS <> ‘VALID’
union all
select ‘alter index ‘ || b . INDEX_OWNER || ‘.’ || b . INDEX_NAME || ‘ rebuild partition ‘ || b . PARTITION_NAME || ‘;’ as TOAD_CMD
from DBA_INDEXES a, DBA_IND_PARTITIONS b
where a. OWNER = b . INDEX_OWNER and a. INDEX_NAME = b . INDEX_NAME and a. PARTITIONED = ‘YES’ and a. INDEX_TYPE in ( ‘NORMAL’ , ‘BITMAP’ , ‘FUNCTION-BASED NORMAL’ ) and b . STATUS = ‘UNUSABLE’;
