Basic methods for creating indexes in Oracle databases, oracle Indexes

Basic methods for creating indexes in Oracle databases, oracle Indexes

How to create the best index?

1. Create an index explicitly

create index index_name on table_name(field_name)tablespace tablespace_namepctfree 5initrans 2maxtrans 255storage(minextents 1maxextents 16382pctincrease 0);

2. Create a function-based index

It is commonly used in functions such as UPPER, LOWER, and TO_CHAR (date). For example:

create index idx_func on emp(UPPER(ename)) tablespace tablespace_name;

3. Create a bitmap Index

When you create an index for a column with a small base and a relatively stable base, you must first consider the bitmap index. For example:

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

4. explicitly create a unique index

You can use the create unique index statement to create a unique index. For example:

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

5. create constraints-related indexes

You can use the using index statement to create indexes related to the unique and primary key constraints. For example:

alter table table_nameadd constraint PK_primary_keyname primary key(field_name)using index tablespace tablespace_name;

How to create a local partition index?

1) The basic table must be a partition table.

2) The number of partitions is the same as that of the base table.

3) The number of subpartitions in each index partition is the same as that in the corresponding basic table partition.

4) The index items of rows in the Self-partition of the base table are stored in the corresponding self-partition of the index, for example

create index TG_CDR04_SERV_ID_IDX on TG_CDR04(SERV_ID)Pctfree 5Tablespace TBS_AK01_IDXStorage(MaxExtents 32768PctIncrease 0FreeLists 1FreeList Groups 1)local/

How do I create a global index for a range partition?

Basic Tables can be Global tables and partition tables.

create index idx_start_date on tg_cdr01(start_date)global partition by range(start_date)(partition p01_idx vlaues less than ('0106')partition p01_idx vlaues less than ('0111')...partition p01_idx vlaues less than ('0401'))/

How to reconstruct an existing index?

Rebuilding an existing index at the current time does not affect queries.

Re-indexing can delete additional data blocks

Improves index query efficiency

alter index idx_name rebuild nologging;

For partition Indexes

alter index idx_name rebuild partition partition_name nologging;

Why is the index deleted?

1) indexes no longer needed

2) The index does not provide the expected performance improvement for the queries published on the relevant tables.

3) The application does not use this index to query data.

4) The index is invalid and must be deleted before reconstruction.

5) The index has become too broken and must be deleted before reconstruction.

Statement:

drop index idx_name;drop index idx_name partition partition_name;

What is the cost of creating an index?

During basic table maintenance, the system must maintain indexes at the same time. unreasonable indexes will seriously affect system resources,

It is mainly manifested in CPU and I/O.

Inserting, updating, and deleting data results in a large number of db file sequential read lock waits.

Suggestions for creating Indexes
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 there is a change in the number of records added, deleted, or updated in the data table. 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.

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 search for "female" whose marital status is "married", 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 value greater than or not equal to the number as the limit condition in the Condition Statement, the use of standard indexes has 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 need to query the information of all employees without an ID card number, the standard index does not accelerate the query speed. In this case, bitmap indexes are used. Because Bitmap indexes record related NULL value columns.

Suggestion 2: some restrictions on creating an index.

Not to mention, the more indexes a table or column creates, the better. On the contrary, the more indexes you create, sometimes it will affect the overall performance of the database. Therefore, there are still some restrictions when creating an index.

First, do not index tables with few records. When designing an application system, for example, designing an ERP system database, it has thousands of tables. However, not every table has a large number of records. On the contrary, there are about half of data tables, and it may not store more than data records. Such as the employee Logon account password table and Enterprise Department information table. For tables with few records, we recommend that you do not index them. Do not create indexes for tables or fields.

Second, if the content in the table is relatively large, but this table is basically not very difficult to query, you only need to create an index on the table; instead, you do not need to create an index on the field. For example, in the ERP system, there is a table named "AD_Table ". It stores information about related tables in the database. This table is used only when the database is designed. Therefore, although there are many records in this table, there is usually no need to create a column-level index for this table because users use less. Instead of using Table indexes.

Third, in some NULL fields, you must determine whether to create an index based on the actual situation. For example, there is a personnel file table with two fields on it: "ID card number" and "region ". Sometimes, for some reason, the company requires all employees to register their ID card numbers in the system to facilitate their payroll card and social insurance. Therefore, personnel management may need to query the system frequently to see if there is any employee information without an ID card number. In this case, we need to use the condition "is null" to query the records we need. Therefore, to improve the query efficiency, if a record may be empty and needs to be queried with NULL as the condition, it is best to add an index for this field and create a bitmap index. On the contrary, although the condition NULL may be used as the limit statement for queries, if not many queries are used, there is no need to create an index for them.

Recommendation 3: Index Design for multi-table join queries.

For example, there is a human affairs management system. The personnel ideally know the social security payment of employees. He needs to know the employee's name, position, household registration nature (the farmer's household registration fee is different from the resident's household registration fee), the payment situation, and so on. However, the information is contained in different tables. To improve the database performance, some sequence numbers may be stored in the table, rather than the specific content. For example, in the Social Security table, the employee ID is stored, rather than the employee name. Therefore, to obtain this report, you may need to associate the basic employee information table, company organization structure table, and other tables to query the content required by the user.

Therefore, we need to use Join statements to associate these tables. To improve the query efficiency of the database, it is best to create indexes for the fields used for association. This can significantly improve the query speed.

Recommendation 4: find a balance between the table update speed and the query speed.

As we all know, indexes do not affect the use of databases. They are mainly used to improve the query efficiency of databases. However, when the data in the database table is updated, including the addition, deletion, and change of records, all indexes that are existing are updated.

Obviously, indexes can increase the query speed. However, it will also have a negative impact on some table update operations. The more indexes you create in a table, the greater the negative impact. Therefore, when setting indexes, the database administrator must note that a balance point is required between the two.

According to the general theory, when most of a table is used for query and update, more indexes are required. On the contrary, when a table has fewer records and fewer queries, do not create too many indexes to avoid adverse effects on the update speed.

In practice, if a table is frequently called by a view, it is better to set more indexes. When selecting a database index, you can refer to the four suggestions given above. I believe that you have mastered the four suggestions above and will be able to select an appropriate index type.

Articles you may be interested in:

• Summary of Oracle debate on re-Indexing
• Analysis of five index access methods in Oracle CBO optimization mode
• Oracle index introduction (graphic explanation)
• How to place tables and indexes in different tablespaces in Oracle
• Summary of Oracle indexes (B * tree and Bitmap)
• Summary of differences between primary keys, indexes, and pages of Oracle and Mysql
• Oracle indexes cannot be parsed in Depth
• Create reasonable database indexes in Oracle databases
• Oracle can easily obtain DDL statements for table creation and indexing.
• How to Create a binary file index in Oracle
• Oracle database index Maintenance