Function Based Indexes
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index.
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.
Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index.
Indexing a Virtual Column
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Functional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.
Several caveats are associated with virtual columns:
* You can define a virtual column only on a regular heap-organized table. You can't define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
* Virtual columns can't reference other virtual columns.
* Virtual columns can reference columns only from the table in which the virtual column is defined.
* The output of a virtual column must be a scalar value (a single value, not a set of values).
It is said that creating a Virtual Column and Indexing the Virtual Column performs much better than a Functional Index on a Table Column however it all depends on the data which is stored in the table. Therefore it is always necessary to test it before replacing any Functional Index with a Virtual Column. Also Virtual column concept can applied only for Oracle Releases after 11G.A virtual column is not free. If you have an existing table, you have to create and maintain the DDL required to create the virtual column, whereas a function-based index can be added, modified, and dropped independently from the table.
Several caveats are associated with virtual columns:
* You can define a virtual column only on a regular heap-organized table. You can't define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
* Virtual columns can't reference other virtual columns.
* Virtual columns can reference columns only from the table in which the virtual column is defined.
* The output of a virtual column must be a scalar value (a single value, not a set of values).
Reverse Key Indexes
Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created.
Reverse-key indexes are similar to B-tree indexes except that the bytes of the index key are reversed when an index entry is created.
Speeding up the Index Creation
The following Two methods are available for Speeding up the Index Creation
* Turning Off the Redo Generated by using the NO LOGGING Clause in the Index Creation Script
* Parallelizing the Load also increases the speed of Index Creation. This can be achieved by using the PARALLEL Clause in the Index Creation Script.
The following Two methods are available for Speeding up the Index Creation
* Turning Off the Redo Generated by using the NO LOGGING Clause in the Index Creation Script
* Parallelizing the Load also increases the speed of Index Creation. This can be achieved by using the PARALLEL Clause in the Index Creation Script.
Shrinking of Un - Used Space Used by Tables and Indexes can be done only on Tablespaces with Automatic Segment Space Management.The COMPACT clause used with the SHRINK SPACE Clause instructs Oracle to only merge index blocks where possible and not to maximize the amount of space being freed up.
No comments:
Post a Comment