All apps dba Blog is the blog contributed by Doyensys Employees, With the view to share the knowledge out of their experience.
Doyensys Is a Fast Growing Oracle Technology Based Solutions Company Located in the US And Offshore Delivery Centers in India. With rich experience In Oracle E-Business, Oracle Database, Oracle Application Express (APEX), Oracle J Developer, Oracle OBIEE and E-Business Suite
Advanced Index Compression works well on all supported indexes, including those indexes that are not good candidates for the existing prefix compression feature; including indexes with no, or few, duplicate values in the leading columns of the index.
Creating an index using advanced index compression reduces the size of all supported unique and non-unique indexes. Advanced Index Compression improves the compression ratios significantly while still providing efficient access to the index.
Before enabling advanced index compression, the database must be at 12.1.0 or higher compatibility level. You enable advanced index compression using the COMPRESS ADVANCED LOW clause. For example, the following statement enables advanced index compression during the creation of the hr.emp_mndp_ix index:
CREATE INDEX hr.emp_mn_ix ON hr.employees(manager_id, department_id) COMPRESS ADVANCED LOW;
You can also specify the COMPRESS ADVANCED LOW clause during an index rebuild. For example, during rebuild, you can enable advanced index compression for the hr.emp_manager_ix index as follows:
ALTER INDEX hr.emp_manager_ix REBUILD COMPRESS ADVANCED LOW;
The COMPRESSION column in the ALL_INDEXES, ALL_IND_PARTITIONS, and ALL_IND_SUBPARTITIONS views shows whether an index is compressed