What about Oracle index?

The table is rarely modified. You can create more indexes, especially read-only tables.
There are many changes to the table, which need to be considered.

15% of the data below is suitable for index creation

The index does Not contain null, so it is appropriate to create an index if the condition is Not null.

The index of the maximum size should be less than half of the table

You can use parallel index creation to accelerate index creation.

You can use nologging to create an index. It is a good idea to increase the speed. Combined with parallel, the archive generation is not tested. You can test it.

Unusable indexes
-- The optimizer will not consider it, and dml will not maintain these indexes. It is suitable for loading large volumes of data and enabling the index after loading.
When the index is set to unusable, the index segment will be deleted.
The skip_unusable_indexes parameter determines whether the dml maintains the index set to unusable.

Invisible indexes
This index will also be ignored by optimizer, but the dml statement will maintain this index. A single partition index cannot be set to invisible.
Use Cases:
1. test the impact of deleting an index.
2. Creating a temporary index does not affect existing programs.
3. create other indexes on a column that already has an index-this may be a new feature. Multiple indexes can be created on one index.

Understand when to create multiple indexes on the same set of columns-p745
You can create different types of indexes on multiple columns, but only one index is visible at a time. Other Indexes must be invisible.

-- Partition indexes can be created on common tables. This is a good idea for optimization. You cannot just look at the Partition Table ****

Rebuilding and coalesce Indexes
Obviously, coalesce has few functions and has little impact.
Coalesce will merge leaf blocks with the same height, so it is not simply to recycle unused leaf blocks, but will not reduce the height of the index

When creating a particularly large index, you should create a temporary tablespace separately. Creating an index will certainly involve sorting operations. Of course, it is also advantageous to increase the sort_area_size of the session.

Create index idx_aa on aa (id) online; -- the specified online statement cannot use parallel or ddl operations, but allows dml operations on the base table.

Function-based indexes have timestamp. If the timestamp is more time-based than the system's restoration time, the index may become invalid. You can use analyze

Index... validate sructure statement to confirm, without the support of the experiment, the views are pale.

 

Creating a key-compressed index

-- The prefix and suffix values are used for compression, which is suitable for guide columns with more non-unique values.

-- Saves space and improves performance
The compressed index is suitable for non-unique columns. It is also used to compress the prefix and suffix.

Create index hr. emp_ename on emp (ename) tablespace users compress 1;

Alter index hr. emp_ename rebuild nocompress;

Unuseable index -- the so-called creation is unavailable, and no segment is allocated

Create index id_a on a (id unusable );

Invisible index -- same as above, but the allocation segment and dml operations will also be maintained by the database (it is best to create fewer indexes when these indexes are not needed). You can use optimizer_use_invisible_indexes at the session level to use indexes true.
-- The two indexes above will be ignored by the optimizer.

Create index emp_id on emp (id) invisible;

The unusable index is ignored by the optimizer and is not maintained during dml operations on the base table.

Alter index id1 rename to id2;

Alter index id1 monitoring usage;
Alter index id1 nomonitoring usage;

Dba_ind_expressions -- you can view the function-based index expression.

Alter index... validate structure; -- you can query index_stats to obtain the index status.

User_object_usage; -- obtains whether the index is in use.

Oracle collects statistics, causing indexes to be monitored

Understanding ArcSDE indexes by Oracle Indexes

How to create an optimal index using Oracle Indexing Technology

Test example of the table where the NULL value of the Oracle index column triggers the execution plan

Oracle index primary key affects query speed

Oracle index Scanning