Partitioning a Non-Partitioned Oracle system

Tuesday Oct 23rd 2007 by DatabaseJournal.com Staff
Share:

Learn how to bring back manageability and responsiveness using Database Partitioning on your current ”Non-Partitioned” system.

by Alexander Yanushkevich and Bryan Hinds

Find out how to bring back manageability and responsiveness using Database Partitioning on your current ”Non-Partitioned” system.

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);
    

by Alexander Yanushkevich and Bryan Hinds

Eleven steps to create a partitioned table

During this next explanation of eleven steps on creating partitioned tables based on non-partitioned tables, we strongly suggest that you use a test environment with database objects created from Appendix 1.

1.  Create alter table statements to rename the non-partitioned tables using table name and owner:

v_Sql := 'Alter Table ' || v_Owner || '.' || v_Table || 
  ' Rename To ' || v_Table_Old;

Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

Alter Table TEST_SCHEMA.NON_PARTITIONED Rename To NON_PARTITIONED_OLD;

2.  To create a DDL statement that reproduces the original table name plus a partitioning clause, use the dbms_metadata package to get the DDL definition of the non-partitioned table. Since we have already defined the transformation parameters above, all storage clauses, segment attributes and constraints will be suppressed. You can choose to not suppress storage clauses and segment attributes if you want to keep them as is. Table related constraints should not appear in dbms_metadata.get_ddl output, since all constraints will be reconstructed one-to-one for partitioned tables later in this process:

v_Ddl := Dbms_Metadata.Get_Ddl('TABLE', v_Table, v_Owner);
v_Sql := v_Ddl || p_Partitioning_Clause || ' Tablespace ' || 
  p_Data_Tablespace || ' Parallel 8';

Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

CREATE TABLE "TEST_SCHEMA"."NON_PARTITIONED" 
   (	"TEST" NUMBER, 
	"CREATED_DATE" DATE DEFAULT sysdate, 
	"NAME" VARCHAR2(10) DEFAULT 'TEST'
   ) 
PARTITION BY RANGE (CREATED_DATE)  
  (PARTITION p_max VALUES LESS THAN(Maxvalue)) 
Tablespace TABLESPACE_DATA Parallel 8;

3.  Copy records from the original table to the newly partitioned tables. We have used the APPEND hint to improve performance during this step. You also can use the NOLOGGING clause so that it will not write information into the log file. When using the NOLOGGING clause, remember to be careful so you don’t cause problems for your backup procedures.

v_Sql := 'Insert /*+ APPEND*/ Into ' || v_Owner || '.' || 
  v_Table || ' (Select * From ' ||
		 v_Owner || '.' || v_Table_Old || ')';
Dbms_Output.Put_Line(v_Sql || ';');
Dbms_Output.Put_Line('');

Result:

Insert /*+ APPEND*/ Into TEST_SCHEMA.NON_PARTITIONED 
  (Select * From TEST_SCHEMA.NON_PARTITIONED_OLD);

4.  Adding comments to your tables is an important part of database design because it provides additional information about tables and columns to developers and DBAs. If you are following good practices like this, use the script below to reproduce the comments that exist for the table. We have used information stored in dba_tab_comments view to recreate a table comment:

For i In (Select Owner, Table_Name, Comments
			From Dba_Tab_Comments
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'Comment On Table  ' || v_Owner || '.' || 
	   v_Table || ' Is ''' ||
		 Replace(i.Comments, '''', '''''') || '''';

	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

Comment On Table  TEST_SCHEMA.NON_PARTITIONED Is 'table for testing script';

5.  To get the columns comments, we are using dba_col_comments view. Based on information stored in that view, we can recreate column comments:

For i In (Select Owner, Table_Name, Column_Name, Comments
			From Dba_Col_Comments
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'Comment On Column  ' || v_Owner || '.' || v_Table || '.' || i.Column_Name ||
			 ' Is ''' || Replace(i.Comments, '''', '''''') || '''';

	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.TEST Is 'column for testing script';

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.CREATED_DATE Is '';

Comment On Column  TEST_SCHEMA.NON_PARTITIONED.NAME Is '';

6.  The script below provides the functionality to recreate constraints for the new table, based on database view dba_constraints. This view provides information about all existing constraints for non-partitioned tables. If the constraints were named by the user, then we can rename such constraints for non-partitioned tables and create exactly the same constraint for the partitioned table. If the constraint is generated by the system, as are many of them we are getting for Not Null constraints, then the best way to reproduce them for partitioned tables is to use dbms_metadata.get_ddl.

In the case of a Unique and Primary key constraint, we have a situation where a related index can exist in the database. We can just create a statement to rename it.

For i In (Select Con.Owner,
				 Con.Constraint_Name,
				 Con.Generated,
				 Con.Table_Name,
				 Con.Constraint_Type,
				 Con.Index_Owner,
				 Con.Index_Name
			From Dba_Constraints Con
		   Where Con.Table_Name = v_Table
			 And Con.Owner = v_Owner) Loop
	v_Ddl := Dbms_Metadata.Get_Ddl('CONSTRAINT', i.Constraint_Name, i.Owner);
	If i.Generated = 'USER NAME' Then
		v_Sql := 'Alter Table ' || i.Owner || '.' || v_Table_Old || '	Rename Constraint ' ||
				 i.Constraint_Name || ' To ' || Get_Name(i.Constraint_Name || '_OLD') || ';';
		Dbms_Output.Put_Line(v_Sql);
	
		If (i.Constraint_Type In ('U', 'P')) Then
			If i.Index_Owner Is Not Null And i.Index_Name Is Not Null Then
				v_Sql := 'Alter index ' || i.Index_Owner || '.' || i.Index_Name ||
						 ' Rename to ' || Get_Name(i.Index_Name || '_OLD') || ';';
				Dbms_Output.Put_Line(v_Sql);
			End If;
			v_Sql := Replace(v_Ddl, ') ENABLE', ')') || ' using index tablespace ' ||
					 p_Index_Tablespace || ' nologging compute statistics;';
		Else
			v_Sql := v_Ddl || ';';
		End If;
		Dbms_Output.Put_Line(v_Sql);
	Else
		v_Sql := v_Ddl || ';';
		Dbms_Output.Put_Line(v_Sql);
	End If;
End Loop;

Result:

ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" MODIFY ("NAME" NOT NULL ENABLE);
Alter Table TEST_SCHEMA.NON_PARTITIONED_OLD	Rename Constraint PK To PK_OLD;
Alter index TEST_SCHEMA.PK Rename to PK_OLD;
ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" ADD CONSTRAINT "PK" PRIMARY KEY ("TEST")
  using index tablespace TABLESPACE_INDEX nologging compute statistics;
Alter Table TEST_SCHEMA.NON_PARTITIONED_OLD	Rename Constraint UK1 To UK1_OLD;
Alter index TEST_SCHEMA.UK1 Rename to UK1_OLD;
ALTER TABLE "TEST_SCHEMA"."NON_PARTITIONED" ADD CONSTRAINT "UK1" UNIQUE ("CREATED_DATE")
  using index tablespace TABLESPACE_INDEX nologging compute statistics;

7.  Because the new partitioned table must be ready for use immediately after creation, we must provide the same privileges on the new tables to all users and roles that are on the existing non-partitioned tables. To reconstruct grants, we are using dba_tab_privs view:

For i In (Select 'grant ' || Tpr.Privilege || ' on ' || Tpr.Owner || '.' || Tpr.Table_Name ||
	 ' to ' || Tpr.Grantee || Decode(Tpr.Grantable, 'NO', '', ' WITH GRANT OPTION') || ';' Sql_Str
			From Dba_Tab_Privs Tpr
		   Where Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

grant SELECT on TEST_SCHEMA.NON_PARTITIONED to NEW;

8.  All triggers that are available for the non-partitioned tables are selected from the dba_triggers view. Based on the data coming from dba_triggers view, all non-partitioned table triggers are renamed for the partitioned tables. To get the trigger DDL statement, we are using dbms_metadada.get_ddl function:

For i In (Select Owner, Trigger_Name
			From Dba_Triggers Tr
		   Where Table_Owner = v_Owner
			 And Table_Name = v_Table) Loop
	v_Sql := 'alter trigger ' || i.Owner || '.' || i.Trigger_Name || ' rename to ' ||
			 Get_Name(i.Trigger_Name || '_OLD') || ';';
	Dbms_Output.Put_Line(v_Sql);
	v_Sql := Replace(Replace(Replace(Dbms_Metadata.Get_Ddl('TRIGGER', i.Trigger_Name, i.Owner),
						 'ALTER TRIGGER ',
						 Chr(10) || '/' || Chr(10) || 'ALTER TRIGGER '),
						 'END;' || Chr(10) || Chr(10) || Chr(10) || Chr(10) || Chr(10),
						 'END;'),
					 'ALTER TRIGGER "' || i.Owner || '"."' || i.Trigger_Name || '" ENABLE',
 'ALTER TRIGGER "' || i.Owner || '"."' || i.Trigger_Name || '" ENABLE' ||
					 Chr(10) || '/');
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

alter trigger TEST_SCHEMA.TRGGIER1 rename to TRGGIER1_OLD;

CREATE OR REPLACE TRIGGER "TEST_SCHEMA"."TRGGIER1" 
  before insert on non_partitioned
  for each row
Declare
	-- local variables here
Begin
	Null;
End Trggier1;
/
ALTER TRIGGER "TEST_SCHEMA"."TRGGIER1" ENABLE
/

9.  This step recreates the indexes based on the view dba_indexes. We assume that primary and unique keys are created using indexes. Based on that assumption, the script below reduces all indexes related to constraints. Before we create the duplicate index on the new table, we are renaming the original indexes that belong to non-partitioned tables. Using the procedure below, dbms_metadata.get_ddl, we can return the index clause and then modify it slightly to gather statistics in order to improve performance. While creating the index creation, do not forget to apply the NOLOGGING clause. By default, all non-unique indexes are created as local indexes.

For i In (Select Dbms_Metadata.Get_Ddl('INDEX', Ind.Index_Name, Ind.Owner) Sql_Str,
				 Ind.Index_Name,
				 Ind.Owner,
				 Ind.Uniqueness
			From Dba_Indexes Ind
		   Where Table_Owner = v_Owner
			 And Table_Name = v_Table
			 And (Ind.Owner, Ind.Index_Name) Not In
				 (Select Con.Index_Owner, Con.Index_Name
					From Dba_Constraints Con
				   Where Con.Owner = v_Owner
				 And Con.Table_Name = v_Table
					 And Con.Index_Owner Is Not Null
					 And Con.Index_Name Is Not Null)) Loop
	v_Sql := 'alter index ' || i.Owner || '.' || i.Index_Name || ' rename to ' ||
			 Get_Name(i.Index_Name || '_OLD') || ';';
	Dbms_Output.Put_Line(v_Sql);
	v_Ddl := i.Sql_Str;
	v_Sql := v_Ddl || ' COMPUTE STATISTICS NOLOGGING TABLESPACE ' || p_Index_Tablespace;
	If i.Uniqueness = 'NONUNIQUE' Then
		v_Sql := v_Sql || ' LOCAL ';
	End If;
	v_Sql := v_Sql || Chr(10) || '/' || Chr(10);
	Dbms_Output.Put_Line(v_Sql);
End Loop;

Result:

alter index TEST_SCHEMA.N1 rename to N1_OLD;

CREATE INDEX "TEST_SCHEMA"."N1" ON "TEST_SCHEMA"."NON_PARTITIONED" ("NAME") 
COMPUTE STATISTICS NOLOGGING TABLESPACE TABLESPACE_INDEX LOCAL ;

10.  Since previous non-partitioned tables can be used in relationships with any other tables, we have to provide logic to point referential constraints to the new partitioned table. We have used dba_constraints view to find referential constraints. Before creating relation to partitioned tables, the previous referential constraints are dropped:

For i In (Select Con.Constraint_Name,
			 Con.Owner,
			 Con.Table_Name,
			 Dbms_Metadata.Get_Ddl('REF_CONSTRAINT', Con.Constraint_Name, Con.Owner) Sql_Str
			From Dba_Constraints Con ,
					 (Select Constraint_Name
						From Dba_Constraints
					   Where Owner = v_Owner
						 And Table_Name = v_Table) Ref_Con
			   Where Con.r_Constraint_Name = Ref_Con.Constraint_Name
			 And Status = 'ENABLED') Loop
	-- drop foreing key constraint pointing to non-partitioned table         
	v_Sql := 'ALTER TABLE ' || i.Owner || '.' || i.Table_Name || ' Drop CONSTRAINT ' ||
			 i.Constraint_Name;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
	-- create foreing key constraint pointing to partitioned table
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

ALTER TABLE TEST_SCHEMA.REFERENCED_TABLE Drop CONSTRAINT FK;
ALTER TABLE "TEST_SCHEMA"."REFERENCED_TABLE" ADD CONSTRAINT "FK" FOREIGN KEY ("TEST")
  REFERENCES "TEST_SCHEMA"."NON_PARTITIONED" ("TEST") ENABLE;

11.  During the process of renaming the non-partitioned table, a set of objects using the declaration of this table will invalidate themselves. We consider it to be a good practice to recompile affected dependent objects (views, synonyms stored packages, procedures and functions). To find these objects, use dba_dependencies views to help. Using the views, we will create an alter statement that compiles all dependent objects.

For i In (Select 'alter ' || Type || ' ' || Owner || '.' || Name || ' compile' Sql_Str
			From Dba_Dependencies
		   Start With Referenced_Owner = v_Owner
				  And Referenced_Name = v_Table
		  Connect By Prior Name = Referenced_Name
				 And Prior Owner = Referenced_Owner
		   Order By Level) Loop
	v_Sql := i.Sql_Str;
	Dbms_Output.Put_Line(v_Sql || ';');
	Dbms_Output.Put_Line('');
End Loop;

Result:

alter TRIGGER TEST_SCHEMA.TRGGIER1 compile;

alter VIEW TEST_SCHEMA.REFERENCED_VIEW compile;

alter SYNONYM TEST_SCHEMA.NON_PARTITIONED_SYN compile;

alter SYNONYM TEST_SCHEMA.REFERENCED_VIEW_SYN compile;

alter FUNCTION TEST_SCHEMA.REFERENCED_FUNCTION1 compile;

alter FUNCTION TEST_SCHEMA.REFERENCED_FUNCTION2 compile;

The scripts above can also be used to create duplicates of the original table if Partitioning_Clause is defined as Null value.

Conclusion:

The purpose of this article was to 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. The reason for this approach was because the tables in the database were not partitioned when they were created. With a little help from the steps and scripting shown in this article, you can easily make partitioning possible at anytime.

Ph.D. Alexander Yanushkevich

Data Architect

Oracle Certified  Professional Developer

Jeppesen

A Boeing Company

Bryan Hinds

Database Architect

Jeppesen

A Boeing Company

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved