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

What is an index [Index]

(1) it is a mechanism for quickly querying the contents of a table, similar to the directory of Xinhua Dictionary.

(2) it is used on some / some fields in the table, but it is stored independently of the table.

Why use an index?

(1) accelerate the query speed of Oracle server through pointers

(2) through the method of rowid to quickly locate the data, the disk I / O can be reduced.

Rowid is the only one in oracle that determines the different records of each table.

Characteristics of rowid

(1) located in each table, but not visible on the surface, for example, desc emp is invisible

(2) you can see it only if you explicitly write rowid in select.

(3) it is bound to each table, the table is dead, the rowid of the table is dead, the rownum of the two tables can be the same, but the rowid must be unique.

(4) rowid is an 18-bit case-plus-digit hybrid, and the only table represents the location of the entry in the DBF file.

(5) when rowid can participate in the = / like comparison, enclose the value of rowid in''single quotation marks and is case-sensitive

(6) rowid is the bridge between contact sheet and DBF file.

Thinking: rowid is the bridge between the contact sheet and the DBF file, I don't understand.

Characteristics of the index

(1) once the index is established, the Oracle management system will automatically maintain it, and it is up to the Oracle management system to decide when to use the index.

(2) users do not need to specify which index to use in the query statement

(3) after defining primary key or unique constraints, the system automatically creates indexes on the corresponding columns.

(4) users can also add indexes to specify a single field or multiple fields according to their own needs.

When [to] create an index

(1) tables often perform SELECT operations

(2) the table is very large (there are too many records), and the distribution of record content is very wide.

(3) column names often appear in WHERE clauses or join conditions

Note: if you meet one of the above requirements, you can create an index. Index creation is not only an optimization problem, but also a strategy problem.

When [do not] create an index

(1) tables often perform INSERT/UPDATE/DELETE operations

(2) the table is very small (very few records)

(3) column names are not often used as join conditions or appear in WHERE clauses

Pay attention to the above

Create an index emp_empno_idx for the empno single field of the emp table, called a single-column index, create index index name on table name (field,...)

Create index emp_empno_idxon emp (empno)

Create an index emp_ename_job_idx, multi-column index / federated index for ename,job multiple fields of the emp table

Create index emp_ename_job_idx on emp (ename,job)

If only job appears in where and does not use index

If only the ename usage index appears in where

We advocate the simultaneous emergence of ename and job

Note: after the index is created, it is only related to the query table and has nothing to do with other (insert/update/delete) to solve the speed problem.

Delete emp_empno_idx and emp_ename_job_idx indexes, drop index index names

Drop index emp_empno_idx;drop index emp_ename_job_idx

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.