Improve Oracle index _ MySQL

Improve Oracle indexes when we use create index to CREATE an INDEX, Oracle provides many parameters to improve the INDEX creation speed, the space occupied by the INDEX, and the layers of the INDEX tree.

1. speed factors

PARALLEL option: When creating an index, Oracle will first perform a full table scan to collect the symbol keys and ROWID pairs, while PARALLEL option allows multiple processors to scan tables in PARALLEL, this will accelerate the index creation process. The recommended PARALLEL value is 1 less than the number of CPUs.

NOLOGGING option: Because the NOLOGGING option does not write logs, the performance is greatly improved, saving about 70% of the time compared to the NOLOGGING option. The only risk of using NOLOGGING to create an index is that if the database needs to perform rollback and recovery, you will have to re-create the index.

2. spatial and structural factors

COMPRESS option: the COMPRESS option is used to COMPRESS duplicate values when creating a non-unique index. For connected indexes (that is, the index contains multiple columns), The COMPRESS option will reduce the index size by more than half. The COMPRESS option can also set the prefix length of the connected index.

Using a large BLOCKSIZE tablespace: creating an index on a large BLOCKSIZE tablespace greatly reduces the layers of the index tree.