Oracle Management Index (i) Oracle Management index

1, the creation of the syntax of the index:

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

On <schema>.<table_name>

(<column_name> | <expression> ASC | DESC,

<column_name> | <expression> ASC | DESC,...)

Tablespace <tablespace_name>

STORAGE <storage_settings>

LOGGING | Nologging

COMPUTE STATISTICS

nocompress | Compress<nn>

Nosort | REVERSE

PARTITION | GLOBAL partition<partition_setting>

Related instructions

1) UNIQUE | BITMAP: Specifies unique unique value index, BITMAP as bitmap index, omitted as B-tree index.

2) <column_name> | <expression> ASC | DESC: Multiple columns can be indexed together, when expression is a "function based index"

3) Tablespace: Specifies the table space in which the index is stored (more efficient when the index and the original table are not in one table space)

4) STORAGE: Can further set the storage parameters of the table space

5) LOGGING | Nologging: Whether to generate redo logs for indexes (use nologging for large tables to reduce space usage and increase efficiency)

6) COMPUTE STATISTICS: Collect statistics when creating new indexes

7) nocompress | COMPRESS<NN>: Whether to use key compression (use key compression to delete duplicate values that appear in a key column)

8) Nosort | Reverse:nosort to create an index in the same order as in the table, REVERSE to store index values in reverse order

9) PARTITION | Nopartition: You can partition the indexes you create on partitioned and unpartitioned tables

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

2, Index features:

First, you can guarantee the uniqueness of each row of data in a database table by creating a unique index.

Second, you can greatly speed up the retrieval of data, which is the main reason to create indexes.

Third, you can speed up the connection between tables and tables, especially in terms of realizing the referential integrity of the data.

Finally, when you use grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

In the process of querying, the optimization of the hidden device can be used to improve the performance of the system by using the index.

3, insufficient index:

First, it takes time to create indexes and maintain indexes, which increase as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if you want to establish a clustered index, then need more space.

Third, when the data in the table is added, deleted and modified, the index will be maintained dynamically, thus reducing the data maintenance speed.

4. The characteristics of the indexed column should be built:

1 The speed of searching can be speeded up on the column that needs to be searched frequently;

2 enforces the uniqueness of the column and arranges the structure of the data in the organization table on the column as the primary key;

3 often used in connected columns, these columns are mainly some foreign keys, can speed up the connection speed;

4 Create indexes on columns that often need to be searched according to scope, because the index is already sorted and its specified range is contiguous;

5 Create indexes on columns that often need to be sorted, because the indexes are sorted so that the query can use the sort of index to speed up the sorting query time;

6. Create indexes on columns that are frequently used in the WHERE clause to speed up the judgment of the condition.

5, should not build the characteristics of indexed columns:

First, you should not create indexes for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, they are indexed or indexed and do not increase the query speed. On the contrary, because of the addition of indexes, it reduces the maintenance speed of the system and increases the space requirement.

Second, you should not add indexes to columns that have very few data values. This is because, because of the low values of these columns, such as the gender column of the personnel table, the result set's data rows account for a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Adding indexes does not significantly speed up the retrieval.

Third, columns that are defined as BLOB data types should not be added to the index. This is because these columns have either a large amount of data or a very small number of values.

Four, you should not create an index when the modification performance is far greater than the retrieval performance. This is because the modification performance and retrieval performance are contradictory. When indexing is added, retrieval performance is improved, but modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when the modification performance is far greater than the retrieval performance.