How to Make Use of Oracle's Data Compression with Materialized Views.
(Doc ID 726343.1)
Last updated on SEPTEMBER 08, 2021
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 10.2.0.4 [Release 9.2 to 10.2]Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Purpose
The aim of this article is to discuss how to make use of the materialized view compression option.
Scope
Facts about MVIEW's:
1. Materialized views store data in regular Oracle tables.
2. The Compress option is used as a storage parameter for the MVIEW container table that holds the data.
3. To make use of compression we need to use direct load operations:
- Create Table As Select (CTAS)
- Parallel INSERT
- Serial INSERT with an APPEND hint
- Use SQL*LOADER with direct option
- Existing data in the database can also be compressed by moving it into compressed form through ALTER TABLE and MOVE statements.
4. Oracle does not compress rows that are subject to committed DML operations until 11g. 11g is the first release that supports DML compression on mview refresh.
The following example provides details of how compression affects the mviews:
Details
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Scope |
| Details |
| References |