Oracle unusable index что это

Specify the schema containing the index. If you omit schema , then Oracle Database assumes the index is in your own schema.

Specify the name of the index to be altered.

Restrictions on Modifying Indexes

The modification of indexes is subject to the following restrictions:

If index is a domain index, then you can specify only the PARAMETERS clause, the RENAME clause, the rebuild_clause (with or without the PARAMETERS clause), the parallel_clause , or the UNUSABLE clause. No other clauses are valid.

You cannot alter or rename a domain index that is marked LOADING or FAILED . If an index is marked FAILED , then the only clause you can specify is REBUILD .

Oracle Database Data Cartridge Developer’s Guide for information on the LOADING and FAILED states of domain indexes

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the index and make the freed space available for other segments in the tablespace.

If index is range-partitioned or hash-partitioned, then Oracle Database deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, then Oracle Database deallocates unused space from each index subpartition.

Restrictions on Deallocating Space

Deallocation of space is subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

You cannot specify this clause and also specify the rebuild_clause .

Refer to deallocate_unused_clause for a full description of this clause.

The KEEP clause lets you specify the number of bytes above the high water mark that the index will have after deallocation. If the number of remaining extents is less than MINEXTENTS , then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL , then INITIAL is set to the value of the current initial extent. If you omit KEEP , then all unused space is freed.

Refer to ALTER TABLE for a complete description of this clause.

The allocate_extent_clause lets you explicitly allocate a new extent for the index. For a local index on a hash-partitioned table, Oracle Database allocates a new extent for each partition of the index.

Restriction on Allocating Extents

You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned index.

Refer to allocate_extent_clause for a full description of this clause.

Use this clause to compact the index segments. Specifying ALTER INDEX . SHRINK SPACE COMPACT is equivalent to specifying ALTER INDEX . COALESCE .

For complete information on this clause, refer to shrink_clause in the documentation on CREATE TABLE .

Restriction on Shrinking Index Segments

You cannot specify this clause for a bitmap join index or for a function-based index.

Use the PARALLEL clause to change the default degree of parallelism for queries and DML on the index.

Restriction on Parallelizing Indexes

You cannot specify this clause for an index on a temporary table.

For complete information on this clause, refer to parallel_clause in the documentation on CREATE TABLE .

Use the physical_attributes_clause to change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition.

the physical attributes parameters in CREATE TABLE

Restrictions on Index Physical Attributes

Index physical attributes are subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

You cannot specify the PCTUSED parameter at all when altering an index.

You can specify the PCTFREE parameter only as part of the rebuild_clause , the modify_index_default_attrs clause, or the split_index_partition clause.

Use the storage_clause to change the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. Refer to storage_clause for complete information on this clause.

Use the logging_clause to change the logging attribute of the index. If you also specify the REBUILD clause, then this new setting affects the rebuild operation. If you specify a different value for logging in the REBUILD clause, then Oracle Database uses the last logging value specified as the logging attribute of the index and of the rebuild operation.

An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.

Restriction on Index Logging

You cannot specify this clause for an index on a temporary table.

logging_clause for a full description of this clause

Use the partial_index_clause to change the index to a full index or a partial index. Specify INDEXING FULL to change the index to a full index. Specify INDEXING PARTIAL to change the index to a partial index. This clause is valid only for indexes on partitioned tables. Refer to the partial_index_clause of CREATE INDEX for the full semantics of this clause.

These keywords are deprecated and have been replaced with LOGGING and NOLOGGING , respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle strongly recommends that you use the LOGGING and NOLOGGING keywords.

RECOVERABLE is not a valid keyword for creating partitioned tables or LOB storage characteristics. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the AS subquery clause of CREATE INDEX .

Use the rebuild_clause to re-create an existing index or one of its partitions or subpartitions. If index is marked UNUSABLE , then a successful rebuild will mark it USABLE . For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, then the rebuild statement will fail.

When you rebuild the secondary index of an index-organized table, Oracle Database preserves the primary key columns contained in the logical rowid when the index was created. Therefore, if the index was created with the COMPATIBLE initialization parameter set to less than 10.0.0, the rebuilt index will contain the index key and any of the primary key columns of the table that are not also in the index key. If the index was created with the COMPATIBLE initialization parameter set to 10.0.0 or greater, then the rebuilt index will contain the index key and all the primary key columns of the table, including those also in the index key.

Restrictions on Rebuilding Indexes

The rebuilding of indexes is subject to the following restrictions:

You cannot rebuild an index on a temporary table.

You cannot rebuild a bitmap index that is marked INVALID . Instead, you must drop and then re-create it.

You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described for the PARTITION clause.

You cannot specify the deallocate_unused_clause in the same statement as the rebuild_clause .

You cannot change the value of the PCTFREE parameter for the index as a whole ( ALTER INDEX ) or for a partition ( ALTER INDEX . MODIFY PARTITION ). You can specify PCTFREE in all other forms of the ALTER INDEX statement.

For a domain index:

You can specify only the PARAMETERS clause (either for the index or for a partition of the index) or the parallel_clause . No other rebuild clauses are valid.

You can rebuild an index only if the index is not marked IN_PROGRESS .

You can rebuild an index partition only if the index is not marked IN_PROGRESS or FAILED and the partition is not marked IN_PROGRESS .

You cannot rebuild a local index, but you can rebuild a partition of a local index ( ALTER INDEX . REBUILD PARTITION ).

For a local index on a hash partition or subpartition, the only parameter you can specify is TABLESPACE .

You cannot rebuild an online index that is used to enforce a deferrable unique constraint.

Use the PARTITION clause to rebuild one partition of an index. You can also use this clause to move an index partition to another tablespace or to change a create-time physical attribute.

The storage of partitioned database entities in tablespaces of different block sizes is subject to several restrictions. Refer to Oracle Database VLDB and Partitioning Guide for a discussion of these restrictions.

Restriction on Rebuilding Partitions

You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD SUBPARTITION clause.

Use the SUBPARTITION clause to rebuild one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify TABLESPACE , then the subpartition is rebuilt in the same tablespace.

Restriction on Modifying Index Subpartitions

The only parameters you can specify for a subpartition are TABLESPACE , ONLINE , and the parallel_clause .

Indicate whether the bytes of the index block are stored in reverse order:

REVERSE stores the bytes of the index block in reverse order and excludes the rowid when the index is rebuilt.

NOREVERSE stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a REVERSE index without the NOREVERSE keyword produces a rebuilt, reverse-keyed index.

Restrictions on Reverse Indexes

Reverse indexes are subject to the following restrictions:

You cannot reverse a bitmap index or an index-organized table.

You cannot specify REVERSE or NOREVERSE for a partition or subpartition.

Use the parallel_clause to parallelize the rebuilding of the index and to change the degree of parallelism for the index itself. All subsequent operations on the index will be executed with the degree of parallelism specified by this clause, unless overridden by a subsequent data definition language (DDL) statement with the parallel_clause . The following exceptions apply:

If ALTER SESSION DISABLE PARALLEL DDL was specified before rebuilding the index, then the index will be rebuilt serially and the degree of parallelism for the index will be changed to 1.

If ALTER SESSION FORCE PARALLEL DDL was specified before rebuilding the index, then the index will be rebuilt in parallel and the degree of parallelism for the index will be changed to the value that was specified in the ALTER SESSION statement, or DEFAULT if no value was specified.

Specify the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.

Use the index_compression clauses to enable or disable index compression for the index. Specify the prefix_compression clause to enable or disable prefix compression for the index. Specify the advanced_index_compression clause to enable or disable advanced index compression for the index.

The index_compression clauses have the same semantics for CREATE INDEX and ALTER INDEX . For full information on these clauses, refer to index_compression in the documentation on CREATE INDEX .

Specify ONLINE to allow DML operations on the table or partition during rebuilding of the index.

Restrictions on Online Indexes

Online indexes are subject to the following restrictions:

Parallel DML is not supported during online index building. If you specify ONLINE and subsequently issue parallel DML statements, then Oracle Database returns an error.

You cannot specify ONLINE for a bitmap join index or a cluster index.

For a nonunique secondary index on an index-organized table, the number of index key columns plus the number of primary key columns that are included in the logical rowid in the index-organized table cannot exceed 32. The logical rowid excludes columns that are part of the index key.

Specify whether the ALTER INDEX . REBUILD operation will be logged.

Refer to the logging_clause for a full description of this clause.

This clause is valid only for domain indexes in a top-level ALTER INDEX statement and in the rebuild_clause . This clause specifies the parameter string that is passed uninterpreted to the appropriate ODCI indextype routine.

The maximum length of the parameter string is 1000 characters.

If you are altering or rebuilding an entire index, then the string must refer to index-level parameters. If you are rebuilding a partition of the index, then the string must refer to partition-level parameters.

If index is marked UNUSABLE , then modifying the parameters alone does not make it USABLE . You must also rebuild the UNUSABLE index to make it usable.

If you have installed Oracle Text, then you can rebuild your Oracle Text domain indexes using parameters specific to that product. For more information on those parameters, refer to Oracle Text Reference .

Restriction on the PARAMETERS Clause

You can modify index partitions only if index is not marked IN_PROGRESS or FAILED , no index partitions are marked IN_PROGRESS , and the partition being modified is not marked FAILED .

Oracle Database Data Cartridge Developer’s Guide for more information on indextype routines for domain indexes

CREATE INDEX for more information on domain indexes

This clause is valid only for XMLIndex indexes. This clause specifies the parameter string that defines the XMLIndex implementation.

The maximum length of the parameter string is 1000 characters.

If index is marked UNUSABLE , then modifying the parameters alone does not make it USABLE . You must also rebuild the UNUSABLE index to make it usable.

Oracle XML DB Developer’s Guide for more information on XMLIndex , including the syntax and semantics of the XMLIndex_parameters_clause

Restriction on the XMLIndex_parameters_clause

You can modify index partitions only if index is not marked IN_PROGRESS or FAILED , no index partitions are marked IN_PROGRESS , and the partition being modified is not marked FAILED .

This clause lets you control when the database invalidates dependent cursors while rebuilding an index or while marking an index UNUSABLE .

If you specify DEFERRED INVALIDATION , then the database avoids or defers invalidating dependent cursors, when possible.

If you specify IMMEDIATE INVALIDATION , then the database immediately invalidates dependent cursors, as it did in Oracle Database 12 c Release 1 (12.1) and prior releases. This is the default.

If you omit this clause, then the value of the CURSOR_INVALIDATION initialization parameter determines when cursors are invalidated.

Oracle Database SQL Tuning Guide for more information on cursor invalidation

Oracle Database Reference for more information in the CURSOR_INVALIDATION initialization parameter

Use this clause to recompile an invalid index explicitly. For domain indexes, this clause is useful when the underlying indextype has been altered to support system-managed domain indexes, so that the existing domain index has been marked INVALID . In this situation, this ALTER INDEX statement migrates the domain index from a user-managed domain index to a system-managed domain index. For all types of indexes, this clause is useful when an index has been marked INVALID by an ALTER TABLE statement. In this situation, this ALTER INDEX statement revalidates the index without rebuilding it.

The CREATE INDEXTYPE storage_table_clause and Oracle Database Data Cartridge Developer’s Guide for information on creating system-managed domain indexes

ENABLE applies only to a function-based index that has been disabled, either by an ALTER INDEX . DISABLE statement, or because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

The function is currently valid.

The signature of the current function matches the signature of the function when the index was created.

The function is currently marked as DETERMINISTIC .

Restrictions on Enabling Function-based Indexes

The ENABLE clause is subject to the following restrictions:

You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE .

You cannot specify this clause for an index on a temporary table. Instead, you must drop and recreate the index. You can retrieve the creation DDL for the index using the DBMS_METADATA package.

DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.

Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE . The space allocated for an index or index partition or subpartition is freed immediately when the object is marked UNUSABLE . An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE , the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it. Refer to CREATE INDEX . USABLE | UNUSABLE for more information.

Specify ONLINE to indicate that DML operations on the table or partition will be allowed while marking the index UNUSABLE . If you specify this clause, then the database will not drop the index segments.

Restrictions on Marking Indexes Unusable

The following restrictions apply to marking indexes unusable:

You cannot specify UNUSABLE for an index on a temporary table.

When a global index is marked UNUSABLE during a partition maintenance operation, the database does not drop the unusable index segments.

Use this clause to specify whether the index is visible or invisible to the optimizer. Refer to «VISIBLE | INVISIBLE» in CREATE INDEX for a full description of this clause.

Use this clause to rename an index. The new_index_name is a single identifier and does not include the schema name.

Restriction on Renaming Indexes

For a domain index, neither index nor any partitions of index should be in IN_PROGRESS or FAILED state.

Specify COALESCE to instruct Oracle Database to merge the contents of index blocks where possible to free blocks for reuse.

Specify CLEANUP to remove orphaned index entries for records that were previously dropped or truncated by a table partition maintenance operation.

To determine whether an index contains orphaned index entries, you can query the ORPHANED_ENTRIES column of the USER_ , DBA_ , ALL_INDEXES data dictionary views. Refer to Oracle Database Reference for more information.

Specify ONLY when you want to clean up the index without coalescing the index blocks.

Use the parallel_clause to specify whether to parallelize the coalesce operation.

For complete information on this clause, refer to parallel_clause in the documentation on CREATE TABLE .

Restrictions on Coalescing Index Blocks

Coalescing of index blocks is subject to the following restrictions:

You cannot specify this clause for an index on a temporary table.

Do not specify this clause for the primary key index of an index-organized table. Instead use the COALESCE clause of ALTER TABLE .

Oracle Database Administrator’s Guide for more information on space management and coalescing indexes

COALESCE Clause for information on coalescing the space of an index-organized table

shrink_clause for an alternative method of compacting index segments

MONITORING USAGE | NOMONITORING USAGE

Use this clause to determine whether Oracle Database should monitor index use.

Specify MONITORING USAGE to begin monitoring the index. Oracle Database first clears existing information on index use, and then monitors the index for use until a subsequent ALTER INDEX . NOMONITORING USAGE statement is executed.

To terminate monitoring of the index, specify NOMONITORING USAGE .

To see whether the index has been used since this ALTER INDEX . NOMONITORING USAGE statement was issued, query the USED column of the USER_OBJECT_USAGE data dictionary view.

Oracle Database Reference for information on the USER_OBJECT_USAGE data dictionary view

UPDATE BLOCK REFERENCES Clause

The UPDATE BLOCK REFERENCES clause is valid only for normal and domain indexes on index-organized tables. Specify this clause to update all the stale guess data block addresses stored as part of the index row with the correct database address for the corresponding block identified by the primary key.

For a domain index, Oracle Database executes the ODCIIndexAlter routine with the alter_option parameter set to AlterIndexUpdBlockRefs . This routine enables the cartridge code to update the stale guess data block addresses in the index.

Restriction on UPDATE BLOCK REFERENCES

You cannot combine this clause with any other clause of ALTER INDEX .

The partitioning clauses of the ALTER INDEX statement are valid only for partitioned indexes.

Restrictions on Modifying Index Partitions

Modifying index partitions is subject to the following restrictions:

You cannot specify any of these clauses for an index on a temporary table.

You can combine several operations on the base index into one ALTER INDEX statement (except RENAME and REBUILD ), but you cannot combine partition operations with other partition operations or with operations on the base index.

Specify new values for the default attributes of a partitioned index.

Restriction on Modifying Partition Default Attributes

The only attribute you can specify for a hash-partitioned global index or for an index on a hash-partitioned table is TABLESPACE .

Specify the default tablespace for new partitions of an index or subpartitions of an index partition.

Specify the default logging attribute of a partitioned index or an index partition.

Refer to logging_clause for a full description of this clause.

Use the FOR PARTITION clause to specify the default attributes for the subpartitions of a partition of a local index on a composite-partitioned table.

Restriction on FOR PARTITION

You cannot specify FOR PARTITION for a list partition.

Use this clause to add a partition to a global hash-partitioned index. Oracle Database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. If you omit the partition name, then Oracle Database assigns a name of the form SYS_P n . If you omit the TABLESPACE clause, then Oracle Database places the partition in the tablespace specified for the index. If no tablespace is specified for the index, then Oracle Database places the partition in the default tablespace of the user, if one has been specified, or in the system default tablespace.

Use the modify_index_partition clause to modify the real physical attributes, logging attribute, or storage characteristics of index partition partition or its subpartitions. For a hash-partitioned global index, the only subclause of this clause you can specify is UNUSABLE .

Specify this clause to merge the contents of index partition blocks where possible to free blocks for reuse.

Specify CLEANUP to remove orphaned index entries for records that were previously dropped or truncated by a table partition maintenance operation.

To determine whether an index partition contains orphaned index entries, you can query the ORPHANED_ENTRIES column of the USER_ , DBA_ , ALL_PART_INDEXES data dictionary views. Refer to Oracle Database Reference for more information.

UPDATE BLOCK REFERENCES

The UPDATE BLOCK REFERENCES clause is valid only for normal indexes on index-organized tables. Use this clause to update all stale guess data block addresses stored in the secondary index partition.

Restrictions on UPDATE BLOCK REFERENCES

This clause is subject to the following restrictions:

You cannot specify the physical_attributes_clause for an index on a hash-partitioned table.

You cannot specify UPDATE BLOCK REFERENCES with any other clause in ALTER INDEX .

If the index is a local index on a composite-partitioned table, then the changes you specify here will override any attributes specified earlier for the subpartitions of index, as well as establish default values of attributes for future subpartitions of that partition. To change the default attributes of the partition without overriding the attributes of subpartitions, use ALTER TABLE . MODIFY DEFAULT ATTRIBUTES FOR PARTITION .

This clause has the same function for index partitions that it has for the index as a whole. Refer to «USABLE | UNUSABLE» .

This clause is relevant for composite-partitioned indexes. Use this clause to change the compression attribute for the partition and every subpartition in that partition. Oracle Database marks each index subpartition in the partition UNUSABLE and you must then rebuild these subpartitions. Prefix compression must already have been specified for the index before you can specify the prefix_compression clause for a partition, or advanced index compression must have already been specified for the index before you can specify the advanced_index_compression clause for a partition. You can specify this clause only at the partition level. You cannot change the compression attribute for an individual subpartition.

You can use this clause for noncomposite index partitions. However, it is more efficient to use the rebuild_clause for noncomposite partitions, which lets you rebuild and set the compression attribute in one step.

Use the rename_index_partition clauses to rename index partition or subpartition to new_name .

Restrictions on Renaming Index Partitions

Renaming index partitions is subject to the following restrictions:

You cannot rename the subpartition of a list partition.

For a partition of a domain index, index cannot be marked IN_PROGRESS or FAILED , none of the partitions can be marked IN_PROGRESS , and the partition you are renaming cannot be marked FAILED .

Use the drop_index_partition clause to remove a partition and the data in it from a partitioned global index. When you drop a partition of a global index, Oracle Database marks the next index partition UNUSABLE . You cannot drop the highest partition of a global index.

Use the split_index_partition clause to split a partition of a global range-partitioned index into two partitions, adding a new partition to the index. This clause is not valid for hash-partitioned global indexes. Instead, use the add_hash_index_partition clause.

Splitting a partition marked UNUSABLE results in two partitions, both marked UNUSABLE . You must rebuild the partitions before you can use them.

Splitting a partition marked USABLE results in two partitions populated with index data. Both new partitions are marked USABLE .

Specify the new noninclusive upper bound for split_partition_1 . The value_list must evaluate to less than the presplit partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).

Specify (optionally) the name and physical attributes of each of the two partitions resulting from the split.

This clause is valid only for hash-partitioned global indexes. Oracle Database reduces by one the number of index partitions. Oracle Database selects the partition to coalesce based on the requirements of the hash function. Use this clause if you want to distribute index entries of a selected partition into one of the remaining partitions and then remove the selected partition.

Use the modify_index_subpartition clause to mark UNUSABLE or allocate or deallocate storage for a subpartition of a local index on a composite-partitioned table. All other attributes of such a subpartition are inherited from partition-level default attributes.

Storing Index Blocks in Reverse Order: Example

The following statement rebuilds index ord_customer_ix (created in «Creating an Index: Example» ) so that the bytes of the index block are stored in reverse order:

Rebuilding an Index in Parallel: Example

The following statement causes the index to be rebuilt from the existing index by using parallel execution processes to scan the old and to build the new index:

Modifying Real Index Attributes: Example

The following statement alters the oe.cust_lname_ix index so that future data blocks within this index use 5 initial transaction entries:

If the oe.cust_lname_ix index were partitioned, then this statement would also alter the default attributes of future partitions of the index. Partitions added in the future would then use 5 initial transaction entries and an incremental extent of 100K.

Enabling Parallel Queries: Example

The following statement sets the parallel attributes for index upper_ix (created in «Creating a Function-Based Index: Example» ) so that scans on the index will be parallelized:

Renaming an Index: Example

The following statement renames an index:

Marking an Index Unusable: Examples

The following statements use the cost_ix index, which was created in «Creating a Range-Partitioned Global Index: Example» . Partition p1 of that index was dropped in «Dropping an Index Partition: Example» . The first statement marks index partition p2 as UNUSABLE :

The next statement marks the entire index cost_ix as UNUSABLE :

Rebuilding Unusable Index Partitions: Example

The following statements rebuild partitions p2 and p3 of the cost_ix index, making the index once more usable: The rebuilding of partition p3 will not be logged:

Changing MAXEXTENTS: Example

The following statement changes the maximum number of extents for partition p3 and changes the logging attribute:

Renaming an Index Partition: Example

The following statement renames an index partition of the cost_ix index (created in «Creating a Range-Partitioned Global Index: Example» ):

Splitting a Partition: Example

The following statement splits partition p2 of index cost_ix (created in «Creating a Range-Partitioned Global Index: Example» ) into p2a and p2b :

Dropping an Index Partition: Example

The following statement drops index partition p1 from the cost_ix index:

Modifying Default Attributes: Example

The following statement alters the default attributes of local partitioned index prod_idx , which was created in «Creating an Index on a Hash-Partitioned Table: Example» . Partitions added in the future will use 5 initial transaction entries:

Oracle mechanics

Пока в запросе не используются подсказки, можно наблюдать ожидаемый («разумный») выбор оптимизатора — использовать индексный доступ INDEX RANGE SCAN когда индекс доступен (статус VALID), и не использовать индекс в статусе UNUSABLE (при этом для доступа к данным используется TABLE FULL SCAN):

И в трейсе 10053 можно найти причины такого выбора:

— т.е. индекс UNUSABLE => значит индексный доступ (INDEX SCAN) невозможен. so far, so good

Если в запросе появляется подсказка INDEX (форсирующая использование unusable индекс) — получаем ошибку и в Oracle 10.2, и в версии 11.2:

несмотря на значение параметра skip_unusable_indexes = TRUE (используется по умолчанию в 10.2 и в 11.2), который согласно документации:

Отключает сообщения об ошибках о том, что индексы или партиции индексов находятся в статусе UNUSABLE. Этот параметр допускает выполнение любых операций (inserts, deletes, updates, и selects) с таблицами, у которых имеются индексы или партиции индексов в статусе UNUSABLE

Т.е. подсказка INDEX при выполнении запроса играет более важную роль, чем параметр skip_unusable_indexes, что также отражается в трейсе 10053 оптимизатора:

Что само по себе достаточно странно: Oracle настойчиво пытается использовать индексный доступ в соответствии с пользовательской подсказкой, несмотря на «плохой» статус индекса (хорошо известный оптимизатору, что видно из выполнения запроса без подсказки) !

То же самое происходит при использовании в запросе подсказки RULE (т.е. давно официально не поддерживаемого RBO = Rule Based Optimizer), но только в Oracle 10.2:

Появление последней ошибки как-то можно объяснить: RBO давно не поддерживается, действует точно в соответствии с правилами, по которым индексный доступ считался более приоритетным, чем сканирование таблицы,… и т.д.

НО тот же запрос с подсказкой RULE в Oracle 11.2 выполняется без ошибок(!):

Кроме того, что в версии 11.2 rule based optimizer научился определять статус индекса, трейс события 10053 (т.е. трейс CBO, как я всегда полагал) показывает сформированную секцию OUTLINE с комментарием RBO_OUTLINE (последнее — только в случае установки параметра optimizer_mode=RULE на уровне сессии, без подсказки RULE в запросе)

В том же трейсе можно найти записи в секции Query Transformations — при этом большая часть трансформаций игнорируются по причине использования rule-based mode:

но не все — ORDER BY ELIMINATION (OBYE) не отвергается для RBO, хотя в рассматриваемом случае и не применяется:

Похоже, что механизм rule-based оптимизации был обновлён (и получил развитие) в Oracle 11g R2: «научился» определять состояние индексов, пишет 10053 трейс и, возможно, умеет использовать некоторые операции трансформации запросов (Query Transformations), ранее доступные только интеллигентному Cost-Based оптимизатору?

Управление схемами

Источник: сайт корпорации Oracle,
серия статей «Oracle Database 11g: The Top New Features for DBAs and Developers»
(«Oracle Database 11g: Новые возможности для администраторов и разработчиков»), статья 4
http://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html

При использовании новой функциональности управление объектами базы данных производится более эффективно, многие общие операции выполняются невероятно просто и быстро.

Oracle Database 11g включает в себя множество функций, которые не только делают работу простой, но в иных случаях некоторые трудоемкие и времязатратные процессы сокращаются практически до линейных операций. В этой статье рассказывается о некоторых из этих функций.

Опция ожидания в DDL (DDL Wait Option)

Джилл, администратор базы данных (АБД) в компании Acme Retailers, пытается добавить столбец TAX_CODE в таблицу SALES. Это довольно рутинная процедура, выполняемая следующим SQL-предложением:

Но вместо того, чтобы получить что-то вроде «Table altered» («Таблица изменена»), она получает:

Сообщение об ошибке говорит само за себя: таблица сейчас, вероятно, используется какой-то транзакцией, так что получить эксклюзивную блокировку на таблицу представляется почти невозможным. Конечно, строки таблицы блокированы не навсегда. Когда сессии завершатся, блокировки с её строк снимутся, но до этого ещё очень далеко. Тем временем другие сессии могут начать обновлять какие-либо другие строки этой же таблицы, и тем самым вероятность получить эксклюзивную блокировку таблицы практически исчезает. В обычной бизнес-среде периодически открывается временное окно для эксклюзивной блокировки таблицы, но АБД может не суметь выполнить команду ALTER именно в это время.

Конечно, Джилл просто может снова и снова вводить эту команду, пока не получает эксклюзивную блокировку или сходит с ума, что скорее всего наступит раньше. В Oracle Database 11g для Джилл есть лучшая альтернатива: опция ожидания в DDL (DDL Wait option). Джилл вводит:

Теперь, если в этой сессии DDL-предложение не получит эксклюзивную блокировку, оно не выдаст ошибку. Вместо этого DDL-предложение ждёт 10 секунд, в течение которых оно постоянно пытается повторно выполнить DDL — операцию до её успешного завершения или до истечения заданного времени, — что наступит раньше. Если же Джилл введёт:

предложение зависнет, но без ошибки. Тем самым вместо того, чтобы несколько раз пытаться врезаться в неуловимый интервал времени, когда доступна эксклюзивная блокировка, Джилл переадресует эти повторные попытки Oracle Database 11g, что похоже на программирование повторения телефонных вызовов на занятый номер.

Теперь Джилл настолько любит эту функцию, что она рекомендует её всем другим АБД. Все, кто сталкивается с этой проблемой занятости системы при изменении таблицы, считают эту новую функцию очень полезной. Более того, Джилл задается вопросом, можно ли задать такое поведение по умолчанию, чтобы не нужно было выдавать каждый раз предложение ALTER SESSION?

Да, это возможно. Если вы введёте предложение

то все сессии автоматически будут останавливаться на этот срок в течение DDL-операций. Как и любой другое предложение ALTER SYSTEM оно может быть перекрыто предложением ALTER SESSION

Добавление столбцов со значением по умолчанию
(Adding Columns with a Default Value)

Осчастливленная уже одной этой функцией Джилл обдумывает другую проблему, несколько связанной с первой. Она хочет добавить столбец TAX_CODE, но чтобы он не был NULL. Очевидно, когда добавляется ненулевой столбец в непустую таблицу, должно также указать значение по умолчанию ‘XX’. Поэтому Джилл пишет следующее SQL-предложение:

Но здесь она стопорится. Таблица SALES огромна, в ней около 400 миллионов строк. Джилл знает, что когда она введет это предложение, Oracle правильно добавит столбец, но обновит его значение ‘XX’ во всех строках перед возвращением управления. Обновление 400 миллионов строк не только займет очень много времени, но также потребует много операций с сегментами отката, породит большое количество записей журнала (redo log), а также потребует очень больших накладных расходов. Поэтому Джилл должна запросить остановку работы на "тихий период", чтобы сделать эти изменения. Но может быть Oracle Database 11g предложит лучший вариант?

Разумеется. SQL-предложение, показанное выше, не станет производить обновления всех записей таблицы. Это, скажем, не проблема для новых записей, в которых значение столбца автоматически устанавливается в ‘XX’, но если пользователь выбирает этот столбец из уже существующей записи, то выберется NULL, не так ли?

На самом деле не так. Когда пользователь выбирает столбец из существующей записи, Oracle получает значение по умолчанию из словаря данных и возвращает его пользователю. Тем самым убиваются два зайца: новый столбец можно определить как непустой и со значением по умолчанию, и до поры нет никаких расходов по части генерации записей redo и undo.

Виртуальные столбцы (Virtual Columns)

База данных компании Acme содержит таблицу SALES, которую вы видели ранее. Таблица имеет следующую структуру:

SALES_ID	NUMBER
CUST_ID	NUMBER
SALES_AMT	NUMBER

Некоторые пользователи хотят, чтобы был добавлен столбец SALE_CATEGORY, который определяет тип продажи: LOW, MEDIUM, HIGH или ULTRA в зависимости от количества в запросе продаж и клиентов. Этот столбец поможет им выявить записи для принятия соответствующих действий и выбора маршрутов для конкретных сотрудников. Логика значений в этом столбце такова:

Если sale_amt более чем:	И sale_amt меньше или равна:	Тогда значение sale_category:
0	1000	LOW
10001	100000	MEDIUM
100001	1000000	HIGH
1000001	Неограниченный	ULTRA

Хотя этот столбец является одним из важнейших требований бизнеса, команда разработчиков не хочет изменить код для включения необходимой логики. Конечно, можно добавить новый столбец в таблицу sale_category и написать триггер для заполнения столбца, используя правила, показанные выше, — довольно тривиальная задача. Но возникают проблемы с производительностью из-за переключения контекста из и в код триггера.

В Oracle Database 11g не нужно писать ни строчки кода в каком-либо триггере. Все, что нужно сделать, так это добавить виртуальный столбец. Виртуальные столбцы обеспечивают гибкость. Добавляемые столбцы передают смысл бизнеса без усложнения и снижения производительности.

Вот как нужно создать такую таблицу:

Примечание к строкам 6-7: столбец определяется как «generated always as» («всегда генерируется как»), ("всегда генерируется как"), то есть значения в столбце генерируются во время выполнения, а не хранятся как часть таблицы. Из этой фразы следует, что значение вычисляется в соответствующей фразе CASE. Далее в строке 15 фраза "virtual" ("виртуальный") подтверждает, что это виртуальный столбец. Теперь вставим несколько записей:

Значения всех виртуальных столбцов заполняются как обычно. Даже если этот столбец не хранится, к нему можно обратиться как к любому другому столбцу в таблице. Можно даже для него создать индекс.

В результате появится индекс, базирующийся на функции (function-based index).

По этому столбцу можно даже секционировать таблицу, как показано в статье Partitioning installment этой серии. Однако в этот столбец нельзя вводить значения. Если вы попытаетесь сделать это, то далеко не уедете:

Невидимые индексы (Invisible Indexes)

Часто ли вы задаетесь вопросом, действительно ли индекс полезен для пользовательских запросов? Он может быть выгоден для одного, но вреден для 10 других пользователей. Индексы, безусловно, негативно влияют на предложения INSERT, а также, возможно, на операции удаления (deletes) и обновления (updates) в зависимости от условия WHERE, которое включает в себя столбец в индексе.

В связи с этим актуален вопрос, если индекс используется на всех запросов, то, что происходит при исполнении запросов, если удален этот индекс? Конечно, вы сами можете удалить этот индекс и увидеть влияние на запросы, но это легче сказать, чем сделать. Что же делать, если индекс сделан именно для того, чтобы на деле помочь выполнению запросов? Вы должны восстановить использование индекса, но для этого его нужно воссоздать. А пока он полностью не воссоздан, никто не может его использовать. Пересоздание индекса также дорогостоящий процесс, он занимает много ресурсов базы данных, которым можно найти лучшее применение.

А вот если бы был вариант, чтобы индекс был неиспользуемым для некоторых запросов, но не затрагивая все другие запросы? Известная команда ALTER INDEX . UNUSABLE в данном случае (до Oracle Database 11g) не вариант, поскольку она обязательно воздействует на все DML-операции на этой таблице. Но теперь есть именно требуемое решение с помощью невидимых индексов. Проще говоря, индекс можно сделать "невидимым" для оптимизатора, и поэтому запрос не будет его использовать. Если же запрос хочет использовать индекс, он должен явно это указать, используя хинт.

Приведу пример. Допустим, есть таблица RES, для которой создан индекс, как показано ниже:

этот индекс можно найти, используя:

Теперь сделаем этот индекс невидимым (invisible):

показывает, что индекс не используется. А для того, чтобы оптимизатор снова использовал индекс, нужно его явно назвать в хинте-подсказке:

Как же здорово! Индекс снова используется оптимизатором.

Кроме этого, чтобы использовать невидимые индексы на уровне сессии, можно установить параметр:

Эта возможность очень полезна, когда вы не можете изменить код, например, в сторонних приложениях. При создании индекса можно в конец добавить фразу INVISIBLE, чтобы построить индекс, как невидимый для оптимизатора. Вы также можете видеть текущее значение индекса с помощью представления словаря данных USER_INDEXES.

Обратите внимание, что при перестроении этого индекса он снова станет видимым. И вы должны снова явно сделать его невидимым.

Итак, что именно делает этот индекс невидимым? Ну, он же не невидимый для пользователя. Он невидим только для оптимизатора. Регулярные операции с базами данных, такие как вставки, обновления и удаления строк будут продолжать обновлять индекс. Помните, что при использовании невидимых индексов нет прироста производительности за счет индекса, хотя вы оплачиваете стоимость DML-операций.

Таблицы только_для_чтения (Read-Only Tables)

Робин, разработчик хранилища данных Acme, озабочен классическими проблемами. Как часть ETL-процессов несколько таблиц обновляются с разной периодичностью. По бизнес-правилам при обновлении таблицы открыты для пользователей, даже если пользователи не имеют права их изменять. Таким образом, отмена DML-привилегий на эти таблицы для таких — пользователей не вариант.

Поэтому Робину нужно средство, которое действует как переключатель, включая и выключая возможность обновления таблицы. Реализация такой тривиально звучащей операции на самом деле довольно сложно. Что может сделать Робин?

Одним из вариантов является создание триггера на таблицу, который вызовет исключение при операциях INSERT, DELETE и UPDATE. Выполнение триггера, вызывающего переключение контекста, не хорошо для производительности. Другой вариант заключается в создании виртуальной частной базы данных (Virtual Private Database (VPD)), политика которой всегда содержит ложную строку, например, "1 = 2". Когда табличная VPD-политика использует эту функцию, она возвращает FALSE, и DML-предложение не выполнятся. Это может быть более производительно, чем использование триггера, но определенно менее желательно, так как пользователи увидят сообщение об ошибке, что "функция политики вернула ошибку".

Однако в Oracle Database 11g, есть гораздо лучший способ достижения этой цели. Все, что нужно — это сделать таблицу только для чтения, как показано ниже:

Теперь, когда пользователь пытается выдать DML, как показано ниже:

Oracle Database 11g сразу выдает ошибку:

Сообщение об ошибке не отражают операции по букве, но передает сообщение, как и предполагалось, без накладных расходов на курсор или VPD-политику.

Когда понадобится восстановить возможность обновления таблицы, вам нужно сделать её для read/write (чтение/запись), как показано ниже:

Теперь с DML-операциями не будет никаких проблем:

В то время как таблица находится в режиме read-only, запрещены только DML-операции, но можно выполнять все DDL-операции (создание индексов, управление секциями и так далее). Таким образом, это очень полезная функция для обслуживания таблиц. Вы можете сделать таблицу read-only, выполнить необходимые DDL-операции, а затем снова перевести её в статус read/write.

Чтобы посмотреть состояние таблицы, обратим внимание на столбец read_only в представлении dba_tables словаря данных.

Мелкодисперсное отслеживание зависимостей
(Fine-Grained Dependency Tracking)

Эту возможность лучше всего объяснить на примере. Рассмотрим таблицу TRANS, созданную как:

Пользователи не должны напрямую получать данные из этой таблицы, они получают их через представление VW_TRANS, созданное как показано ниже:

Теперь представление VW_TRANS зависит от таблицы TRANS. Вы можете проверить эту зависимость при помощи следующего запроса:

Как показано, статус представления VW_TRANS — VALID. Далее изменим базовую таблицу, например, добавим столбец:

Поскольку представление зависит от таблицы, которая была изменена, то это представление в Oracle Database 10g и предыдущих релизах было бы аннулировано. Можно проверить состояние зависимости и сейчас, используя показанный выше запрос:

Статус показывает, что оно INVALID (недействительное). Принципиально ничего не изменилось, поскольку в общем случае представление стало перманентно недействительным, но оно может быть легко подвергнуто повторной компиляции:

Итак, почему это представление недействительно? Ответ прост: когда изменятся родительский объект, дочерние объекты автоматически подпадают под пристально внимание, потому что что-то в них, возможно, потребуется изменить. Однако в этом случае изменением является добавление новых столбцов. Представление не использует этот столбец, так почему оно должно быть признано недействительным?

В Oracle Database 11g это не так. Зависимость по-прежнему установлена на TRANS, конечно, но статус не INVALID – он по-прежнему VALID!

Поскольку представление не инвалидное, то и все зависимые от этого представления объекты, например, другие представления, пакеты и процедуры, также не инвалидны. Это свойство устраивает огромное количество приложений, что, в свою очередь, повышает общую доступность всего стека. Не нужно стопорить программы, чтобы сделать некоторые изменения в базе данных.

Если был изменен столбец, используемый, например, в представлении TRANS_AMT, представление было бы признано недействительным. Это желательно, так как столбцы альтер-таблицы могут повлиять на представление.

Но высокая доступность не замыкается на одиночных представлениях и таблицах, вы нуждаетесь в них для других хранимых объектов, таких как процедуры и пакеты. Рассмотрим пакет показанный ниже:

Теперь предположим, что вы хотите написать функцию, которая увеличивает объем сделки на определенный процент. Эта функция использует пакет pkg_trans.

Если вы захотите проверить статус функции, она должна быть valid:

Предположим, вы хотите изменить пакет pkg_trans путем добавления новых процедур для обновления столбца vendor_name. Вот новое определение пакета:

После этого пакет перекомпилируется. Каким будет статус функции ADJUST? В Oracle Database 10g и ниже, функция, будучи зависимой, считается недействительной, как показывает её в статус:

Её легко перекомпилировать alter function . recompile;, но в Oracle Database 11g эта функция не будет считаться инвалидной (недействительной):

Это огромный шаг к понятию высокой доступности. Функция настройки не вызывает изменения части пакета pkg_trans, поэтому нет необходимости эту функцию признавать инвалидной, и это справедливо не только в Oracle Database 11g.

Но это не всегда так. Если пакет модифицирован таким образом, что новые суб-компоненты находится в его конце, как показано в приведенном выше примере, то зависимость хранимого кода не является инвалидной. Иначе будет, если суб-компонент добавляется в начало, как показано ниже:

Хранимый зависимый код, ADJUST, является недействительным, так как это имеет место в Oracle Database 10g и ниже. Это происходит потому, что новая процедура, вставляемая перед существующими, меняет номера слотов в пакете, тем самым вызывая инвалидность. Когда процедура была вставлена после выхода из них, номера слотов не изменились, просто был добавлен новый номер слота.

Вот некоторые общие рекомендации по снижению связанной зависимости инвалидизации.

Добавить компоненты, такие как функции и процедуры в конец пакета.
Распространенной причиной инвалидизации является изменение типов данных. Если вы не указывать имена столбцов, все столбцы, предполагается в порядке этой процедуры, и любое изменение может привести к аннулированию процедуры, даже если столбец не используется. Например, когда вы используете SELECT * FROM SomeTable, предполагается выборка всех столбцов таблицы. Избегайте конструкций типа SELECT *, типов данных, как SomeTable% ROWTYPE и insert into sometable values (. ), где не упоминается список столбцов.
Если возможно, используйте в хранимых кодах представления, а не таблицы. Это позволяет добавлять столбцы в таблицы, которые не используются в хранимых кодах. Поскольку представление не инвалидно, как показано выше, хранимый код также не будет считаться недействительным.
В случае синонимов используйте:

Кроме того, если вы использовали механизм оперативного онлайн-переопределения (online redefinition), то ранее вы, возможно, видели, что переопределение (redefinition) делает некоторые зависимые объекты инвалидными. Этого больше нет в Oracle Database 11g. Теперь онлайн-переопределения не инвалидизирует объекты, если указанные в них столбцы одного и того же имени и типа. Если столбец был удален (dropped) во время REDEF, но процедура не используют этот столбец, то процедура не признается инвалидной.

Примечание: В Oracle Database 11g Release 2 это описанное выше инвалидное поведение ведет себя по-другому. Чтобы продемонстрировать это, давайте создадим таблицу в базе данных Oracle Database 11g Release 1

Затем создадим триггер:

Проверим статус этого триггера:

Изменим таблицу следующим образом:

Теперь проверим статус триггера:

В Oracle Database 11g Release 1 триггер был признан инвалидным, хотя это не имело ничего общего с модификацией таблицы. Однако в Release 2 он не будет считаться инвалидным, поскольку триггер не зависит от модификации таблицы. (Это — новый столбец; существующий триггер никогда бы его не вызвал.)

Воссоздадим этот сценарий в Oracle Database 11g Release 2 и проверим статус:

Триггер остается в силе. История будет другой, когда что-то изменится так, что это повлияет на триггер. Возьмем, к примеру,

Таким образом, во многих случаях изменения таблиц, таких как добавление новых столбцов, независимые объекты будут признаны недействительными — создавая базы данных по-настоящему высокой доступности.

Внешние ключи по виртуальным столбцам (Только Release 2)
Foreign Keys on Virtual Columns (Release 2 Only)

В Oracle Database 11g Release 1 мы видели наличие двух новых очень важных и полезных функций. Одна из них виртуальные столбцы, описанные выше. Вторая — секционирование на основе ссылочной целостности (partitioning based on referential integrity constraints) — так называемое REF-секционирование (REF partitioning), которое позволяет секционировать дочерние таблицы-секции (partition child tables) как и родительскую таблицу, даже если в ней (child table) нет столбца секционирования.

Эти две возможности предлагают различные преимущества: виртуальные столбцы позволяют управлять таблицей, не тратя ресурсы на хранение столбцов или на изменения приложений, чтобы включить новые столбцы. А REF-секционирование позволяет разделить таблицы на секции так, чтобы воспользоваться разграничениями в отношениях родитель-ребенок (parent-child relationships) без добавления этих столбцов в child-таблицах. А что, если вы захотите воспользоваться преимуществами обеих этих возможностей на одних и тех же наборах таблиц? В Oracle Database 11g Release 2 это легко можно сделать.

Вот пример: таблица CUSTOMERS имеет два виртуальных столбца, CUST_ID, который также используется в качестве первичного ключа, и CATEGORY — столбец секционирования. Таблица SALES является дочерней по отношению к таблице CUSTOMERS, с которой соединяются по CUST_ID. Давайте посмотрим этот код в действии.

Давайте вставим несколько строк, заботясь лишь о том, чтобы не назначить определенное значение для виртуальных столбцов. Мы хотим, чтобы были созданы виртуальные столбцы.

Будут ли виртуальные столбцы правильно возвращать данные? Это мы сможем проверить, выбрав строки из таблицы:

Теперь, когда родительская таблица готова, давайте создадим дочернюю таблицу:

В 11g Release 1 этот код закончился бы ошибкой

В 11g Release 2 эта операция возможна, и предложение создаст дочернюю таблицу. Применяя эти возможности, можно использовать всю мощь двух довольно полезные функции в Oracle для построения всё лучших моделей данных.

IPv6 Форматирование в JDBC (Release 2 Only)
(IPv6 Formatting in JDBC (Release 2 Only)

За последние несколько лет IP-адресация подверглась капитальным изменениям. Традиционный способ адресования – это набор из четырех чисел, разделенных точками, например 192.168.1.100. Эта схема, называемая IPv4, является схемой 32-разрядной адресации, которая допускает сравнительно небольшое множество IP-адресов. При взрывоподобном росте спроса на IP-адреса для не только сайтов, но таких устройств, как IP-совместимые телефоны и PDA (personal digital assistant — персональный цифровой помощник), эта схема исчерпает свои IP-адреса в течение короткого времени. Для решения проблемы нового поколения IP-адресации была введена так называемая схема IPv6. Это 128-битная система, способная поддерживать гораздо большее множество адресов.

В Oracle Database 11g Release 2 можно сразу использовать схему IPv6. Приведем простой пример команды (в командной строки Linux), чтобы узнать адреса IPv6.

Обратите внимание на традиционную IP -адресацию (как показано в заголовке “inet addr ”): 10.14.104.253. Вы можете использовать схему адресации EZNAMES для подключения к базе данных под названием D112D1, которая по умолчанию работает через порт 1521:

Обратите внимание на выход команды Ifconfig. В дополнение к IPv4 вы можете увидеть схему адресации IPv6 (показан в столбце с заголовком "inet6 адрес"): fe80 :: 219:21 FF: febb: 9aa5. Вы можете использовать этот адрес вместо адреса IPv4. Вы должны заключить IPv6 в квадратные скобки.

Поддержка IPv6 не ограничивается SQL*Plus. Вы можете, как показано ниже, использовать IPv6 также в JDBC:

Конечно, можно использовать и IPv6, и IPv4 одновременно. Убедитесь только в том, что IPv6-адрес помещен в квадратных скобках.

Сегментов меньше, чем Объектов (только Release 2)
Segment-less Objects (Release 2 Only)

Рассмотрим ситуацию, когда сторонние приложения или даже ваше собственное приложение развертывается на нескольких тысячах таблиц. Каждая таблица имеет, по крайней мере, один сегмент, и даже если все они пусты, каждый сегмент занимают, по крайней мере, один экстент. В каждый момент времени многие из этих таблиц могут быть пустыми, а могут содержать записи. Поэтому не имеет смысла предварительно выделить всё пространство сразу же. Эта ситуация раздувает общий объем базы данных и увеличивает время установки приложения. Можно отложить создание таблиц, но развертывание зависимых объектов, таких как процедуры и представления не позволяет это сделать без ошибок.

В 11g Release 2 есть довольно элегантное решение. В этом релизе сегменты не создаются по умолчанию при создании таблицы, а [физически создаются только тогда] когда в таблицы вставляются первые данные. Давайте посмотрим это на примере:

Не существует сегмента для вновь созданной таблицы. Теперь вставим в таблицу строку:

Сегмент создается с начальным экстентом (initial extent). Это необратимый процесс. Экстент сохраняется, даже если происходит откат.

Но эту возможность не обязательно заявлять по умолчанию. Например, вы хотите, чтобы сегмент создавался при создании таблицы. Параметр deferred_segment_creation управляет такой возможностью. Для создания сегментов при создании таблицы надо установить этот параметр в значение FALSE. Им можно управлять даже на уровне сеанса:

После создания сегмента он сохраняется в базе. Если вы опустошаете (truncate) таблицу, сегмент не удаляется. Обратите внимание, что эта возможность не применима к LOB-сегментам, которые создаются независимо, даже если не создается таблица сегмента.

Однако если посмотреть на таблицу сегментов:

Вы видите, что сегмент создан не был.

В Release 1 отложенное создание сегментов работало только для несекционированных объектов. Ограничение для секционированных таблиц было снято в Release 2, так что теперь эта функциональность применяется и для секционированных объектов.

Надо сказать, что по-прежнему существует ряд ограничений для различных типов объектов. Они подробно описаны в справочном руководстве языка SQL.

Неиспользуемые индексы не требуют пространства (Только Release 2)
Unusable Indexes Do Not Consume Space (Release 2 Only)

Теперь, понимая преимущества отложенного создания сегмента, можно понадеяться на универсальность этого качества. Однако, после обращения к документации, становится очевидным, что нет никакого способа сделать отложенное создание сегментов для индексов.

Этому существует идеальное объяснение: как класс вторичных структур – таблица всегда создается раньше индекса – индексы просто следуют атрибутам таблицы. Если вы создаете пустую таблицу с отложенным созданием сегмента, для индекса также будет иметь место отложенное создание сегмента. Вы вставляете в таблицу первую запись, и вуаля, вы получите сегмент как для таблицы, так и сегмент(ы) индекса(ов).

Рассмотрим следующий пример, где мы создадим индекс на таблицу с данными (или когда отложенное создание сегмента была отключено).

Сегмент создан. Но какой смысл хранения этого блока данных в базе данных? Вы никаким образом не можете получить к нему доступ, вы не можете использовать его для восстановления или чего-либо другого, кроме как занимая пространство, — он бесполезен.

В Oracle Database 11g Release 2 существует неоценённая здесь возможность (и использует отложенное создание сегмента под одеялом). Но в этом выпуске, если вы делаете индекс unusable (непригодным для использования), исчезает соответствующая бесполезность сегмента:

При перестроении индекса (чтобы якобы начать использовать его), сегмент возникает:

Эта возможность особенно полезна при секционировании, когда можно для экономии места выборочно сделать индексы несуществующими. Давайте рассмотрим пример — таблица SALES из схемы SH.

Давайте проиндексируем одинаковые секции таблицы:

Возьмем конкретный индекс, например, SALES_CUST_BIX, и проверим и количество его секций, и сколько места они занимают:

Этот индекс разделен на много секций, вплоть до 1995 года. В обычных приложениях обращение к очень старым данным, загруженным, например, в 1995 году, случается довольно редко. Следовательно, и индекс такой секции используется редко, если и вообще когда-либо используется. Несмотря на это, он занимает значительное место. Если такая секция каким-то образом была бы удалена, то занимаемое её индексом пространство было бы освобождено. Однако отказаться от секции нельзя, поскольку необходимо, чтобы её данные были в наличии.

В Oracle Database 11g Release 2 существует довольно простое решение: привести индекс секции в неработоспособное (unusable) состояние, что заставит исчезнуть его сегмент, оставляя нетронутыми таблицы секций:

Примечание: более не существует сегмента SALES_1995. Сегмент был удален, поскольку индекс этой секции стал непригодным для использования. Если это сделать для многих старых разделов с несколькими индексами, то можно освободить много пространства без потери данных — старых или новых.

Но что произойдет, если секция сделается раздел непригодной, а какой-то пользователь запросит из нее данные. Будет ли это ошибкой? Давайте посмотрим на примере.

Вот план оптимизации для запроса, который обращается к секции, индекс который доступен:

Теперь выполним тот же запрос для секции, [индекс] который был сброшен:

Запрос выполнился правильно; оптимизатор не возвратил ошибку. Так как индекс раздела был недоступен, просто было выполнено полное сканирование [секции] таблицы.

Это очень полезная возможность для тех баз данных, которые юридически обязаны держать записи в течение длительного периода времени. Это обеспечивает минимум требуемой памяти хранения, экономя пространство и деньги.

Заключение

Как можно видеть, стали радикально проще не только ранее трудоемкие команды, но в некоторых случаях открылись совершенно новые возможности для проводимых изо дня в день операций.

За время своей карьеры я видел в СУБД Oracle много функциональных изменений, которые являлись и достопримечательностями, и поворотными пунктами в том, как делается бизнес. Описанные в этой статье возможности принадлежат к этой категории.

Oracle unusable index что это