Oracle Mass Data Processing

In Oracle databases, indexes can be divided into three types based on different indexing mechanisms.

1. B-Tree index

Application Scenario: OLTP is applicable to B-Tree indexes for fields with low key-value repetition rate. When the key-value repetition rate is very high, the efficiency of the B-Tree index will be very inefficient.

By default, the B-Tree index is added to the primary key in Oracle. Use B-Tree indexes in Oracle's primary key and uniqueness constraints

B-Tree indexes can be divided into: unique index, composite index, reverse key index, and function-based index.

Unique key index: 1. Unique key index ensures no duplicate values in the defined index column. 2. Oracle automatically creates a unique key index in the primary key column of the table. 3. Use the create unique index statement to CREATE a UNIQUE key INDEX.

Composite Index: 1. Composite Index created on multiple columns of the table. 2. The column order in the index is arbitrary. 3. If the WHERE clause of an SQL statement references all or most columns of the composite index, the retrieval speed can be improved.

Reverse key index: 1. Reverse key index reverses each byte of the index column key value. 2. Normally, the value built on the column is continuously added to the value, so that the data can be evenly distributed across the entire index. 3. Use the REVERSE keyword during creation.

Function-based indexes: 1. Indexes created based on functions or expressions on one or more columns. 2. Aggregate functions cannot appear in expressions. 3. You cannot create a column of the LOB type. 4. You must have the query rewrite permission during creation.

2. Bitmap index

Application Scenario: 1) It is suitable for processing fields with a high repetition rate. 2) It saves more space than B-Tree indexes. 3) OLAP systems are frequently used. When the values of a certain (several) Field in a table have a large number of duplicates, for example, the field is country, region, or gender (in the world, or the field is other identification information. 4) queries of specific types have high performance. For example, to perform the count operation on a result set, especially in the condition section of an SQL statement, there is a query method for the logical "and", "or" operation between many fields.

Disadvantage: 1. Index columns are not suitable for frequent DML operations. 2. Low Value repetition rate is not suitable for use. 3. Bitmap indexes are suitable for use in data warehouses and are not frequently used in OLTP.

Create bitmap index index_name on table_name (column_name );

3. Text index

Application Scenario: it belongs to the category of data warehouse and is not widely used in OLTP systems. The advantage of full-text indexing is that you can quickly search for words.

Disadvantage: Full-text indexes occupy a large amount of space.

Full-text indexes of the context type cannot synchronize data and indexes without being based on transactions.

Next article: Oracle massive data processing-parallel

1 2 Next Page

[Content navigation]
Page 1: index selection	Page 1: Parallel