Oracle index creation policy

Oracle index problems are often encountered when learning Oracle. Here we will introduce the solutions to Oracle index problems. Oracle 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 indexes: store the value of the index field and ROWID in the leaf node of Tree B. both unique and non-unique indexes are only applicable to B-tree indexes. Oracle allows composite indexes containing up to 32 Fields
Oracle 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
1. create index emp_ename on employees (ename)
2. tablespace users
3. storage (......)
4. pctfree 0;
Create a unique index
1. create unique index emp_email on employees (email)
2. tablespace users;
Create a bitmap Index
1. create bitmap index emp_sex on employees (sex)
2. tablespace users;
Create reverse Index
1. create unique index order_retries on orders (order_num, order_date)
2. tablespace users
3. reverse;
Create a function index (the function index can be a common B-tree index or a bitmap index)
1. create index emp_substr_empno
2. on employees (substr (empno, 1, 2 ))
3. tablespace users;
The preceding section describes the Oracle index creation policy.