What indexes does Oracle have?

Oracle's index types include: non-unique index, unique index, bitmap index, local prefix partition index, local non-prefix partition index, global prefix partition index, hash partition index, function-based index . The index needs to be created after inserting data into the table. A unique index can be created with the "create unique index" statement.

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

What is an index?

• The index is an auxiliary object built on one or more columns of the table, with the purpose of speeding up access to the data in the table;
• Oracle storage index The data structure is a B* tree (balanced tree), and the same is true for bitmap indexes, except that the leaf nodes have different B* number indexes;

• The index consists of root nodes, branch nodes and leaf nodes, and the superior Index blocks contain index data for subordinate index blocks, and leaf nodes contain index data and rowids that determine the actual location of the row.

Index description

1) The index is one of the database objects, used to speed up data retrieval, similar to the index of a book. Indexing in the database can reduce the amount of data that needs to be read when the database program queries the results, similar to how in books we can use indexes to find the information we want without having to read the entire book.

2) Index is an optional object built on the table; the key to the index is to replace the default full table scan retrieval method with a set of sorted index keys, thereby improving retrieval efficiency

3) The index is logically and physically independent of the related tables and data. When creating or deleting an index, it will not affect the basic table;

4) Once the index is created, it will be performed on the table. During DML operations (for example, when performing insert, modify, or delete related operations), Oracle will automatically manage the index, and index deletion will not affect the table

5) The index is transparent to the user, regardless of the table. Whether there is an index, the usage of the sql statement remains unchanged

6) Oracle will automatically create an index on the column when creating the primary key

The purpose of using the index:

• Speed ​​up query speed
• Reduce I/O operations
• Eliminate disk sorting (indexes can speed up sorting)

When to use indexes:

• The number of records returned by the query is
• The table has many fragments (frequent additions and deletions)

Types of indexes

• Non-unique index (most commonly used)
• Unique index
• Bitmap index
• Local prefixed partition index
• Local non-prefixed partition index
• Global prefixed partition index
• Hash partitioned index
• Function-based index

Guidelines for managing indexes

• Create an index after inserting data into the table
• After inserting or loading data with SQL*Loader or import tools, it is more effective to create an index;

Index the correct tables and columns

• Frequently retrieved 40% of sorted large tables or 7% of unsorted tables For rows, it is recommended to build an index;
• In order to improve multi-table association, index columns are used for joins;
• The values ​​in the columns are relatively unique;
• Value range (large : B* tree index, small: bitmap index);
• Date-type columns are generally suitable for function-based indexes;
• There are many null values ​​in the column and are not suitable for indexing

Arrange index columns for performance

• Often multiple fields are used together to retrieve records, and combined indexes are more efficient than single indexes;
• Put the most commonly used columns at the front, for example: dx_groupid_serv_id(groupid,serv_id), use groupid or # in the where condition ##groupid,serv_id, the query will use the index. If only the serv_id field is used, the index will be invalid;

Merge/Split unnecessary index.

Limit the number of indexes per table

• A table can have hundreds of indexes (would you do this?) , but for frequently inserting and updating tables, the more indexes the system CPU has, the heavier the I/O burden will be;

• It is recommended that each table have no more than 5 indexes.

Delete indexes that are no longer needed

• Invalid indexes are mainly manifested in the use of function-based indexes or bitmaps Graph index, instead uses B* tree index;

• The query in the application does not use the index;

• The index must be deleted before rebuilding the index , if you use alter index...rebuild to rebuild the index, you do not need to delete the index.

Index data block space usage

• Specify the table space when creating an index, especially when creating a primary key, you should clearly specify the table space;
• Set pctfress appropriately, note: pctused cannot be specified for the index;
• Estimate the size of the index and set the storage parameters reasonably. The default is the table space size, or initial and next are set to the same size.

Consider creating indexes in parallel

• Parallel index creation can be used for large tables. When creating indexes in parallel, the storage parameters are changed by each query server. Processes are used separately, for example: initial is 1M, and the parallelism is 8, then at least 8M space will be consumed during index creation;

Consider using nologging to create an index

• To create an index on a large table, you can use nologging to reduce redo logs;
• Save redo log file space;
• Shorten the time to create an index;
• Improve the performance when creating large indexes in parallel.

How to create the best index?

Create the index explicitly

create index index_name on table_name(field_name)
　　tablespace tablespace_name
　　pctfree 5
　　initrans 2
　　maxtrans 255
　　storage
　　(
　　minextents 1
　　maxextents 16382
　　pctincrease 0
　　);

Create a function-based index:

Commonly used with UPPER, LOWER, TO_CHAR(date) and other function classifications, for example:

create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

Creating a bitmap index:

Compare the base When creating an index for a column that is small and has a relatively stable base, you should first consider a bitmap index. For example:

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

Create a unique index explicitly

You can use create unique index statement to create a unique index, for example:

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

Create an index related to constraints

You can use the using index statement, for unique and primary keyCreate an index on fields related to constraints, for example:

alter table table_name
　　add constraint PK_primary_keyname primary key (field_name)
　　using index tablespace tablespace_name；

How to create a local partition index

• The base table must be a partitioned table;
• The number of partitions is the same as the base table;
• The number of sub-partitions of each index partition is the same as the corresponding base table partition;
• The index entries of rows in the sub-partitions of the base table are stored in In the corresponding sub-partition of the index, for example:

　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
　　Pctfree 5　　Tablespace TBS_AK01_IDX
　　Storage (
　　MaxExtents 32768　　PctIncrease 0　　FreeLists 1　　FreeList Groups 1　　)
　　local　　/

How to create a global index for range partitions

The basic table can be a global table and a partitioned table.

create index idx_start_date on tg_cdr01(start_date)
　　global partition by range(start_date)
　　(partition p01_idx vlaues less than (‘0106’)
　　partition p01_idx vlaues less than (‘0111’)
　　…
　　partition p01_idx vlaues less than (‘0401’ ))
　　/

Rebuilding the existing index
The current moment of rebuilding the existing index will not affect the query;

Rebuilding the index can delete additional data blocks;
Improve the efficiency of index query;

alter index idx_name rebuild nologging;

For partitioned indexes:

alter index idx_name rebuild partition partiton_name nologging;

Reason for deleting the index

• The index is no longer needed;
• The index does not target its related tables The published query provides the expected performance improvement;
• The application is not using the index to query the data;
• The index is invalid and must be dropped before rebuilding;
• The index has become too fragmented and must be dropped before rebuilding;
• Statement:
drop index idx_name;
drop index idx_name drop partition partition_name;


The cost of establishing an index

When maintaining basic tables, the system must maintain the index at the same time. Unreasonable indexes will seriously affect System resources, mainly manifested in CPU and I/O;

Inserting, updating, and deleting data generate a large amount of db file sequential readlock waiting;

There are There are millions of pieces of data, and an index is added to a certain field, but the query performance has not improved. This may be mainly caused by the index limit of oracle. The index of

oracle has some index restrictions. When these index restrictions occur, even if the index has been added, oracle will still perform a full table scan. The performance of the query will not be improved compared to without indexing. Instead, the performance may be worse due to the system overhead of maintaining the index in the database.

Extended knowledge: Common index restriction issues

1. Use the inequality operator (<>, != )

In the following situation, even if there is an index on column dept_id, the query statement still performs a full table scan

select * from dept where staff_num <> 1000;

But this is indeed needed in development query, is there no solution to the problem?

have!

By replacing the inequality sign with the or syntax for querying, you can use the index to avoid a full table scan: change the above statement to the following, and you can use the index.

select * from dept shere staff_num < 1000 or dept_id > 1000;

2. Use is null or is not null

Using is null or is nuo null will also limit The use of indexes, because the database does not define the null value. If there are many nulls in the indexed column, the index will not be used (unless the index is a bitmap index, which will be explained in detail in a future blog post). Using null in sql statements will cause a lot of trouble.

The way to solve this problem is: when creating the table, define the columns that need to be indexed as non-null (not null)

3. Use the function

If a function-based index is not used, then using a function on a column with an index in the where clause will cause the optimizer to ignore these indexes. The following query will not use the index:

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

4、比较不匹配的数据类型

比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为oracle会自动把where子句转换成to_number(dept_id)=900198，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = '900198';

5、使用like子句

使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

Like 的字符串中第一个字符如果是‘%’则用不到索引

Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到

6、使用IN

尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多

用In还是用Exists的时机

当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists
例：

select count(*) from person_info where xb in (select xb_id from dic_sex);

Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …);

Select * from person_info where zjhm=3101….;--将会对person_info全表扫描

Select * from person_info where zjhm =‘3101…’;--才能用到索引

假定TEST表的dt字段是date类型的并且对dt建了索引。
如果要查‘20041010’一天的数据.下面的方法用不到索引

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

而以下将会用到索引。

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

7、如果能不用到排序，则尽量避免排序。

用到排序的情况有
集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

Order byGroup byDistinctIn

有时候也会用到排序
确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

在排序的字段上创建索引，让排序在内存中执行，加快排序速度。

8、在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

解决：执行表分析。获取表的最新信息。

9、获取的数据量过大，全部扫描效率更高

10、索引字段的值分散率太低，值太集中，如类型字段都是1,2， 状态类型Y-有效/N-无效。这类型的字段最好别建索引。

尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

推荐教程：《Oracle教程》

The above is the detailed content of What indexes does Oracle have?. For more information, please follow other related articles on the PHP Chinese website!