Hash
partitioning is a partitioning technique where a hash key is used to distribute
rows evenly across the different partitions (sub-tables). This is typically
used where ranges aren't appropriate, i.e. invoiceNumber, productID, OrderID
etc.
Implementation of HASH partition in FXXX_ACCOUNT
table. Below are details step:
Step-1: Check Redefinition possible in
FXXX_ACCOUNT for Partition.
SQL>
EXEC DBMS_REDEFINITION.can_redef_table('Nxxx24', 'Fxxx_ACCOUNT');
PL/SQL
procedure successfully completed.
Step-2: Check Index, constraint &
data in Fxxx_ACCOUNT
SQL>
select count(1) from Fxxx_ACCOUNT;
COUNT(1)
----------
2049406
SQL>
select table_name, index_name from user_indexes where table_name='Fxxx_ACCOUNT';
TABLE_NAME INDEX_NAME
------------------------------
------------------------------
Fxxx_ACCOUNT SYS_IL0001003322C00003$$
Fxxx_ACCOUNT Fxxx_ACCOUNT_PK
SQL>
select constraint_name,
constraint_type,table_name from user_constraints where table_name='Fxxx_ACCOUNT';
CONSTRAINT_NAME C TABLE_NAME
------------------------------
- ------------------------------
SYS_C00404236 C Fxxx_ACCOUNT
Fxxx_ACCOUNT_PK P Fxxx_ACCOUNT
Step-3: Check Partition is already
exists on FXXX_ACCOUNT table
SQL>
SELECT partitioned FROM user_tables WHERE
table_name = 'Fxxx_ACCOUNT';
PAR
---
NO
Step-4: create a partition interim
table Fxxx_ACCOUNT_PAR
SQL> CREATE
TABLE Fxxx_ACCOUNT_PAR(
RECID
VARCHAR2(255) NOT NULL,
XMLRECORD
XMLTYPE
)
PARTITION
BY HASH(RECID) (
partition p1
tablespace NBxxxDATA,
partition p2
tablespace NBxxxDATA,
partition p3
tablespace NBxxxDATA,
partition p4
tablespace NBxxxDATA,
partition p5
tablespace NBxxxDATA,
partition p6
tablespace NBxxxDATA,
partition p7
tablespace NBxxxDATA,
partition p8
tablespace NBxxxDATA,
partition p9
tablespace NBxxxDATA,
partition p10 tablespace NBxxxDATA,
partition p11
tablespace NBxxxDATA,
partition p12
tablespace NBxxxDATA,
partition p13
tablespace NBxxxDATA,
partition p14
tablespace NBxxxDATA,
partition p15
tablespace NBxxxDATA
);
/
Step-5: Start the Redefinition Process
SQL>
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
Session
altered.
SQL>
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
Session
altered.
SQL>
BEGIN
2
DBMS_REDEFINITION.start_redef_table(
3
uname => ' Nxxx24',
4
orig_table => 'Fxxx_ACCOUNT',
5
int_table => 'Fxxx_ACCOUNT_PAR');
6 END;
7 /
PL/SQL
procedure successfully completed.
Note:
this process take quite some time.
Step-6: Synchronize new table with
interim data before index creation
SQL>
BEGIN
2
DBMS_REDEFINITION.sync_interim_table(
3
uname => ' Nxxx24',
4
orig_table => 'Fxxx_ACCOUNT',
5
int_table => 'Fxxx_ACCOUNT_PAR');
6 END;
7 /
PL/SQL
procedure successfully completed.
2 SELECT 'p2', count(1) FROM FBNK_ACCOUNT partition (p2) UNION ALL
3 SELECT 'p3', count(1) FROM FBNK_ACCOUNT partition (p3) UNION ALL
4 SELECT 'p4', count(1) FROM FBNK_ACCOUNT partition (p4) UNION ALL
5 SELECT 'p5', count(1) FROM FBNK_ACCOUNT partition (p5) UNION ALL
6 SELECT 'p6', count(1) FROM FBNK_ACCOUNT partition (p6) UNION ALL
7 SELECT 'p7', count(1) FROM FBNK_ACCOUNT partition (p7) UNION ALL
8 SELECT 'p8', count(1) FROM FBNK_ACCOUNT partition (p8) UNION ALL
9 SELECT 'p9', count(1) FROM FBNK_ACCOUNT partition (p9) UNION ALL
10 SELECT 'p10', count(1) FROM FBNK_ACCOUNT partition (p10) UNION ALL
11 SELECT 'p11', count(1) FROM FBNK_ACCOUNT partition (p11) UNION ALL
12 SELECT 'p12', count(1) FROM FBNK_ACCOUNT partition (p12) UNION ALL
13 SELECT 'p13', count(1) FROM FBNK_ACCOUNT partition (p13) UNION ALL
14 SELECT 'p14', count(1) FROM FBNK_ACCOUNT partition (p14) UNION ALL
15 SELECT 'p15', count(1) FROM FBNK_ACCOUNT partition (p15);
Step-7: The dependent objects will
need to be created against the new table. This is done using the
COPY_TABLE_DEPENDENTS procedure. We can decide which dependencies should be
copied.
SET
SERVEROUTPUT ON
DECLARE
l_errors NUMBER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'NBxxx24',
orig_table => 'Fxxx_ACCOUNT',
int_table => 'Fxxx_ACCOUNT_PAR',
copy_indexes => DBMS_REDEFINITION.cons_orig_params,
copy_triggers => TRUE,
copy_constraints
=> TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_errors,
copy_statistics => FALSE,
copy_mvlog => FALSE);
DBMS_OUTPUT.put_line('Errors='
|| l_errors);
END;
/
Note: we can do that manually instead of COPY_TABLE_DEPENDENTS
Step-8: Finish the Redefinition
Process. At this process interim table
has become the "real" table and their names have been switched in the
data dictionary.
BEGIN
DBMS_REDEFINITION.finish_redef_table(
uname
=> 'Nxxx24',
orig_table => 'Fxxx_ACCOUNT',
int_table
=> 'Fxxx_ACCOUNT_PAR');
END;
Step-9: Check whether partition is
create in Fxxx_ACCOUNT
>
Partition create check IN Fxxx_ACCOUNT
SQL>
SELECT partitioned FROM user_tables WHERE
table_name = 'FXXX_ACCOUNT';
PAR
---
YES
>
Partition wise total row INTO in Fxxx_ACCOUNT
SQL> SELECT 'p1', count(1) FROM FBNK_ACCOUNT partition (p1) UNION ALL2 SELECT 'p2', count(1) FROM FBNK_ACCOUNT partition (p2) UNION ALL
3 SELECT 'p3', count(1) FROM FBNK_ACCOUNT partition (p3) UNION ALL
4 SELECT 'p4', count(1) FROM FBNK_ACCOUNT partition (p4) UNION ALL
5 SELECT 'p5', count(1) FROM FBNK_ACCOUNT partition (p5) UNION ALL
6 SELECT 'p6', count(1) FROM FBNK_ACCOUNT partition (p6) UNION ALL
7 SELECT 'p7', count(1) FROM FBNK_ACCOUNT partition (p7) UNION ALL
8 SELECT 'p8', count(1) FROM FBNK_ACCOUNT partition (p8) UNION ALL
9 SELECT 'p9', count(1) FROM FBNK_ACCOUNT partition (p9) UNION ALL
10 SELECT 'p10', count(1) FROM FBNK_ACCOUNT partition (p10) UNION ALL
11 SELECT 'p11', count(1) FROM FBNK_ACCOUNT partition (p11) UNION ALL
12 SELECT 'p12', count(1) FROM FBNK_ACCOUNT partition (p12) UNION ALL
13 SELECT 'p13', count(1) FROM FBNK_ACCOUNT partition (p13) UNION ALL
14 SELECT 'p14', count(1) FROM FBNK_ACCOUNT partition (p14) UNION ALL
15 SELECT 'p15', count(1) FROM FBNK_ACCOUNT partition (p15);
>
After Partition total row in FXXX_ACCOUNT
SQL>
select count(1) from fbnk_account;
COUNT(1)
----------
2049406
