Table Fragmentation and Reorg Options

Table Fragmentation

Note: Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

How to find Table Fragmentation in Oracle Database

What is Oracle Table Fragmentation?

 If a table is only subject to inserts, there will not be any fragmentation.
 Fragmentation comes with when we update/delete data in table.
 The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

When rows are not stored contiguously, or if rows are split onto more than one block, performance decreases because these rows require additional block accesses.

Note that table fragmentation is different from file fragmentation. When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

HWM is an indicator of USED BLOCKS in the database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark when doing a full table scan.

DDL statement always resets the HWM.

What are the reasons to reorganization of table?

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to find Table Fragmentation?

In Oracle schema there are tables which has huge difference in actual size (size from User_segments) and expected size from user_tables (Num_rows*avg_row_length (in bytes)). This all is due to fragmentation in the table or stats for table are not updated into dba_tables.

Steps to Check and Remove Table Fragmentation:-

1. Gather table stats:

To check exact difference in table actual size (dba_segments) and stats size (dba_tables). The difference between these value will report actual fragmentation to DBA. So, We have to have updated stats on the table stored in dba_tables. Check LAST_ANALYZED value for table in dba_tables. If this value is recent you can skip this step. Other wise i would suggest to gather table stats to get updated stats.

exec dbms_stats.gather_table_stats('&schema_name','&table_name');

2. Check Table size:

Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';

3. Check for Fragmentation in table:

Below query will show the total size of table with fragmentation, expected without fragmentation and how much % of size we can reclaim after removing table fragmentation. table_name and schema_name has to provide as input to this query.

set pages 50000 lines 32767
 select owner,
        table_name,
  round((blocks*8),2)||'kb' "Fragmented size",
  round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
  round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
        ((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % "
 from dba_tables
 where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
 /

 Note: This query fetch data from dba_tables, so the accuracy of result depends on dba_table stats.

If you find reclaimable space % value more than 20% then we can expect fragmentation in the table. Suppose, DBA find 50% reclaimable space by above query, So he can proceed for removing fragmentation.

4. How to reset HWM / remove fragemenation?

We have three options to reorganize fragmented tables:

1. Alter table move (to another tablespace, or same tablespace) and rebuild indexes.
   (Depends upon the free space available in the tablespace)
2. Export and import the table. (difficult to implement in production environment)
3. Shrink command (fron Oracle 10g)
   (Shrink command is only applicable for tables which are tablespace with auto segment space management)

Here, following Options 1 and 3 option by keeping table availability in mind.

Option: 1 Alter table move (to another tablespace, or same tablespace) and rebuild indexes:-

Collect status of all the indexes on the table:-

We will record Index status at one place, So that we get back them after completion of this exercise, 

SQL> select index_name,status from dba_indexes where table_name like '&table_name';
Move table in to same or new tablespace:

In this step we will move fragmented table to same tablespace or from one tablespace to another tablespace to reclaim fragmented space. Find Current size of you table from dba_segments and check if same or any other tablespace has same free space available. So, that we can move this table to same or new tablespace.

Steps to Move table in to same tablespace:

alter table <table_name> move;   ------> Move to same tablespace

OR

Steps to Move table in to new tablespace:

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <new_tablespace_name>;

Now, get back table to old tablespaces using below command

alter table table_name move tablespace old_tablespace_name;

Now,Rebuild all indexes:

We need to rebuild all the indexes on the table because of move command all the index goes into unusable state.

SQL> select status,index_name from dba_indexes where table_name = '&table_name';
STATUS   INDEX_NAME
-------- ------------
UNUSABLE INDEX_NAME      -------> Here, value in status field may be valid or unusable.

SQL> alter index <INDEX_NAME> rebuild online;  -------> Use this command for each index
 Index altered.

SQL> select status,index_name from dba_indexes where table_name = '&table_name';

STATUS   INDEX_NAME
-------- -------------
VALID    INDEX_NAME      -------> Here, value in status field must be valid.

Gather table stats:

 SQL> exec dbms_stats.gather_table_stats('&owner_name','&table_name');
 PL/SQL procedure successfully completed.

Check Table size:

Now again check table size using and will find reduced size of the table.

select table_name,bytes/(1024*1024*1024) from dba_table where table_name='&table_name';

Check for Fragmentation in table:

set pages 50000 lines 32767
 select owner,
        table_name,
  round((blocks*8),2)||'kb' "Fragmented size",
  round((num_rows*avg_row_len/1024),2)||'kb' "Actual size",
  round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
        ((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % "
 from dba_tables
 where table_name ='&table_Name' AND OWNER LIKE '&schema_name'
 /

Option: 3 Shrink command (fron Oracle 10g):-

Shrink command:

Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management. This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table <table_name> enable row movement;
 Table altered.

There are 2 ways of using this command.

1. Rearrange rows and reset the HWM:

 Part 1: Rearrange (All DML's can happen during this time)
 SQL> alter table <table_name> shrink space compact;
 Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)
 SQL> alter table <table_name> shrink space;
 Table altered.

2. Directly reset the HWM:

 SQL> alter table <table_name> shrink space; (Both rearrange and restting HWM happens in one statement)
 Table altered.

Advantages over the conventional methods are:

 1. Unlike "alter table move ..",indexes are not in UNUSABLE state.After shrink command,indexes are updated also.
 2. Its an online operation, So you dont need downtime to do this reorg.
 3. It doesnot require any extra space for the process to complete.


 

Oracle Partitioning

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Partitioning  

Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as Very Large Databases (VLDB). From Oracle Ver. 8.0  Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria . 

Partitioning allows a table, index, or index-organized table to be subdivided into smaller, manageable pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.

Logically the partitioned object remains as a single entity for the consumers, where physically the object is split into a number of relatively smaller partitions. Partitioning feature provides the flexibility to tune the structures for better performance and availability. All the partitions of a partitioned object must have the same logical attributes like colunm names, data types, constraints etc, but each partition can have separate physical attributes like tablespace etc. Partition tables are created using the PARTITION BY clause in the CREATE TABLE / CREATE INDEX commands.

A table can be partitioned upto 1024K – 1 partitions
A table containing LONG or LONG RAW data types cannot be partitioned

Each row in a partitioned table is unambiguously assigned to a single partition. The Partition Key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.
A partition key -

• Determines a unique partition of the object for each row
• Consists of a list of 1 to 16 columns
• Cannot contain a column of type ROWID
• Can contain columns that are NULLable

Overview of Partitioned Tables

Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.

When to Partition a Table

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
• When the contents of a table need to be distributed across different types of storage devices.

Overview of Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.
2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.
4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

When to Partition an Index

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index.
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Overview of Partitioned Index-Organized Tables

Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.

For partitioning an index-organized table:

• Partition columns must be a subset of the primary key columns
• Secondary indexes can be partitioned (both locally and globally)
• OVERFLOW data segments are always equi-partitioned with the table partitions

Benefits of partitioning

There are many compelling reasons to implement Oracle partitioning for larger databases, and Oracle partitioning has become the de-facto standard for systems over 500 gigabytes.  Oracle partitioning has many benefits to improve performance and manageability:

• Stable - Oracle partitioning is a very stable technology and has been used in Oracle since Oracle 8, back in 1997.  Each new release of Oracle improves Oracle partitioning features.
• Robust - Oracle partitioning allows for multi-level keys, a combination of the Range and List partitioning technique. The table is first range-partitioned with Oracle partitioning, and then each individual range-partition is further sub-partitioned using a list partitioning technique with Oracle partitioning. Unlike composite Range-Hash Oracle partitioning, the content of each sub-partition represents a logical subset of the data, described by its appropriate Range and List Oracle partition setup. 
• Faster backups - A DBA can back-up a single Oracle partition of a table, rather than backing up the entire table, thereby reducing backup time.
• Less overhead - Because older Oracle partitioned tablespaces can be marked as read-only, Oracle has less stress on the redo logs, locks and latches, thereby improving overall performance.
• Easier management - Maintenance of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.
• Faster SQL - Oracle is partition-aware, and some SQL may improve is speed by several orders of magnitude (over 100x faster).

- Index range scans : Oracle partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.

-  Full-table scans : Oracle partition pruning only accesses those data blocks required by the query.

-  Table joins : Oracle partition-wise joins take the specific sub-set of the query partitions, causing huge speed improvements on nested loop and hash joins.

-  Updates : Oracle parallel query for partitions improves batch load speed.

In sum, Oracle partitioning has a very-fast payback time and the immediate improvements to performance and stress reduction on the Oracle server makes it a slam-dunk decision.

Suppose if a table contains millions of records, but all the records belong to four years only i.e. 2011, 2012, 2013 and 2014.  And most of the time you are concerned about only one year i.e. you give queries like the following

select sum(amt) from sales where year=2013;

select product,sum(amt) from sales where year=2014 Group by product;

Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improved since oracle will scan only a single partition instead of whole table.

 In Oracle you can partition a table by

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Composite Partitioning

List, Range, and Hash Partitioning

Composite Partitioning

As of 11g version, we saw these new partitioning features. 

• Interval partitioning for tables - This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. You can now partition by date, one partition per month for example, with automatic partition creation. Interval partitions build upon the foundation introduced with range partitioning for Oracle 11g. Interval   partitioning resolves the limitations built into range partitioning when a specific range is unknown by the  developer or DBA creating the partitions for the table. It tells Oracle to automatically setup new  partitions for a particular interval when data inserted to tables are greater than the range partitions. As  such the requirement of interval partitioning dictates that at least one range partition is specified. Once  the range partitioning key is given for the high value of the range partitions, this transition point is used  as the baseline to create interval partitions beyond  this point. When using interval partitioning, consider the following restrictions:
  • You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
  • Interval partitioning is not supported for index-organized tables.
  • You cannot create a domain index on an interval-partitioned table.
  You can create single-level interval partitioned tables as well as the following composite partitioned tables:
  • Interval-range
  • Interval-hash
  • Interval-list
• System Partitioning - The new system partitioning feature in Oracle 11g provides you with the ability to implement and manage new partitions without a specific partition key. Instead, each partition is mapped to a tablespace using the extended partitioning syntax for system partitions. Due to lack of partition keys with system partitioning, the usual performance benefits available for partitioned tables do not exist with system partitions. Another drawback to system partitions is that they cannot be used for partition-wise joins or traditional partition pruning operations. As such, the main benefit of using the new system partitioning is for manageability purposes.

CREATING PARTITION TABLES

Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces then you can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.

Range Partitioning

This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;
for example, value of year. Performance is best when the data evenly distributes across the range

To create a partition table give the following statement

create table sales (year number(4),

                    product varchar2(10),

                   amt number(10,2))

     partition by range (year)

     partition p1 values less than (2011) tablespace u1,

     partition p2 values less than (2012) tablespace u2,

     partition p3 values less than (2013) tablespace u3,

     partition p4 values less than (2014) tablespace u4,

   partition p5 values less than (MAXVALUE) tablespace u5;

In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 2011 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 2012 and it will be stored in tablespace u2. Similarly p3 and p4.

In the above example if you don’t specify the partition p5 with values less than MAVALUE, then you will not be able to insert any row with year above 2014.

Hash partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key

The following example shows how to  create a hash partition table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).

CREATE TABLE products

     (partno NUMBER,

      description VARCHAR2 (60))

   PARTITION BY HASH (partno)

   PARTITIONS 4

   STORE IN (tab1, tab2, tab3, tab4);

List Partitioning

Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.

List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally

The following example creates a table with list partitioning 

Create table customers (custcode number(5),

                  Name varchar2(20),

                  Addr varchar2(10,2),

                  City varchar2(20),

                  Bal number(10,2))

     Partition by list (city),

Partition north_India values ('DELHI','CHANDIGARH'),

Partition east_India values ('KOLKATA','PATNA'),

Partition south_India values ('HYDERABAD','BANGALORE','CHENNAI'),

Partition west India values ('MUMBAI','GOA');

If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the city column the row is stored in that partition.

COMPOSITE PARTITONING

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating composite partitions, you specify the following:

• Partitioning method: range
• Partitioning column(s)
• Partition descriptions identifying partition bounds
• Subpartitioning method: hash
• Subpartitioning column(s)
• Number of subpartitions for each partition or descriptions of subpartitions

The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4).

CREATE TABLE PRODUCTS (partno NUMBER, 

      description VARCHAR(32),

       costprice NUMBER)

  PARTITION BY RANGE (partno)

     SUBPARTITION BY HASH(description)

    SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)

      (PARTITION p1 VALUES LESS THAN (100),

       PARTITION p2 VALUES LESS THAN (200),

       PARTITION p3 VALUES LESS THAN (MAXVALUE));

ALTERING PARTITION TABLES

To add a partition 

You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

For example to add a partition to sales table give the following command.

alter table sales add partition p6 values less than (2015);

To add a partition to a Hash Partition table give the following command.

alter table products add partition;

Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace. 
To add a partition by user define name and in your specified tablespace give the following command.

alter table products add partition p5 tablespace u5;

To add a partition to a List partition table give the following command.

alter table customers add partition central_India values ('BHOPAL','NAGPUR');

Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

To coalesce a hash partition give the following statement.

alter table products coalesce partition;

This reduces by one the number of partitions in the table products.

DROPPING PARTITIONS

To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.

alter table sales drop partition p1;

Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop the partition like this

delete from sales where year=2011;

alter table sales drop partition p1;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Another method of dropping partitions is give the following statement.

ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;

This causes the global index to be updated at the time the partition is dropped.

Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, tablestocks can be range, hash, or list partitioned.

ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;

Merging Partitions

Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.

You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.

You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

For example the merge the partition p2 and p3 into one partition p23 give the following statement.

alter table sales merge partition p2 and p3 into partition p23;

Modifying Partitions: Adding Values

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      ADD VALUES ('KOCHI', 'MANGALORE');

Modifying Partitions: Dropping Values

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITIONstatement instead. 

The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      DROP VALUES ('KOCHI','MANGALORE');

SPLITTING PARTITIONS

You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command

Alter table sales split partition p5 into 

  (Partition p6 values less than (2015),

   Partition p7 values less then (MAXVALUE));

TRUNCATING PARTITON

Truncating a partition will delete all rows from the partition. To truncate a partition give the following statement

alter table sales truncate partition p5;

LISTING INFORMATION ABOUT PARTITION TABLES

To see how many partitioned tables are there in your schema give the following statement

select * from user_part_tables;

To see on partition level partitioning information

select * from user_tab_partitions;