Database Reference
In-Depth Information
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
1 - filter("ID"=42)
Making or creating an index invisible is helpful in two situations:
•
To assess whether it's possible to drop an existing index without jeopardizing performance.
This is useful when the index to be dropped is large. In fact, tentatively dropping a large index
isn't an option, because it takes too long and too many resources to rebuild the index if later
you decide that dropping it was a mistake.
•
To create an index without making it immediately available to the query optimizer.
By default, the query optimizer honors the visibility of an index. This is because, by default, the
optimizer_use_invisible_indexes
initialization parameter is set to
FALSE
. If this parameter is set to
TRUE
, either
at the system or at the session level, the query optimizer is allowed to treat invisible indexes as visible ones. As of
version 11.1.0.7, it's also possible to control whether the query optimizer honors the visibility of indexes by adding the
(
no_
)
use_invisible_indexes
hint to SQL statements.
■
according to the
High Availability Overview
manual: “an invisible index is maintained for any DmL
operation but isn't used by the optimizer unless you explicitly specify the index with a hint.” Unfortunately, this sentence
contains a mistake. the issue is that an
index
hint can't be used to change the visibility of invisible indexes. Only the
(
no_
)
use_invisible_indexes
hint impacts the visibility of invisible indexes.
Caution
Up to and including version 11.2, it's not possible to create several indexes on the same set of columns (if you try,
the database engine raises an ORA-01408). From version 12.1 onward this restriction has been removed. In fact, it's
possible to create several indexes on the same set of columns provided that only one of those indexes is visible at a
given time. This possibility, for applications that must be made highly available, is useful for changing the uniqueness,
type (B-tree or bitmap), and partitioning of an index without having to plan downtime. Without this feature, it might
be necessary to stop an otherwise highly available application while dropping and re-creating an index. The following
example, which is an excerpt of the output generated by the
multiple_indexes.sql
script, illustrates the feature:
1.
Setup the initial objects:
SQL> CREATE TABLE t (n1 NUMBER, n2 NUMBER, n3 NUMBER);
SQL> CREATE INDEX i_i ON t (n1);
Creating another
visible
index on the same column (
n1
) as the previous one isn't
supported (notice that the new index is unique):
2.
SQL> CREATE
UNIQUE
INDEX i_ui ON t (n1);
CREATE UNIQUE INDEX i_ui ON t (n1)
*
ERROR at line 1:
