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

Indexes have two functions: one is to enforce primary key constraints and unique constraints, and the other is to improve performance.

However, it will reduce the performance of DML operability.

1.1 necessity of index

First, the index is part of the constraint mechanism, through which the key value can be accessed immediately (or approximately immediately). If a primary key is defined and an index has not been established on the primary key, Oracle automatically creates an index. Unique constraints also need to be indexed, and in order to improve performance, indexes should always be established on the foreign keys of the word table. Indexes are critical to performance.

Second, the case where an index can be used is sorting.

Third, the third case where indexes help improve performance is join queries.

1.2 Type of index

In OLTP systems, minimize the number of indexes, while in OLAP systems (data warehouses), creating enough indexes will help.

(1) B* tree index

The leaf nodes of the index tree store row keys sequentially, with each key having a pointer to determine the physical location of the row. The pointer to the row is rowid. Rowid is a virtual column dedicated to Oracle, and every row of every table has this virtual column. Each rowid is globally unique.

Select count (*) from employees where last_namebetween'A% 'and' Z%'

Note: if the value specified in the where clause is null, it will be an exception. Null does not participate in the B* tree index, causing the entire table to be scanned all the time.

Select * from employees where last_name is null

The use of the B* tree is as follows:

The cardinality of the column (the number of different values) is very large

The table has a large number of rows

Columns are used in where clauses or join conditions.

(2) Bitmap index

Bitmap indexes are used in data warehouses and can be analyzed from several dimensions. The situations where you can index with a bitmap are as follows:

The cardinality of the column (the number of different values) is small

There are many rows in the table.

Columns are used for Boolean algebraic operations.

(3) Index type option

Unique or not unique (Unique or non_unique)

Reverse key (Reverse Key)

Compression (Compressed)

Composite (Composite)

Based on function (Function based)

Ascending or descending (Ascending or Desending)

The above six options can be used for B* trees, only composite, function-based, ascending or descending order can be applied to bitmap indexes.

1.3 create and use indexes

Indexes are defined in primary key constraints and unique constraints.

Syntax:

CREATE [UNIQUE | BITMAP] INDEX [schema.] indexname

On [schema.] tablename (column [, column...])

Note: the default created index type is non-unique, non-compressed, non-reverse key B* tree.

Eg.

SQL > create table dept (deptno number (8), dname varchar2 (20))

SQL > createtable emp (empno number (8), ename varchar2 (20), deptno number (8))

SQL > createunique index dept_i on dept (deptno)

SQL > createunique index emp_i on emp (empno)

SQL > createbitmap index emp_i2 on emp (deptno)

SQL > altertable dept add constraint dept_pk primary key (deptno)

SQL > altertable emp add constraint emp_pk primary key (empno)

SQL > altertable emp add constraint emp_fk foreign key (deptno) references dept (deptno)

1.4 modify and delete indexes

The alter index command belongs to the category of database management and is explained in 1Z0-055.

SQL > drop index emp_i2

[create an index case]

SQL > commwebstore/admin123

SQL > createindex cust_name_i on customers (customer_name,customer_status)

Create bitmap indexes on some low cardinality columns

SQL > createbitmap index creditrating_i on customers (creditrating)

Run the following query to determine the name and other features of the index you just created.

SQL > selectindex_name,column_name,index_type,uniqueness from user_indexes natural joinuser_ind_columns where table_name = 'CUSTOMERS'

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.