Topic: Performance & Tuning >> Partitioning of Existing Table
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Partitioning of Existing Table |
| Mit |
Posted: Jul 01, 2009 06:17:55 AM |
Total Post: 13
Joined: Jul, 2009
|
| Hi,We are using Oracle 10g & I want to do the Partition of the Existing Table which is having 400,000 data in it. So is this possible to keep the data as it is & do the partition of table? If yes then How? If no then what will be the Other Solution to improve the Performance of Table. |
|
|
 kumar |
| Posted: Jul 01, 2009 06:50:51 AM |  |
|
Total Post: 638
Joined: May, 2006
|
Partitioning an Existing Table
This article presents a simple method for partitioning an existing table using the DBMS_REDEFINITION package. The contents of the article should not be used as an indication of when and how to partition objects, it simply shows the method of getting from A to B. Remember, in many cases incorrect partitioning is worse than no partitioning!
• Create a Sample Schema
• Create a Partitioned Interim Table
• Start the Redefintion Process
• Create Constraints and Indexes
• Complete the Redefintion Process
Create a Sample Schema
First we create a sample schema as our starting point.
-- Create and populate a small lookup table.
CREATE TABLE lookup (
id NUMBER(10),
description VARCHAR2(50)
);
ALTER TABLE lookup ADD (
CONSTRAINT lookup_pk PRIMARY KEY (id)
);
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;
-- Create and populate a larger table that we will later partition.
CREATE TABLE big_table (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
);
DECLARE
l_lookup_id lookup.id%TYPE;
l_create_date DATE;
BEGIN
FOR i IN 1 .. 1000000 LOOP
IF MOD(i, 3) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -24);
l_lookup_id := 2;
ELSIF MOD(i, 2) = 0 THEN
l_create_date := ADD_MONTHS(SYSDATE, -12);
l_lookup_id := 1;
ELSE
l_create_date := SYSDATE;
l_lookup_id := 3;
END IF;
INSERT INTO big_table (id, created_date, lookup_id, data)
VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
END LOOP;
COMMIT;
END;
/
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
CONSTRAINT big_table_pk PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
Create a Partitioned Interim Table
Next we create a new table with the appropriate partition structure to act as an interim table.
-- Create partitioned table.
CREATE TABLE big_table2 (
id NUMBER(10),
created_date DATE,
lookup_id NUMBER(10),
data VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2003 VALUES LESS THAN (TO_DATE('01/01/2004', 'DD/MM/YYYY')),
PARTITION big_table_2004 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')),
PARTITION big_table_2005 VALUES LESS THAN (MAXVALUE));
With this interim table in place we can start the online redefinition.
Start the Redefintion Process
First we check the redefinition is possible using the following command.
EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');
If no errors are reported it is safe to start the redefintion using the following command.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
Depending on the size of the table, this operation can take quite some time to complete.
Create Constraints and Indexes
If there is delay between the completion of the previous operation and moving on to finish the redefinition, it may be sensible to resynchronize the interim table before building any constraints and indexes. The resynchronization of the interim table is initiated using the following command.
-- Optionally synchronize new table with interim data before index creation
BEGIN
dbms_redefinition.sync_interim_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
The constraints and indexes from the original table must be applied to interim table using alternate names to prevent errors. The indexes should be created with the appropriate partitioning scheme to suit their purpose.
-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;
ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade => TRUE);
Complete the Redefintion Process
Once the constraints and indexes have been created the redefinition can be completed using the following command.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
At this point the interim table has become the "real" table and their names have been switched in the data dictionary. All that remains is to perform some cleanup operations.
-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;
-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
The following queries show that the partitioning was successful.
SELECT partitioned
FROM user_tables
WHERE table_name = 'BIG_TABLE';
PAR
---
YES
1 row selected.
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'BIG_TABLE';
PARTITION_NAME
------------------------------
BIG_TABLE_2003
BIG_TABLE_2004
BIG_TABLE_2005
3 rows selected.
For more information see:
• Partitioned Tables And Indexes
• Online Table Redefinition
• DBMS_REDEFINITION
|
|
|
|
|
| Mit |
| Posted: Jul 04, 2009 03:26:05 AM | |
|
Total Post: 13
Joined: Jul, 2009
|
After creating Partition table (ie.BigTable2) while executing DBMS.Redefinition showing error:PLS-00201: identifier 'DBMS_REDEFINITION' must be declared"
So How to resolve this Error. I execute this command as User.
|
|
|
|
|
| kumar |
| Posted: Jul 06, 2009 02:41:26 AM | |
|
Total Post: 638
Joined: May, 2006
|
connect sys/x as sysdba
SQL> grant execute on dbms_redefinition to USERNAME;
Grant succeeded.
SQL> connect USERNAME/PASSWORD
Connected.
SQL> exec dbms_redefinition.can_redef_table('TEST', 'EMP');
|
|
|
|
|
| Mit |
| Posted: Jul 06, 2009 03:58:11 AM | |
|
Total Post: 13
Joined: Jul, 2009
|
Thanks its executing that command but later on when I try to execute the second command of within Procedure ie.
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
Its given me the Again error :
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
Now which grant I have to give to overcome this error..
|
|
|
|
|
| Mit |
| Posted: Jul 06, 2009 07:32:08 AM | |
|
Total Post: 13
Joined: Jul, 2009
|
Hi,I overcome with that error.By Granting lock to that user but now when I'm trying to execute that procedure I'm getting this message.
ERROR at line 1:
ORA-23539: table "ELINK"."SMS_ZERO_FREQUENCY" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 2
So I wait for 20 mins but still it showing me the same result so how long it will take to execute ...or is there any way to find the solution of this...
Please reply as early as possible Thanks.
|
|
|
|
|
| kumar |
| Posted: Jul 10, 2009 05:45:44 AM | |
|
Total Post: 638
Joined: May, 2006
|
Cause: An attempt was made to redefine a table which is currently
involved in an ongoing redefinition.
Action: Do not perform this redefinition operation on this table or wait
till the ongoing redefinition of the table is completed.
|
|
|
|
|
| Gitesh |
| Posted: Jul 11, 2009 02:48:11 PM | |
|
Total Post: 482
Joined: May, 2005
|
My Dear friend Kumar,
DON'T COPY PASTE from any other site. Give proper link to guide any question maker.
Hey Above all you can get from following link. Here is only copy/pasted.
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php
Thanks and regards,
Gitesh Trivedi
www.dbametrix.com
|
|
|
|
|
| Mit |
| Posted: Jul 17, 2009 04:55:44 AM | |
|
Total Post: 13
Joined: Jul, 2009
|
Thanks both of you for sending me the replies but I problem still there that why its not allowing me to do redefinition for that table.as per your solu I waited some time but same error repeated. So what to do next? How to overcome for this?
|
|
|
|
|
| Mit |
| Posted: Jul 31, 2009 02:39:25 AM | |
|
Total Post: 13
Joined: Jul, 2009
|
Thanks. I Done It with your all replies.
once again Thanks.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| menon srivalsala kumar | 638 |
| positive fanatic | 613 |
| Gitesh Trivedi | 482 |
| Jayanta Sur | 480 |
| Vinoth Kumar | 436 |
| Vigyan Kaushik | 394 |
| Gopu Gopi | 352 |
| Vishant Sanghavi | 320 |
|
|
