Oracle Performance Analysis 7: Creating an Index

When creating an index, we often want to estimate the size of the index to assess the impact on the existing project environment, and we also want the process of creating an index to minimize the impact of the project environment we are executing and to see the status of the index.

Estimated Index size

The best way to estimate the size of an index is to create it in a test environment, preferably including a complete project environment data, or simply to extrapolate the full index size through some data.
Assuming that the test environment cannot be built, Oracle provides stored procedure dbms_space. Create_index_cost to estimate the size of the index, here is a sample:

DECLARE  used_bytes number  (ten);  Alloc_bytes number (); Begin  Dbms_stats.gather_table_stats (user, ' historyalarm ');  Dbms_space.create_index_cost (' CREATE index idx_historyalarm on Historyalarm (Position1) ',                               used_bytes      = Used_bytes,                               alloc_bytes     = alloc_bytes);  Dbms_output.put_line (' used_bytes: ' | | used_bytes);  Dbms_output.put_line (' alloc_bytes: ' | | alloc_bytes); end;

When calculating the index size, you need to collect the statistics of the table first, because Oracle is based on the data information of the table, the following is the result of the output:

used_bytes:151994511alloc_bytes:251658240

Create an index

After estimating the size of the index, assuming there is no problem, you can start to actually create the index:

Create INDEX Idx_historyalarm on Historyalarm (position1) tablespace Uep4x_fm_index

Indexing an index creates an exclusive DDL lock on the table (Exclusive DDL Lock), which prevents other sessions from getting their own DDL Lock or TM (DML) lock, which means that you can query a table during the creation of the index, but you cannot alter the table in any way. This causes problems when the index-creation operation is implemented in the project environment, so Oracle Enterprise Edition provides a way to create an index online:

Create INDEX Idx_historyalarm on Historyalarm (position1) tablespace uep4x_fm_index Online

Online will change the process of creating indexes in detail, and Oracle will not add an exclusive DDL lock to prevent data churn, instead trying to get a low-level (Mode 2) TM lock on the table, which will effectively prevent other DDL (data definition language, Includes Create, ALTER, drop, and so on), but agrees that DML (data manipulation language, including Select, UPDATE, INSERT, DELETE) operations are working properly. Within Oracle, Oracle puts DML operations made during the creation of an index into a transient table, and then synchronizes the changes made by the DML operation to the new index when the index operation is complete. This effectively overcomes the problems that are implemented in the project environment.

View index Information

The ability to view the index after a successful creation is done in the above way:

SELECT * from user_indexes where index_name = Upper (' idx_historyalarm ')

The actual amount of space occupied can be displayed in the following ways:

Select bytes from user_segments where segment_name = Upper (' idx_historyalarm ')

The following is the output, the estimated amount of space allocated bytes is slightly less than the actual usage:

BYTES--------------------------293601280

As the record is inserted into the table, the index is added, and the index size monitoring ensures that there is enough disk space to accommodate future data-adding requirements.

Oracle Performance Analysis 7: Creating an Index