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

The database also has important metrics, so performance and security are definitely two important sectors, where performance refers to the speed of processing and responding to client services, and security is the data integrity rule.

Why index?

We know that when we use select to query the contents of a table, or specify some keywords with where, Oracle will traverse the entire table to find our data, and will not continue to traverse because it finds the data where, because Oracle does not know whether the fields behind the data table have the data we want, so it will traverse the entire table data. When a large data file is a very time-consuming operation, in order to improve our retrieval efficiency, Finding the data we want faster and more efficiently is also one of the original intentions of index design.

How efficient is indexing?

Why force index will be efficient, then we come to understand the principle of the index, relational database every row will have ROWID, then ROWID includes the conditions of the row, BOLACKS in the file, etc., then said here probably know! Index building relies on ROWID to carry out, according to the index of the village storage method has B tree, there are bitmap index. Personal organization language explanation of these two types of fear of misleading children reference Baidu knowledge!

(1)B* The storage structure of tree index is similar to that of book index, with two types of storage data blocks: branch block and leaf block. Branch block is equivalent to the large directory of book, and leaf block is equivalent to the specific page indexed. Oracle uses a B* tree mechanism to store index entries to ensure the shortest path to access key values. By default, B* tree indexes are mostly used, which is the only index, reverse index, that is usually seen.

(2)Bitmap index storage is mainly used to save space and reduce oracle access to data blocks. It uses bitmap offset to correspond to the row ID number of the table, and bitmap index is generally a table field with too many duplicate values. Bitmap indexes are less used in real-world intensive OLTP(Online Transaction Processing) because OLTP does a lot of deleting, modifying, and creating operations on tables. Oracle locks the data block to be manipulated each time it performs an operation. In order to prevent the database lock waiting and even deadlock phenomenon easily produced by multi-person operation. Bitmap has advantages in OLAP(Online Analytical Processing), because most of OLAP is query operation to database, and data warehouse technology is generally used, so bitmap index can save space obviously. Bitmap indexes cannot be created when the command to create a table contains the uniqueness keyword, and bitmap indexes cannot be used when creating a global partitioned index.

The above is Baidu encyclopedia's introduction to the two structures

Format:

create index index_name on people(name)

Explanation:

Then index is the keyword to create the index. At this time, the index will be established on the people column name of the table, and the name information will be divided into blocks according to ROWID and other information.

Through my test without mapping, on 10W data test, ordinary search COST is about 400, when a column index is established, about 4, efficiency 100 times!

Impact of DML

It is indeed fast in retrieval, but the shortcomings are undoubtedly exposed, not suitable for frequent insertion, change and deletion operations, why ah? Because when we insert, we will also modify the index, so the index will not clean up and release the memory of the data you modified and deleted from the block, but only mark it, and sometimes it may change the original structure of "subversion". The important thing is not to release space resources. Therefore, we try our best to avoid this mechanism in the table that frequently inserts, modifies and deletes. Secondly, we should avoid this mechanism when the amount of data is small, because small data has to establish extra overhead for the index. It has no advantages in itself.

Data Integrity (Data Integrity)

Index he simply finished, talk about the integrity of the data, in many previous chapters have mentioned the integrity of the data, most of us think that the data is relatively perfect is the integrity of the data, in fact, very fine, the integrity of the data refers to the reliability and accuracy of the data.

Further, data integrity is divided into four categories: entity integrity, referential integrity, domain integrity, and user-defined integrity.

Integrity (Entity)

Simply put, it is the integrity of the line, which cannot be null, and the uniqueness constraint is guaranteed. Then the primary key is usually used to ensure the integrity of the entity. When the primary key has multiple columns, the uniqueness constraint UNIQUE is declared to ensure the unique identity.

Referential integrity (Domain integrity)

The relationship between tables. For parent-child tables, if insert, update and delete operations are performed, the integrity of the reference will be destroyed if only one table is completed. The word table changes with the parent table 2. Foreign key is generally used to ensure the integrity of the reference.

Domain integrity (Referential Integrity)

Domain integrity refers to columns full of special data types or constraints, such as check,not null,default, etc., all within the scope of domain integrity.

User-defined Integrity

User-defined complete Most of the constraints on the data type and field in the user-defined column, such as what kind of content I only want to insert into a certain field, or only allow the insertion of wages below 1500 yuan, etc., according to our needs, we can get the expected results through SQL statement actions. This process ensures the integrity of our customization, such as the use of CHECK constraints.

More in-depth knowledge here do not do more sharing, afraid of misleading children, the above is a personal reference books and information plus their own understanding to share knowledge to everyone, deficiencies hope to point out certain improvements!

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.