ORACLE index and constraints

Oracle DatabaseInIndexIt is very important that the existence of indexes simplifies the operation process of the Oracle database. Next we will explain the indexes and constraints of the Oracle database in detail, hoping to help you.

Oracle Constraints

If a constraint only applies to individual fields, you can define the constraint at the field level or at the table level. However, if a constraint acts on multiple fields,

Constraints must be defined at the table level

When defining constraints, you can use the CONSTRAINT keyword to name constraints. If not specified, ORACLE automatically creates default names for constraints.

Define the primary key constraint (single field)

Create table employees (empno number (5) primary key ,...)

Constraint name

Create table employees (empno number (5) constraint emp_pk primary key ,...)

Define the primary key constraint (multiple fields are defined at the table level)

Create table employees

(Empno number (5 ),

Deptno number (3) not null,

Constraint emp_pk primary key (empno, deptno)

Using index tablespace indx

Storage (initial 64 K

Next 64 K

)

)

ORACLE automatically creates a unique index and a not null constraint for a field with the primary key constraint (PRIMARY code segment). When the primary key constraint is defined, it can be indexed.

Specify storage location and storage Parameters

Alter table employees add primary key (empno)

Alter table employees add constraint emp_pk primary key (empno)

Alter table employees add constraint emp_pk primary key (empno, deptno)

Not null constraint (the not null constraint can only be defined at the field level. Multiple not null constraints can be defined in the same table)

Alter table employees modify deptno not null/null

Unique constraints

Create table employees

(Empno number (5 ),

Ename varchar2 (15 ),

Phone varchar2 (15 ),

Email varchar2 (30) unique,

Deptno number (3) not null,

Constraint emp_ename_phone_uk unique (ename, phone)

)

Alter table employees

Add constraint emp_uk unique (ename, phone)

Using index tablespace indx

Fields defining the UNIQUE constraint cannot contain duplicate values. You can define a UNIQUE constraint for one or more fields. Therefore, UNIQUE can be defined at the field or table level,

Fields with UNIQUED constraints can contain null values.

Foreign key constraint

Fields defined as the foreign key constraint can only contain values or NULL values that reference the code segment in other tables.

You can define the foreign key constraint for a combination of one or more fields.

The external code segment that defines the foreign key constraint and the corresponding reference code segment can exist in the same table. In this case, it is called "Self-reference"

The foreign key constraint and not null constraint can be defined for the same field at the same time.

The field defining the foreign key constraint is called the "external code segment". The field referenced by the forgien key constraint is called the "reference code segment". The reference code must be the primary code or unique code, A table that contains external codes is called a sub-table,

A table containing a reference code is called a parent table.

A:

Create table employees

(.....,

Deptno number (3) not null,

Constraint emp_deptno_fk foreign key (deptno)

References dept (deptno)

)

If the external code in the sub-table has the same name as the reference code in the master table, you can write it as follows:

B:

Create table employees

(.....,

Deptno number (3) NOT NULL

Constraint emp_deptno_fk references dept

)

Note:

In the above example (B), not null is followed by no comma, because the contraint of this sentence is followed by the deptno column and belongs to the column definition, so you do not need to specify the column. In Example A, the table definition is used and the column must be specified. Therefore, you must add A comma (,). You cannot define the column after it. You can also write it as follows:

Create table employees

(Empno char (4 ),

Deptno char (2) not null constraint emp_deptno_fk references dept,

Ename varchar2 (10)

)

The table definition contraint can only be written at the end. Let's look at two examples:

Create table employees

(Empno number (5 ),

Ename varchar2 (10 ),

Deptno char (2) not null constraint emp_deptno_fk references dept,

Constraint emp_pk primary key (empno, ename)

)

Create table employees

(Empno number (5 ),

Ename varchar2 (15 ),

Phone varchar2 (15 ),

Email varchar2 (30) unique,

Deptno number (3) not null,

Constraint emp_pk primary key (empno, ename ),

Constraint emp_phone_uk unique (phone)

)

Add a foreign key constraint (Multi-field/table-level)

Alter table employees

Add constraint emp_jobs_fk foreign key (job, deptno)

References jobs (jobid, deptno)

On delete cascade

Modify the reference behavior defined by the foreign key constraint (delete cascade/delete set null/delete no action). The default value is delete on action.

Reference behavior (when a record in the master table is deleted, determine how to process the external code segment in the word table ):

Delete cascade: delete all related records in the subtable

Delete set null: set the value of the external code segment of all related records to NULL.

Delete no action: no action

Delete the original foreign key constraint and then add the constraint.

Alter table employees drop constraint emp_deptno_fk;

Alter table employees add constraint emp_deptno_fk foreign key (deptno) REFERENCES dept (deptno) on delete cascade;

Check Constraints

The expression of the CHECK constraint must reference one or more fields in the table, and the calculation result of the expression must be a Boolean value.

Can be defined at the table level or field level

Multiple CHECK constraints can be defined for the same field, and the not null constraint can also be defined.

Create table employees

(Sal number (7,2)

Constraint emp_sal_ck1 check (sal> 0)

)

Alter table employees

Add constraint emp_sal_ck2 check (sal <20000)

Delete Constraints

Alter table dept drop unique (dname, loc) -- specify the definition content of the constraint

Alter table dept drop constraint dept_dname_loc_uk -- specify the constraint name

When you delete a constraint, the INDEX corresponding to the constraint will be deleted by default. If you want to retain the INDEX, use the keep index keyword.

Alter table employees drop primary key keep index

If the constraint to be deleted is being referenced by another constraint, you can use the alter table. DROP statement to specify the CASCADE keyword to delete the constraint that references it at the same time.

When the following statement is used to delete the primary key constraint in the DEPT table, the foreign key constraint that references this constraint in other tables will also be deleted:

Alter table dept drop primary key cascade

Disable/activate constraints (disabling/activating constraints will cause deletion and re-indexing operations)

Alter table employees disable/enable unique email

Alter table employees disable/enable constraint emp_ename_pk

Alter tabel employees modify constraint emp_pk disable/enable

Alter tabel employees modify constraint emp_ename_phone_uk disable/enable

If the foreign key constraint is referencing the UNIQUE or primary key constraint, the UNIQUE or primary key constraints cannot be disabled,

In this case, you can DISABLE the foreign key constraint first, and then DISABLE the UNIQUE or primary key constraint. Alternatively, you can DISABLE the UNIQUE or primary key constraint in alter table... DISABLE

Specify the CASCADE keyword in the statement. In this way, the foreign key constraints that reference them are disabled while the UNIQUE or primary key constraints are disabled, for example:

Alter table employees disable primary key cascade

Constraint Data Dictionary

All_constraints/dba_constraints/user_constraints constraint basic information, including the constraint name, type, status

(Constraint type: C (CHECK constraint), P (primary code constraint), R (external code constraint), U (unique code constraint ))

All_cons_columns/dba/user field information corresponding to the Constraint

Oracle Index

Indexes and corresponding tables should be in different tablespaces. oracle can read data on different hard disks in parallel to avoid I/O conflicts.

B-Tree Index: store the value and ROWID of the index field in the leaf node of B-tree.

Both unique and non-unique indexes are only applicable to B-tree indexes.

Oracle allows composite indexes containing up to 32 Fields

Index creation policy

1. import data before creating an index

2. You do not need to create an index for a small table.

3. Bitmap indexes should be created for fields with a small value range, such as gender fields.

4. Limit the number of indexes in a table

5. Set the appropriate PCTFREE value for the index

6. It is best to set the tablespace for storing indexes separately.

Create a non-unique index

Create index emp_ename on employees (ename)

Tablespace users

Storage (......)

Pctfree 0;

Create a unique index

Create unique index emp_email on employees (email)

Tablespace users;

Create a bitmap Index

Create bitmap index emp_sex on employees (sex)

Tablespace users;

Create reverse Index

Create unique index order_retries on orders (order_num, order_date)

Tablespace users

Reverse;

Create a function index (the function index can be a common B-tree index or a bitmap index)

Create index emp_substr_empno

On employees (substr (empno, 1, 2 ))

Tablespace users;

Modify index storage parameters (similar to the table, INITIAL and MINEXTENTS parameters cannot be changed after the index is created)

Alter index emp_ename storage (pctincrease 50 );

Because the index automatically created by oracle does not know the name when defining constraints, changes to such indexes are often made using the alter table... using index statement, rather than the alter index statement.

Use the following statement to modify the PCTFREE parameter of the index corresponding to the primary key constraint in the employees table to 5

Alter table employees enable primary key using index pctfree 5;

Clear index fragments

1. Merge indexes (simply merge the storage fragments in B leaf nodes without changing the index's physical structure)

Alter index emp_pk coalesce;

2. Re-indexing (not only can delete storage fragments, but also can change all storage parameter settings of indexes. In addition, you can move the indexes to other tablespaces and recreate indexes.

In fact, a new index is created again in the specified tablespace and then the original index is deleted)

Alter index emp_pk rebuild;

Delete Index

Drop index emp_ename;

If the index contains corrupted data blocks or contains too many storage fragments, you must first Delete the index and then recreate it.

If the index is automatically generated by oracle when a constraint is created, you can delete the corresponding index by disabling or deleting the constraint.

When you delete a table, oracle automatically deletes all indexes related to the table.

Index data dictionary

Basic information of all_indexes/dba_indexes/user_indexes Indexes

All_ind_columns/dba_ind_columns/user_ind_columns

I have explained so much about Oracle database indexing and constraints. I hope you will be able to learn from the content mentioned above.