1z0-051-ddl-2 creation and deletion of simple indexes

Indexes have two functions: first, enforce primary KEY constraints and unique constraints, and second, improve performance

but it will reduce DML operational performance.

1.1 the necessity of indexing

The index is part of the constraint mechanism, and the key value can be accessed immediately (or approximately immediately) through the index. If you define a primary key, and the primary key is not already indexed,Oracle automatically creates an index. The unique constraint also needs to be indexed, and in order to improve performance, it should always be indexed on the foreign key of the Word table. Indexes are critical to performance.

Second, the situation that can use the index is sort.

Third, the index to help improve performance is a second situation is to join the query.

1.2 type of index

in the OLTP system, minimizing the number of indexes, while OLAP It helps to create enough indexes in the system (data Warehouse).

( 1 ) b* Tree Index

the leaf nodes of the index tree store row keys sequentially, and each key has a pointer to determine the physical location of the row. The pointer to the line is rowid. rowid is a virtual column that is dedicated to Oracle and has this virtual column for each row in each table. Each rowid is unique in its global sense.

Select Count (*) from employees where Last_namebetween ' A% ' and ' Z% ';

Note: If the where the value specified in the clause is NULL, is an exceptional case. null does not participate in the b* tree Index, causing the full table to always be scanned.

Select *from Employees where last_name is null;

Use b* the situation of the tree is as follows:

• The cardinality of the column (the number of different merits) is large

• Number of rows in the table

• columns are used in where clause or Join the condition.

( 2 ) Bitmap Index

Bitmap indexes are used in the data warehouse and can be analyzed from several dimensions. The following scenarios can be used to index the bitmap:

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

• The number of rows in the table is many.

• Columns are used for Boolean algebra operations.

( 3 ) index type options

• unique or non-unique (Unique or Non_unique)

• Reverse Key (Reverse Key)

• Compression (compressed)

• Composite (Composite)

• based on function (Function based)

• Ascending or Descending (Ascending or desending)

above 6 options are available for B * tree, which is applied to the bitmap index only with a composite, based on function, ascending, or descending.

1.3 Creating and Using Indexes

An index is defined in a PRIMARY KEY constraint and a unique constraint.

Grammar:

Create[unique | BITMAP] INDEX [schema.] IndexName

On[schema.] TableName (Column[,column ...]);

Note: Indexed types created by default are non-unique, uncompressed, non-inverted keys b* Tree.

Eg.

Sql>create Table Dept (deptno Number (8), dname varchar2 (20));

Sql>createtable EMP (empno number (8), ename varchar2 (+), 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 Modifying and deleting Indexes

ALTER INDEX the command belongs to the category of database management, 1z0-055 explained in this paper.

Sql>drop index EMP_I2;

"Create an index case"

Sql>commwebstore/admin123

Sql>createindex cust_name_i on Customers (Customer_name,customer_status);

Create a bitmap index on some low cardinality columns

Sql>createbitmap index creditrating_i on customers (creditrating);

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

Sql>selectindex_name,column_name,index_type,uniqueness from User_indexes natural joinuser_ind_columns where table _name = ' CUSTOMERS ';

This article is from the "struggle more than" blog, please be sure to keep this source http://peenboo.blog.51cto.com/2865551/1793830

1z0-051-ddl-2 creation and deletion of simple indexes