As your Data warehouse and OLTP databases explode in growth from gigabytes to terabytes, beyond any initial design and planning, the responsiveness of the database and the manageability begin to suffer. The roles of Data Architect and DBA are to understand the data and plan for such growth. Yet, even with all the hours spent on initial planning, things change over time. As part of your role, you must manage the complex growth tossed over the cube wall day-to-day and spend countless hours in attempts to raise performance.

If this sounds like you and your current system, then here is the solution for you, something that has been lying around waiting to help you grab a hold of manageability and responsiveness - “Database Partitioning”. This article will provide you a little help with basic step-by-step instructions and scripting examples, so you can easily make partitioning possible at anytime.

Background / Overview

The purpose of this article will show how you can safely and easily transform non-partitioned tables into partitioned tables. This article shows a general approach to help you get a handle on performance and manageability.

Preliminary Setup

The first step to keep in mind is that all table related objects need to be tracked:

• Primary keys
• Unique keys
• Foreign keys
• Check constraint
• Triggers
• Indexes
• Synonyms
• Comments
• Grants to users
• Grants to roles
• Referenced objects

In order to do this, the script below will generate a new DDL script for all table related objects, along with the partitioning syntax and existing data. It will also reproduce all existing related objects to support the new schema.

The transformation process is created in such a way that we are not deleting any of the non-partitioned tables, they will still exist in the database. This allows us to restore non-partitioned tables if any errors are encountered during the transformation procedure. This is done by renaming the non-partitioned table, its index(s) and trigger(s), and all of its “user-named” constraints.

Input parameters

• First you must define an owner for the new partitioned table and define the name of the table under transformation:

	p_Owner           Varchar2,
	p_Table           Varchar2

• Next, you must define where data and indexes are to be stored:

	
	p_Data_Tblspc     Varchar2,
	p_Index_Tblspc    Varchar2

• In addition, you can pass a partitioned clause to make the transformation procedure unified. This allows for the partitioning transformation to work with any table:

	
	p_Partitioning_Claus Varchar2

Initialization and setup

• If any database object is generated by a DBA or Architect, the length of the object name must be reduced to your length standards. The following procedure checks the length of names and either allows it or generates a new unique object name:

  	
  	Function Get_Name(p_Possible_Name Varchar2) Return Varchar2 Is
  	       v_Resulting_Name Varchar2(30);
  	       v_Prefix         Varchar2(4) Default 'GEN_';
  	Begin
  	       If Length(p_Possible_Name) > 30 Then
  	              v_Resulting_Name := Substr(To_Char(Rawtohex(Sys_Guid())), 1, 26);
  	              v_Resulting_Name := v_Prefix || v_Resulting_Name;
  	       Else
  	              v_Resulting_Name := p_Possible_Name;
  	       End If;
  	       Return v_Resulting_Name;
  	End;
  

• Incoming parameters can be passed in upper or lower case, but will be transformed to upper case by default:

  	
  	v_Table     := Upper(p_Table);
  	v_Owner     := Upper(p_Owner);
  

• All tables under this transformation are renamed to free up a name for the new partitioned table, which will be duplicated to a non-partitioned table:

  	
  	v_Table_Old := Get_Name(v_Table || '_OLD');
  

• The package dbms_metadata is used during the transformation procedure. Normally dbms_metadata.get_ddl function will return a DDL script that defines the storage clause and segment attributes (physical attributes, storage attributes, tablespace, login). If dbms_metadata.get_ddl is used to get table DDL statements, then it also returns all constraints definitions. To suppress storage clause, segment attributes and constraints, execute the following procedures:

  	
  	-- 0. setup transformation to reduce appearance of storage,constraints
  	in ddl 
  	--   
  	coming out from get_ddl
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'STORAGE', False);
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'CONSTRAINTS', False);
  	Dbms_Metadata.Set_Transform_Param(Dbms_Metadata.Session_Transform, 'SEGMENT_ATTRIBUTES', False);
  

Next

Back to DBAsupport.com