Very Large Databases use partitioning to improve the ability to support large data sets.  Breaking large tables into smaller manageable chunks.  This allows for partition pruning, which prevents access to partitions which will not have data queried.  Partitioning and indexing work together to provide parallel processing to search multiple partitions at same time as in data ware housing.

Partitioning Methods: 

You can partition data into a single level or two level for a composite partition.  How the data is accessed determines the best implementation method.   Regardless of partitioning method, you have three ways to control distributing of partitions:

1. Range
2. Hash
3. List

Single Level Partitioning

One or more of the columns acts as the partition key spitting the table into multiple sections.

1. Range Partitioning – maps data to a range of values in the partition key. Each row is identified with a mapping to partition key.   This is the most common partitioning method and is commonly used with dates.  Partition is created with a VALUES LESS THEN clause which show the upper bound of a date column.  The highest partition can use a MAXVALUE to provide an upper limit.
2. Hash Partitioning – This method uses a hashing algorithm to map data to partitioning key. Used to evenly spread data among partitions. It is easier to implement then range.  Good to use if no date column exists and no obvious partitioning key exists.
3. List Partitioning – Allows you to provide a list of values for partitioning key column and divides data base on list. You control exactly how rows are mapped to partitions.  You list a set of values to be placed in separate partitions and use the DEFAULT clause to specify a partition for any value not in the list.

Composite Partitions

This combines the basic partitioning methods into multiple sub-partitions of the same method or different methods.  Provides for a finer level of sub-division of data.   Possible composite partitions are:

• Range-Range
• Range-Hash
• Range-List
• List-Range
• List-Hash
• List-List
• Hash-Hash

Larry Catt

OCP

Oracle provides a large amount of storage options to reduce the space required for data storage and increase the speed of retrieval.   This article provides a brief overview of space management in an Oracle 12c database with some general suggestions to increase flexibility and performance.

Local Managed Tablespaces:

You should always use locally managed tablespaces in Oracle, this is where bitmaps in the tablespace to manage all extents and not external in the data dictionary.  Any tablespace can be locally managed, including SYSTEM and SYSAUX.   Advantage of local managed tablespaces:

1. Allocation and deallocation of space is local managed, resulting in higher performance.
2. Local managed temporary tablespaces do not generate undo or redo.
3. The AUTOALLOCATE clause allows for automatically extent sizing.
4. Reduce contention on data dictionary resulting from no access by tablespace.
5. No need to coalesce free extents.

DBMS_SPACE_ADMIN is a package containing procedures specific to locally managed tablespaces.

Use the ‘EXTENT MANAGEMENT LOCAL AUTOALLOCATE’ is the clause to specify local management with auto select of extent sizes which is the default.  To have uniformed extent sizes you must specify, ‘UNIFORM’

Example:

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLESPACE data1 DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’ SIZE 50M;

Two methods you can use for segment space in locally managed tablespace.  Manual and Automatic:  Manual uses a free lists to manage free space in segments.   Automatic is the default method and it uses bitmaps to manage free space and is more efficient.   You can explicitly specify clause:  ‘SEGMENT SPACE MANAGEMENT AUTO’

Example:

CREATE TABLESPACE data1

DATAFILE ‘/ u02/ oracle/ data/ orcl01. dbf’

SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

SEGMENT SPACE MANAGEMENT AUTO;

BASIC TABLE COMPRESSION

Is the lowest level compression and does not compress data in a table from INSERT/UPDATE.   Data is only compressed during bulk loads.   Basic compression is available in EE license and its format is the same as Advance Compression.  It is possible to convert from Basic to Advance, by simply changing the storage definition of the table or partition.

Advance Row Compression

Originally called OLTP Table compression in 11g, Advance Row compression compresses data regardless how it arrived.  It also minimizes overhead in write operations by eliminating duplicates in data blocks even across rows.  Advance Compression is an additional licensing option.

Hybrid Columnar Compression

Hybrid Columnar Compression (HCC) is a method of organizing data within a set of database blocks.   The underlying OS must support HCC and requires the use of Oracle Storage Exadata, Pillar Axiom or Sun ZFS Storage Appliance.  It stores multiple rows by columns, in what is call compression units.  Columns of like data are stored together similar data types, thus can achieve compression of x6 x15.

Tables created in tablespace with compression option will be compressed unless you specify no compression or another compression method in the CREATE TABLE clause.

Segment Shrink

Online segment shrink allows you to reclaim fragmented free space below high watermark in a segment.  With the following benefits:

1. Compacting provides better cache use.
2. Compact data has less data blocks to be scanned.

Segment shrink is performed online without DML operations interference.    Concurrent DML operations are blocked at end of shrink for space deallocation.  Indexes are maintained and usable after shrink.   Unused disk space is reclaimed above and below high water mark.  No additional space is needed during shrink.  By default shrink operation compact data, move the high water mark and release unused space.

Segment shrink requires row movement and thus requires you to enable row movement and disable rowed triggers.  Shrink operations can only happen on segments using locally managed tablespace with ASSM Automatic segment space management.  In ASSM all segment types are shrinkable except:

1. IOT mapping tables
2. Tables with rowed based materialized views.
3. Tables with function-based indexes.
4. SECUREFILE LOBs
5. Compressed tables.

Invoking Online Segment Shrink – SHRINK SPACE

You can shrink space in the following by use of SHRINK SPACE clause

1. Table – with ALTER TABLE..
2. Index-Organized Table – with ALTER TABLE
3. Index – with ALTER INDEX
4. Partition – with ALTER TABLE
5. Sub-partition – with ALTER TABLE
6. Materialized view – with ALTER MATERIALIZED VIEW
7. Materialized view log – with ALTER MATERIALIZED VIEW LOG

Two operations with SHRINK SPACE clause.

1. COMPACT – shrink operation divided into two sections. First phase:  defragments and compacts rows, does not reset high water mark or release space.  You can re-issue shrink operation without the COMPACT operation during off hours to complete deallocation of space and movement of high water mark.
2. CASCADE – This extends shrink operation to all dependent objects.

Example shrink a table and all of its dependent segments:

ALTER TABLE employees SHRINK SPACE CASCADE;

Example shrink a single partition of a partitioned table:

ALTER TABLE customers MODIFY PARTITION cust_P1 SHRINK SPACE;

Larry Catt

OCP