Oracle index and constraints (zhuang)

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.

SQL code for creating a non-unique index

1. Create index emp_ename on employees (ename)
2. Tablespace users
3. Storage (......)
4. Pctfree 0;

create index emp_ename on employees(ename) tablespace users storage(......) pctfree 0; 

SQL code for creating a unique index

1. Create unique index emp_email on employees (email)
2. Tablespace users;

create unique index emp_email on employees(email) tablespace users; 

SQL code for creating Bitmap indexes

1. Create bitmap index emp_sex on employees (sex)
2. Tablespace users;

create bitmap index emp_sex on employees(sex) tablespace users; 

SQL code for creating reverse Index

1. Create unique index order_retries on orders (order_num, order_date)
2. Tablespace users
3. Reverse;

create unique index order_reinx on orders(order_num,order_date) tablespace users reverse; 

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

1. Create index emp_substr_empno
2. On employees (substr (empno, 1, 2 ))
3. Tablespace users;

create index emp_substr_empno on employees(substr(empno,1,2)) tablespace users; 

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

1. Alter index emp_ename storage (pctincrease 50 );

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 SQL code

1. Alter table employees enable primary key using index pctfree 5;

alter table employees enable primary key using index pctfree 5; 

Clear index fragments
1. Merge indexes (simply merge the storage fragments in the B leaf node without changing the index's physical organization structure) SQL code

1. Alter index emp_pk coalesce;

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.
It is actually re-creating a new index in the specified tablespace and then deleting the original index.) SQL code

1. Alter index emp_pk rebuild;

alter index emp_pk rebuild; 

Delete index SQL code

1. Drop index emp_ename;

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