Shulou(Shulou.com)06/01 Report--

I. Affairs

1. Introduction

Transaction is the core of data processing and a logical unit of the business, which can ensure that all operations on the data are either successful or fail. DBMS coordinates the concurrent behavior of users through transaction management and reduces conflicts when users access resources.

1) Show commit: when a transaction encounters a COMMIT instruction, it ends the transaction and permanently saves the data for all changes.

2) Show rollback: when a transaction encounters a ROLLBACK instruction, it will also end the execution of the transaction, but at this time it rolls back all changed data to the original value at the beginning of the transaction, that is, cancels the change, and the data remains unchanged.

3) DDL statement: once the user executes the DDL (data definition language, such as create,drop, etc.) statement, all previous DML (data manipulation language) operations are committed as a transaction, which is called implicit commit.

4) normal termination program: if the oracle database application ends normally, if you use the sqlplus tool to change the data and exit the program (exit) normally, oracle automatically commits the transaction.

5) abnormal termination of the program: when the program crashes or terminates unexpectedly, all data changes are rolled back, which becomes an implicit rollback.

2. Characteristics of the transaction

Transactions have four features, abbreviated as ACID features.

1) principle: take the transfer operation as an example, the decrease of the balance of the transfer account and the increase of the transfer balance are two DML statements, but they must be treated as an indivisible and complete operation. Either succeed at the same time or fail at the same time, it is obviously unacceptable to transfer out without turning in.

2) consistency: whether before, during or after a transaction, the database is always in a consistent state. For example: before the transfer is 2000 and 1000 respectively, the total amount is 3000, after 300 is 1700 and 1300 respectively, the total amount is still 3000. It's called consistency. Inconsistency is that the total amount queried at some point is not 3000.

3) isolation: in a certain period of time, there must be a lot of people transferring money, and everyone's transfer is in their own transaction, so there will be a lot of things in a database at the same time. Although there are many things at the same time, things do not influence each other.

4) persistence: if the transaction is successfully submitted, the data modification will take effect forever. If it is a rollback, the data will not be modified at all, which means that no such thing has happened.

II. Index

1. Index is an object of oracle and an optional structure associated with tables, which provides a fast way to access data and improves the performance of database retrieval. The index enables the database program to find the data it needs without scanning the entire table. Like the catalogue of a book, you can quickly find the letter you need through the catalogue.

There is no need to read the whole book.

2. Classification of index

1) B-tree index structure

4. Syntax for creating an index

Create [unique] index index name on table name (column name) [tablespace tablespace name]

Explanation:

[unique] is used to specify a unique index, which is non-unique by default

[tablespace] specify a tablespace for the index

1) create a standard index

SQL > CREATE INDEX index_name ON tablename (columnname)

TABLESPACE index_tbs

2) rebuild the index

SQL > ALTER INDEX index_name REBUILD

Merge index fragments

SQL > ALTER INDEX index_name COALESCE

3) Delete index

SQL > DROP INDEX index_name

Example 1:

1) in the emp table, create a b-tree index for the employee name (ename) column.

5. Create a unique cable

N make sure there are no duplicate values in the column that defines the index

N Oracle automatically creates a unique index on the primary key column of the table

N create a unique index using the CREATE UNIQUE INDEX statement

The syntax is as follows:

SQL > CREATE UNIQUE INDEX index_name

ON tablename (columnname)

Example: in the salary scale (salgrade) table, create a unique index for the level numbered grade column.

6. Reverse key index

In contrast to a regular B-tree index, the reverse key index reverses the bytes of the index column while maintaining the column order. By reversing the data value of the index key, the reverse key index makes the modification of the index evenly distributed over the whole index tree. It is mainly used in scenarios where multiple instances access a database at the same time.

6. Bitmap index

Bitmap indexes are suitable for columns that are lower than the cardinality, that is, the column has a limited number of values. For example, the type of work (job) column in the employee table is limited, even if there are millions of employee records. The Job column can be used as a bitmap index, similar to the book category column in the book table.

The bitmap index does not store ROWID directly, but stores the mapping of byte bits to ROWID, which reduces response time and saves space. Bitmap indexes should not be used on tables where insert, update and delete operations occur frequently, because a single bitmap index points to many data rows of the table, and all the data rows it points to need to be locked when the index item is modified, which will seriously reduce the concurrent processing ability of the database. Bitmap index is suitable for data warehouse and decision support system.

Example: in the employee emp table, create a bitmap index for the job column.

Basic syntax:

CREATE BITMAP INDEX emp_job_bit_idx ON emp (job)

6. Combined index

A composite index similar to sqlserver that creates an index on multiple columns within a table. The columns in the index do not have to be in the same order as the columns in the table, nor do they have to be adjacent to each other.

6. Function-based index

The index you need to create requires a function or expression with one or more columns in the table, or you can create a function-based index as a B-tree index or a bitmap index.

Basic syntax:

SQL > CREATE INDEX emp_ename_upper_idx

ON tablename (UPPER (columnname))

In the emp table, create a lowercase function index for the employee name (ename) column

6. Principles for creating indexes

N frequently searched columns can be used as index columns

N often sort, grouped columns can be used as indexes

N columns that are often used as joins (primary / foreign keys) can be used as indexes

N put the index in a separate table space, not in a table space with fallback segments, temporary periods, and tables

N for large indexes, consider using the NOLOGIN clause to create large indexes.

N periodically regenerate or reorganize the index and defragment according to the frequency of business data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.