The basic method of indexing in Oracle database explain _oracle

How do I build the best index?

1. To create an index explicitly

CREATE INDEX index_name on table_name (field_name)

tablespace tablespace_name pctfree

5

Initrans 2

Maxtrans 255

Storage

(

minextents 1

maxextents 16382 pctincrease

0

);

2. Create a function based index

Commonly used with upper, LOWER, To_char (date) and other functions of the classification, example:

CREATE INDEX Idx_func on EMP (UPPER (ename)) tablespace tablespace_name;

3. Create Bitmap Index

When indexing a column with a small base and a relatively stable base, you should first consider the bitmap index, for example:

Create Bitmap index Idx_bitm on class (Classno) tablespace tablespace_name;

4. Create a unique index explicitly

You can create a unique index by using the Create UNIQUE index statement, for example:

Create unique index DEPT_UNIQUE_IDX on dept (DEPT_NO) tablespace idx_1;

5, create the index related to the constraint

An index associated with a unique and primary KEY constraint can be used with the using index clause, for example:

ALTER TABLE table_name

ADD constraint Pk_primary_keyname primary key (Field_name)

using index tablespace Tablespace_name;

How do I create a partial-area index?

1) The base table must be a partitioned table

2 The number of partitions is the same as the underlying table

3 The number of sub partitions per index partition is the same as the corresponding base table partition

4 The index entry of the row in the self partition of the underlying table is stored in the corresponding self partition of the index, for example

Create INDEX Tg_cdr04_serv_id_idx on tg_cdr04 (serv_id)

Pctfree 5

tablespace tbs_ak01_idx Storage

(

Maxextents 32768

pctincrease 0

freelists 1

freelist Groups 1

)

local

/

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

The underlying table can be a global table and a partitioned table

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 do I rebuild an existing index?

The current moment of rebuilding an existing index does not affect the query

Rebuilding an index can remove additional blocks of data

Improve index query efficiency

Alter index idx_name rebuild nologging;

For partitioned indexes

ALTER index IDX_NAME rebuild partition partition_name nologging;

Why did you delete the index?

1 indexes that are no longer needed

2 The index does not provide the desired performance improvement for queries published by its related tables

3 Application does not use this index to query data

4) The index is invalid and must be deleted before rebuilding

5 The index has become too broken and must be deleted before rebuilding

Statement:

Drop index idx_name;

Drop index idx_name partition partition_name;

What is the cost of establishing an index?

When the underlying table is maintained, the system maintains the index at the same time, and unreasonable indexes will severely affect system resources.

The main performance is in CPU and I/O.

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

Suggestions on creating an index
Although it is said that indexing in a table does not affect the use of the Oracle database, it does not affect the use of database statements. It's like even if the dictionary doesn't have a catalog, the user can still use it. However, if the dictionary does not have a catalogue, then it is conceivable that the user to check an item, it has to go through the whole dictionary. The same is true for databases. If the relevant index is not established, the database will have to query the entire table when querying records. When there are more records in the table, the query efficiency will be very low. Therefore, a suitable index is a good tool to improve the efficiency of database operation.

However, it is not that the more indexes on the table the better. . Therefore, in the database design process, still need to select some suitable indexes for the table. Ningquewulan, this is a standard to follow when indexing is established. In theory, although a table can set an infinite index. However, the database administrator needs to know that the more indexes in the table, the greater the overhead required to maintain the index. The database system needs to update all indexes whenever there are additions, deletions, and updates to the records in the datasheet. So the index in the database table is definitely not the more. Specifically, in the establishment of the index, the author has the following suggestions.

Recommendation one: In a small base of the field to be good at using bitmap indexing.

Cardinality is a basic definition in a bitmap index, which is a value that is not duplicated in the contents of a field in a database table. such as in the Employee Information table of the gender field, usually only male and female two values, so, its base is 2; Marital Status field, then it is only married, unmarried, divorced three states, its base is 3; The national list is also limited to a few values.

Using bitmap indexing can improve query efficiency for fields where you want to query for small cardinality, such as if you now want to find all women with marital status of "married". This is mainly because standard indexes are implemented by saving sorted indexed columns and corresponding ROWID in the index. If we set up a standard index on a column with a small base, it returns a large number of records.

When we create a bitmap index, the entire table is scanned at Oracle, and a bitmap is created for each value of the indexed column. If the content is the same, the bitmap is represented by an identical number. At this point, if the base of this field is relatively small, then if you need to implement the query for the entire field, the efficiency will be very high. Because at this point, the database as long as the number of bitmaps in the same content to find.

In addition to the fact that a column base in a datasheet is relatively small, bitmap indexing is often recommended in some special cases. The most common scenario is that, in the where constraint, bitmap indexing is also recommended if we use the and or or conditions more than once. Because when a query drinks some columns that have a bitmap index deployed, these bitmaps can be easily combined with and or operator operations to quickly identify the records the user needs.

However, it should be noted here that using a bitmap index provides a high rate of efficiency when the operator is not included in the conditional statement. In general, bitmap indexes are advantageous only when the and or or operators are available. If the user uses greater-than or not-equal numbers as the constraints in the conditional statement, then the standard index is often used to have a greater advantage.

Therefore, the author in the database setup, generally only in three kinds of circumstances to use the bitmap index. One is that the cardinality of the column is relatively small, and it is possible to find the relevant records based on the contents of the fields, and the second is when the and OR operator is used in a conditional statement. In addition to these two cases, it is best to adopt other appropriate indexes. The third scenario is that you need to use NULL as a constraint on the query. Because standard queries generally ignore all null value columns. In other words, if you need to query the "all without ID number" of the employee's information, the standard index can not play a faster speed of inquiry. At this point, you need to use a bitmap index. Because the bitmap index records related null value column information.

Recommendation two: Some restrictions on creating an index.

It does not say that the more indexes the table or column establishes, the better. Conversely, the more indexes are built, the more it sometimes affects the overall performance of the database operation. Therefore, there are still some restrictions when indexing is established.

One is not to index some tables with fewer records. In an application system design, such as the design of an ERP system database, although it has thousands of tables. However, not every table has a large number of records. On the contrary, there are nearly half of the data tables, which may not store more than hundreds of data. such as Employee login account password table, Enterprise Department information table and so on. For tables with fewer records, it's best not to index them. Do not create an index on a table or on a field.

Second, if the contents of the table is relatively large, but the table is basically not query, then only need to index on the table, and do not need to establish an index on the field. As now in the ERP system, there is a table is "ad_table". It stores information about the related tables in this database. This form is only used when the database is designed. Therefore, although the record in this table is more, but because users use less, so there is generally no need for this table to establish a column level index. Instead, the table index is used directly instead.

The third is in some null fields, according to the actual situation to determine whether to establish an index. If there is a form for personnel files, there are two fields, namely "ID number" and "area". Sometimes for some reason, the enterprise needs all employees to register their ID number in the system so as to facilitate them to do payroll cards, social insurance and so on. So personnel management may require a regular query system to see if there is no ID number of staff information. At this point, you need to use the condition "is NULL" to query the records we need. Therefore, in order to improve query efficiency, if a record may be empty, and often need to query with null conditions, it is best to add an index to this field, and it is best to establish a bitmap index. Conversely, if you might use NULL as a constraint for a query, but not many times, it is not necessary to index it.

Recommendation three: Index design of multiple table join query.

If there is a personnel management system now. The HR manager wants to know about the employee's social security payments. He needs to know the employee's name, title, the nature of the household registration (peasant account and resident account fee is not the same), pay the situation and so on. However, this information is contained in a different table. Because in order to improve the performance of the database, the stored in the table may be only some ordinal, rather than the specific content. In the Social Security statement, the employee's corresponding number is stored, not the employee's name. Therefore, in order to get this report, you may need to relate the basic staff information table, the company organization chart and other forms to be able to query the user needs.

To do this, you need to use the join statement to associate the tables. In order to improve the query efficiency of the database, the fields that are used to correlate are best indexed. This can significantly improve the speed of the query.

Recommendation IV: Find a balance between the update speed of the table and the speed of the query.

As we all know, the index itself does not affect the use of the database, its main purpose is to improve the query efficiency of the database. However, the indexes are updated when the data in the database table is updated, including the addition of records, deletions, changes, and so on.

Obviously, indexing can improve query speed. However, it can also have an adverse effect on the update operations of some tables. The more indexes you create in a table, the greater the negative impact. Therefore, when setting up the index, the database administrator needs to pay attention to the need for a balanced point between the two.

According to the general theory, when a table most of the query, update relative to compare, it is more to use index. Conversely, when a table record update is dominant, the query is relatively small, do not establish too many indexes, to avoid the updated speed of poor adversely affect.

In actual work, if a table is frequently called by the view, it is best to set more indexes. When you select the database index, you can refer to the above four points of advice, I believe that the above four points of advice, we can certainly choose a suitable index type.