What is an index in oracle

In Oracle, an index is a database structure that allows the server to quickly find a row in a table. It is an auxiliary object built on one or more columns of the table, with the purpose of speeding up access to data in the table. The functions of indexes: 1. Quickly access data; 2. It can not only improve database performance, but also ensure the uniqueness of column values, etc.

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

What is an index?

In Oracle, an index is a database structure that allows the server to quickly find a row in a table.

• An index is an auxiliary object built on one or more columns of a table to speed up access to data in the table.

• oracle The structure of storage index is B* number (balanced tree), and the index is composed of root node, branch point and leaf point. The upper-level index block contains the index data of the lower-level index block , the leaf nodes contain the index data and the rowid that determines the actual location of the row.

Creating an index in the database mainly has the following functions:

(1) Quickly access data.

(2) Reduce I/O operations

(3) It can not only improve database performance, but also ensure the uniqueness of column values.

(4) Implement referential integrity between tables

(5) When using orderby and groupby clauses for data retrieval, using indexes can reduce the time of sorting and grouping.

When to use:

• The fields used as query conditions after where are suitable for indexing.

When not to use:

• There are many repeated fields;

• Fields that are not used as query conditions after where;

• There are fewer records in the table.

How to use index:

Create index:

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

For example,

Create a product table:

-- Create table
create table TB_GOODS
(
  goods_id    VARCHAR2(64) not null,
  goods_name  VARCHAR2(256) not null,
  goods_price VARCHAR2(64) not null,
  status      VARCHAR2(1) not null
);

Create index:

--这里unique可以省略，若省略则索引为非唯一索引
create index UI_tb_goods on tb_goods(goods_name);
--若使用unique则为唯一索引
create unique index un_tb_goods on tb_goods(goods_name);
--括号中添加两列则为组合索引
create unique index un2_tb_goods on tb_goods(goods_name,status);
--括号中添加三列，其顺序没有关系，效果是一样的
create unique index un3_tb_goods on tb_goods(goods_name,goods_price,status);

Another function of the unique index is to control that the column cannot have the same value!

Query index:

select * from user_indexes where table_name='表名';
select * from user_ind_columns  where index_name='索引名';

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of What is an index in oracle. For more information, please follow other related articles on the PHP Chinese website!