Oracle Performance Analysis 8: Creating an Index

When creating an index, we often want to be able to estimate the size of the index to assess the impact on an existing engineering environment, and we want to create an index that minimizes the impact of the engineering environment we're running and can 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 with complete engineering environment data, or only part of the data to calculate the full index size.
If a test environment cannot be built, Oracle provides stored procedure dbms_space. Create_index_cost to estimate the size of the index, here is an example:

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 table statistics 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 index size, if there is no problem, you can start to actually create the index:

Create INDEX Idx_historyalarm on Historyalarm (position1) tablespace Uep4x_fm_index

Indexes that are indexed create an exclusive DDL lock on the table (Exclusive DDL Lock), which prevents other sessions from getting their own DDL or TM (DML) locks, which means that you can query a table during the creation of the index, but you cannot modify the table in any way. This causes problems when the index-creation operation is implemented in the engineering 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 an index specifically, Oracle will not add an exclusive DDL lock to prevent data modification, 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 allows DML (data manipulation language, including Select, UPDATE, INSERT, DELETE) operations to work properly. Within Oracle, Oracle places DML operations made during the creation of indexes into a temporary table, and then synchronizes the changes made by the DML operation to the new index when the index operation is completed. This effectively solves the problem of implementation in the engineering environment.

View index Information

You can see the index after the successful creation in the way above:

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

The actual amount of space occupied can be shown in the following way:

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

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

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

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

Oracle Performance Analysis 8: Creating an Index