Home > Database > Oracle > body text

What is an index in oracle

青灯夜游
Release: 2021-12-24 11:28:36
Original
5733 people have browsed it

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.

What is an index in oracle

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];                                    --表示创建索引时不进行排序,默认不适用,如果数据已经是按照该索引顺序排列的可以使用
Copy after login

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
);
Copy after login

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);
Copy after login

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='索引名';
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact [email protected]
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!