Nologging
This option Significantly improving performance when u create index. (up to 30% faster)
Other benefits:
* Space is saved in the redo log files.
* The time it takes to create the index is decreased.
* Performance improves for parallel creation of large indexes.
Ex-1:
CREATE INDEX cust_dup_idx
ON customer(sex, hair_color, customer_id)
PARALLEL 35
NOLOGGING;
Ex-2:
CREATE INDEX UA0909_PROV_DT_I
ON THARCH.UA_200909_200910 (PROV_ID, DATESTAMP)
TABLESPACE X_INDX01_16K
NOLOGGING
COMPRESS;
Logging
As long as the indexes are in logging mode immediately before and during a hot backup they will be refreshed properly.
This option Significantly improving performance when u create index. (up to 30% faster)
Other benefits:
* Space is saved in the redo log files.
* The time it takes to create the index is decreased.
* Performance improves for parallel creation of large indexes.
Ex-1:
CREATE INDEX cust_dup_idx
ON customer(sex, hair_color, customer_id)
PARALLEL 35
NOLOGGING;
Ex-2:
CREATE INDEX UA0909_PROV_DT_I
ON THARCH.UA_200909_200910 (PROV_ID, DATESTAMP)
TABLESPACE X_INDX01_16K
NOLOGGING
COMPRESS;
Logging
As long as the indexes are in logging mode immediately before and during a hot backup they will be refreshed properly.
ALTER INDEX UA0909_PROV_DT_I LOGGING;
Compress
This option is used to repress duplication of keys in non-unique indexes.
The compress option can reduce the size of the index by more than half.
The compress option allows you to specify the prefix length for multiple column indexes.
Ex-1:
CREATE INDEX cust_dup_idx
ON Customer (sex,hair_color, customer_id)
PARALLEL 35
NOLOGGING
COMPRESS 2;
Ex-2:
CREATE INDEX UA0909_PROV_DT_I
ON THARCH.UA_200909_200910 (PROV_ID, DATESTAMP)
TABLESPACE X_INDX01_16K
NOLOGGING
COMPRESS;
