Oracle Performance Analysis 8: Creating indexes and oracle performance indexes

Oracle Performance Analysis 8: Creating indexes and oracle performance indexes

When creating an index, we often want to estimate the index size to evaluate the impact on the existing engineering environment. We also hope that the index creation process will minimize the impact on the running engineering environment, you can also view the index status.

Estimate index size

The best way to estimate the index size is to create it in the test environment. The test environment should contain the complete engineering environment data. Otherwise, only part of the data can be used to estimate the complete index size.
If you cannot build a test environment, Oracle provides the Stored Procedure DBMS_SPACE.CREATE_INDEX_COST to estimate the index size. The following is an example:

declare  used_bytes  number(10);  alloc_bytes number(10);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 statistical information of the table first, Because Oracle calculates the data information of the table. The output result is as follows:

used_bytes : 151994511alloc_bytes : 251658240

Create an index

After estimating the index size, if there is no problem, you can start creating an index:

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index

The Exclusive DDL lock (Exclusive DDL lock) is applied to the index created, which prevents other sessions from getting their own DDL lock or TM (DML) lock, that is, you can query a table during index creation, but you cannot modify the table in any way. As a result, the index creation operation may cause problems in the project environment. Therefore, Oracle Enterprise Edition provides the online index creation method:

create index idx_historyalarm on historyalarm(position1) tablespace uep4x_fm_index online

Online will change the specific index creation process. Oracle will not add an exclusive DDL lock to prevent data modification. Instead, it tries to get a low-level (mode 2) tmlock on the table, this effectively prevents other DDL (data definition language, including CREATE, ALTER, and DROP) operations, but allows DML (data manipulation language, including SELECT, UPDATE, INSERT, and DELETE) the operation is normal. In Oracle, the DML operation performed during index creation is placed in a temporary table. After the index creation operation is complete, the modifications made by the DML operation are synchronized to the new index. This effectively solves the problem of implementation in the engineering environment.

View index information

You can view the created index in the above way:

select * from user_indexes where index_name = upper('idx_historyalarm')

You can use the following method to display the actual amount of space occupied:

select bytes from user_segments where segment_name = upper('idx_historyalarm')

The following is the output result. The estimation of the number of space allocation bytes is slightly smaller than the actual usage:

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

As the record is inserted into the table, the index will increase, and the index size monitoring can ensure sufficient disk space to meet the needs of increasing data in the future.

Can a field in Oracle be used for index after a joint index is created for a table?

1. The table's primary key and foreign key must have an index;
2. Indexes should be available for tables with more than 300 of the data volume;
3. For tables that are often connected to other tables, indexes should be created on the connection fields;
4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;
5. The index should be built on highly selective fields;
6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields;
7. The establishment of a composite index requires careful analysis. Try to replace it with a single field index:
A. correctly select the primary column field in the composite index, which is generally A highly selective field;
B. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries? If yes, a composite index can be created; otherwise, a single field index is considered;
C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;
D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;
E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;
8. Do not create too many indexes for tables that frequently perform data operations;
9. Delete useless indexes to avoid negative impact on the execution plan;
The above are some general judgment bases for indexing. In a word, the establishment of indexes must be careful. The necessity of each index should be carefully analyzed and a basis should be established. Because too many indexes and inadequate or incorrect indexes have no benefits for performance: Each index created on a table increases storage overhead, indexes also increase processing overhead for insert, delete, and update operations. In addition, too many composite indexes generally have no value when there is a single-field index. On the contrary, they also reduce the performance when data is added and deleted, especially for frequently updated tables, the negative impact is greater.

Look at this and you will understand.

It is better to create an index for an oracle database.

The index is like a dictionary directory. With the dictionary directory, we can quickly find the entries we need. The same is true for databases. With the Oracle database index, related statements can quickly locate the record location without having to locate the entire table.
Whether to create an index in a table does not affect the use of the Oracle database or the use of database statements. It seems that even if the dictionary does not have a directory, you can still use it. However, if the dictionary does not have a directory, you can imagine that if you want to query an entry, you have to go through the entire dictionary. The same is true for databases. If no index is created, the database has to query the entire table when querying records. When there are many records in the table, the query efficiency will be very low. Therefore, an appropriate index is a good tool to improve the efficiency of database operation.
However, it does not mean that the more indexes on the table, the better. . Therefore, in the database design process, you still need to select some suitable indexes for the table. This is a standard for indexing. Theoretically, an infinite index can be set for a table. However, the database administrator needs to know that the more indexes in a table, the higher the overhead required for index maintenance. All indexes must be updated in the database system whenever the records in a data table are added, deleted, or changed. Therefore, indexes in database tables are definitely not more beneficial. Specifically, in terms of index creation, I have the following suggestions for you.
Recommendation 1: Use Bitmap indexes for fields with a small base.
Create bitmap index index_name on table_name (column_name );
The base number is a basic definition in the bitmap index. It refers to a non-repeated value in the content of a field in the database table. For example, the gender field in the employee information table generally has only two values: Male and female. Therefore, the base number is 2. For the Marital Status field, there are only three States: Married, unmarried, and divorced, the base number is 3, and there are only a limited number of values in the national list.
For fields with a small base number to be queried, if you want to find all married women, bitmap indexes can improve the query efficiency. This is mainly because standard indexes are implemented by saving sorted index columns and corresponding rowids in indexes. If we create a standard index on a column with a small base, a large number of records will be returned.
When creating a bitmap index, we will scan the entire table in Oracle and create a bitmap for each value in the index column. If the content is the same, the in-place graph is represented by an identical number. In this case, if the base of this field is small, the efficiency will be very high if you need to query the entire field. Because at this time, the database only needs to find out the same numbers in the bitmap.
In addition to Bitmap indexes when the base of a column in a data table is small, we recommend Bitmap indexes in some special situations. The most common condition is that Bitmap indexes are also recommended if we use the and or condition multiple times in the Where condition. Because when a query consumes some columns with bitmap indexes, these bitmaps can be easily combined with the AND Or operator to quickly find the records you need.
However, it is important to note that Bitmap indexes are not used when conditional statements contain operators. In general, bitmap indexes are advantageous only when the and or operator is used. If the user uses a greater than or not equal to the number as the limit condition in the Condition Statement, the standard index is often used to have a greater advantage.
Therefore, in database settings, bitmap indexes are generally used only in three cases. First, the column base is relatively small, AND you may need to find related records based on the content of these fields; second, when the and or operator is used in the Condition Statement. In addition to these two cases, it is best to use other suitable indexes. The third case is that NULL is used as the query restriction. Because standard queries generally ignore all NULL value columns. That is to say, if you want to query the information of all employees without ID card numbers... the remaining full text>