AWS Database Blog

Implementing table partitioning in Oracle Standard Edition: Part 1

Organizations are aggressively adopting the cloud as a standard and actively evaluating their database needs. Amazon RDS for Oracle is a managed service that makes it easy to quickly create Oracle Database instances, enabling you to migrate existing on-premises workloads to the cloud. Migration from on-premises Oracle Database to Amazon RDS for Oracle is quick because it doesn’t involve code conversion.

Oracle Enterprise Edition (Oracle EE) has become the standard adopted by many organizations. However, if you do a more in-depth database assessment, you may find that not every application needs all the features of Oracle EE, and you may be overpaying.

You can significantly reduce your Oracle commercial license usage by switching to Oracle Database Standard Edition (Oracle SE), which is even easier with the Amazon RDS for Oracle License Included (LI) option. Applications with no or minimum Oracle EE features usage are excellent candidates for migrating to Oracle SE.

Oracle table partitioning is a commonly used feature to manage large tables and improve SELECT query performance. Oracle partitioning is only available in the Oracle EE Extra cost option.

This post demonstrates how to implement a partitioning feature for tables in Oracle Standard Edition (Oracle SE) using a single view accessing multiple sub-tables and the INSTEAD OF trigger.

The following diagram illustrates the architecture of this solution.

Creating objects

To implement the solution, start by creating your objects.

  1. To demonstrate the feature, you break the Oracle EE partition tables into individual tables per partition on Oracle SE. Create a sample table using the following code: 
    CREATE TABLE "TESTPART"."PARTTBL_MAIN"
   (    "ORD_DAY" NUMBER(2,0),
        "ORD_MONTH" NUMBER(2,0),
        "ORD_YEAR" NUMBER(4,0),
        "ORD_ID" NUMBER(10,0)
   ) 
  PARTITION BY RANGE ("ORD_YEAR","ORD_MONTH","ORD_DAY")
 (PARTITION "P1"  VALUES LESS THAN (2020, 3, 31),
 PARTITION "P2"  VALUES LESS THAN (2020, 6, 30),
 PARTITION "P3"  VALUES LESS THAN (2020, 9, 30),
 PARTITION "P4"  VALUES LESS THAN (2020, 12, 32));
    SQL
  2. Collect the privilege details of the source table. For this use case, grant SELECT to oraadmin and use the following script to collect the object-level grant details: 
    select 'GRANT ' || PRIVILEGE || ' ON ' || OWNER ||'.' || TABLE_NAME || ' to ' || GRANTEE ||';' "USER_PRIVILGES"  from ALL_TAB_PRIVS_MADE WHERE OWNER = 'TESTPART';

Sample output

USER_PRIVILGES
---------------------------------------------------
GRANT SELECT ON TESTPART.PARTTBL_MAIN to ORAADMIN;
    SQL
  3. Connect Oracle SE Database and create four individual tables, append the unique number to each to identify it like a table partition: 
    sqlplus <username>/<password>@<TNSAlias>
-- Partition like table 1
CREATE TABLE PARTTBL_MAIN_1
(
ord_day NUMBER(2),
ord_month NUMBER(2),
ord_year NUMBER(4),
ord_id NUMBER(10)
);

-- Partition like table 2

CREATE TABLE PARTTBL_MAIN_2
(
ord_day NUMBER(2),
ord_month NUMBER(2),
ord_year NUMBER(4),
ord_id NUMBER(10)
);

-- Partition like table 3
CREATE TABLE PARTTBL_MAIN_3
(
ord_day NUMBER(2),
ord_month NUMBER(2),
ord_year NUMBER(4),
ord_id NUMBER(10)
);

-- Partition like table 4
CREATE TABLE PARTTBL_MAIN_4
(
ord_day NUMBER(2),
ord_month NUMBER(2),
ord_year NUMBER(4),
ord_id NUMBER(10)
);
    SQL
  4. Enter the following code to create a single view that acts as the main partition table by combining all four tables using the UNION ALL statement: 
    -- View based on all partition tables
CREATE VIEW PARTTBL_MAIN AS 
SELECT * FROM PARTTBL_MAIN_1 UNION ALL 
SELECT * FROM PARTTBL_MAIN_2 UNION ALL 
SELECT * FROM PARTTBL_MAIN_3 UNION ALL
SELECT * FROM PARTTBL_MAIN_4;
    SQL

Inserting and distributing data

To insert and distribute your data, complete the following steps:

  1. Enter the following code to create an INSTEAD OF INSERT trigger for the PARTTBL_MAIN view: 
    -- Before insert trigger on view
-- This will propagate the data to respective partition table

CREATE OR REPLACE TRIGGER PARTTBL_MAIN_INSERT
INSTEAD OF INSERT
ON PARTTBL_MAIN
FOR EACH ROW

DECLARE
n_part integer;

BEGIN
  n_part := :NEW.ORD_MONTH;
  IF n_part <= 3  THEN
    insert into PARTTBL_MAIN_1 values(:new.ORD_DAY,:new.ORD_MONTH,:new.ORD_YEAR,:new.ORD_ID);
  ELSIF n_part > 3 and n_part <= 6 THEN
    insert into PARTTBL_MAIN_2 values(:new.ORD_DAY,:new.ORD_MONTH,:new.ORD_YEAR,:new.ORD_ID);
  ELSIF n_part > 6 and n_part <= 9 THEN
    insert into PARTTBL_MAIN_3 values(:new.ORD_DAY,:new.ORD_MONTH,:new.ORD_YEAR,:new.ORD_ID);
  ELSIF n_part > 9 and n_part <= 12 THEN
    insert into PARTTBL_MAIN_4 values(:new.ORD_DAY,:new.ORD_MONTH,:new.ORD_YEAR,:new.ORD_ID);
 END IF;
END;
/
    SQL
  2. Confirm that tables are empty. See the following code: 
    select 'PARTTBL_MAIN',count(*) rowcount from PARTTBL_MAIN
union all
select 'PARTTBL_MAIN_1',count(*) rowcount from PARTTBL_MAIN_1
union all
select 'PARTTBL_MAIN_2',count(*) rowcount from PARTTBL_MAIN_2
union all
select 'PARTTBL_MAIN_3',count(*) rowcount from PARTTBL_MAIN_3
union all
select 'PARTTBL_MAIN_4',count(*) rowcount from PARTTBL_MAIN_4;

'PARTTBL_MAIN'   rowcount
-------------- ----------
PARTTBL_MAIN            0
PARTTBL_MAIN_1          0
PARTTBL_MAIN_2          0
PARTTBL_MAIN_3          0
PARTTBL_MAIN_4          0
    SQL
  3. Create a sequence to control row creation: 
    -- Create a large number of rows for the partitioning
-- Worked examples. It is up to the user to ensure there is appropriate
-- Temporary/Rollback space.
-- Sequence used for control
DROP SEQUENCE partition_seq
/
CREATE SEQUENCE partition_seq
/
    SQL
  4. Insert the data into the PARTTBL_MAIN view. Each time you run the following code, it inserts approximately 93 rows because we insert 1 row per day: 
    BEGIN
FOR j in 1..12 LOOP
FOR i in 1..31 LOOP
INSERT INTO PARTTBL_MAIN VALUES ( i , j, 2020, partition_seq.nextval );
END LOOP;
END LOOP;
END;
/
Commit;
    SQL
  5. Confirm that the rows are inserted into each table, and we get a total row count at the PARTTBL_MAIN view: 
    select 'PARTTBL_MAIN',count(*) rowcount from PARTTBL_MAIN
union all
select 'PARTTBL_MAIN_1',count(*) rowcount from PARTTBL_MAIN_1
union all
select 'PARTTBL_MAIN_2',count(*) rowcount from PARTTBL_MAIN_2
union all
select 'PARTTBL_MAIN_3',count(*) rowcount from PARTTBL_MAIN_3
union all
select 'PARTTBL_MAIN_4',count(*) rowcount from PARTTBL_MAIN_4;

'PARTTBL_MAIN'   rowcount
-------------- ----------
PARTTBL_MAIN          372
PARTTBL_MAIN_1         93
PARTTBL_MAIN_2         93
PARTTBL_MAIN_3         93
PARTTBL_MAIN_4         93
    SQL

Updating the data

To update your data, complete the following steps:

  1. Enter the following code to create an INSTEAD OF UPDATE trigger to the PARTTBL_MAIN view: 
    -- Update trigger
CREATE OR REPLACE TRIGGER PARTTBL_MAIN_UPDATE
INSTEAD OF UPDATE
ON PARTTBL_MAIN
FOR EACH ROW

DECLARE
n_part integer;

BEGIN
  n_part := :NEW.ORD_MONTH;
  IF n_part <= 3  THEN
    update PARTTBL_MAIN_1 set ORD_YEAR=:NEW.ORD_YEAR where ORD_MONTH=:NEW.ORD_MONTH;
  ELSIF n_part > 3 and n_part <= 6 THEN
    update PARTTBL_MAIN_2 set ORD_YEAR=:NEW.ORD_YEAR where ORD_MONTH=:NEW.ORD_MONTH;
  ELSIF n_part > 6 and n_part <= 9 THEN
    update PARTTBL_MAIN_3 set ORD_YEAR=:NEW.ORD_YEAR where ORD_MONTH=:NEW.ORD_MONTH;
  ELSIF n_part > 9 and n_part <= 12 THEN
    update PARTTBL_MAIN_4 set ORD_YEAR=:NEW.ORD_YEAR where ORD_MONTH=:NEW.ORD_MONTH;
 END IF;
END;
/
    SQL
  2. Review the current data using the following SELECT statement on the PARTTBL_MAIN view. See the following code: 
    
select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN group by ORD_MONTH,ORD_YEAR order by 1;

ORD_MONTH   ORD_YEAR   COUNT (*)
---------- ---------- ----------
         1       2020         31
         2       2020         31
         3       2020         31
         4       2020         31
         5       2020         31
         6       2020         31
         7       2020         31
         8       2020         31
         9       2020         31
        10       2020         31
        11       2020         31
        12       2020         31

12 rows selected.
    SQL
  3. Update the PARTTBL_MAIN view and confirm that the records are updated correctly. See the following code: 
    -- Case 1: update ORD_MONTH 1 records with ORD_YEAR as 2021. This will only update data into PARTTBL_MAIN_1 table

update PARTTBL_MAIN set ORD_YEAR=2021 where ORD_MONTH=1;

31 rows updated.

select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN_1 group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT (*)
---------- ---------- ----------
         1       2021         31
         2       2020         31
         3       2020         31

-- Case 2: update records between ORD_MONTH  2 and 3 records with ORD_YEAR as 2022. This will only update data into PARTTBL_MAIN_1 table

update PARTTBL_MAIN set ORD_YEAR=2022 where ORD_MONTH between 2 and 3;

62 rows updated.

select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN_1 group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT (*)
---------- ---------- ----------
         1       2021         31
         2       2022         31
         3       2022         31

commit;

Commit complete.

select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT (*)
---------- ---------- ----------
         1       2021         31
         2       2022         31
         3       2022         31
         4       2020         31
         5       2020         31
         6       2020         31
         7       2020         31
         8       2020         31
         9       2020         31
        10       2020         31
        11       2020         31
        12       2020         31

12 rows selected.

-- Case 3: update records between ORD_MONTH 8 and 12 records with ORD_YEAR as 2030. This will update data into PARTTBL_MAIN_3 table and PARTTBL_MAIN_4

update PARTTBL_MAIN set ORD_YEAR=2030 where ORD_MONTH between 8 and 12;

155 rows updated.

select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT (*)
---------- ---------- ----------
         1       2021         31
         2       2022         31
         3       2022         31
         4       2020         31
         5       2020         31
         6       2020         31
         7       2020         31
         8       2030         31
         9       2030         31
        10       2030         31
        11       2030         31
        12       2030         31

12 rows selected.
    SQL

Deleting the data

To delete your data, complete the following steps:

  1. Enter the following code to create an INSTEAD OF DELETE trigger on the PARTTBL_MAIN view: 
    -- delete trigger on view
CREATE OR REPLACE TRIGGER PARTTBL_MAIN_Delete
INSTEAD OF DELETE
ON PARTTBL_MAIN
FOR EACH ROW

DECLARE
n_part integer;

BEGIN
  n_part := :OLD.ORD_MONTH;
  IF n_part <= 3  THEN
    delete from PARTTBL_MAIN_1 where ORD_MONTH=:OLD.ORD_MONTH;
  ELSIF n_part > 3 and n_part <= 6 THEN
    delete from PARTTBL_MAIN_2 where ORD_MONTH=:OLD.ORD_MONTH;
  ELSIF n_part > 6 and n_part <= 9 THEN
    delete from PARTTBL_MAIN_3 where ORD_MONTH=:OLD.ORD_MONTH;
  ELSIF n_part > 9 and n_part <= 12 THEN
    delete from PARTTBL_MAIN_4 where ORD_MONTH=:OLD.ORD_MONTH;
 END IF;
END;
/
    SQL
  2. Review the current partition data using the following SELECT statement on the PARTTBL_MAIN view: 
    select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT(*)
---------- ---------- ----------
         1       2021         31
         2       2022         31
         3       2022         31
         4       2020         31
         5       2020         31
         6       2020         31
         7       2020         31
         8       2030         31
         9       2030         31
        10       2030         31
        11       2030         31
        12       2030         31

12 rows selected.
    SQL
  3. Delete the records for ORD_MONTH 3 and confirm that the records are deleted successfully. See the following code: 
    SQL> delete from PARTTBL_MAIN where ORD_MONTH=3;

31 rows deleted.

SQL> select ORD_MONTH,ORD_YEAR,count(*) from PARTTBL_MAIN group by ORD_MONTH,ORD_YEAR order by 1;

 ORD_MONTH   ORD_YEAR   COUNT(*)
---------- ---------- ----------
         1       2021         31
         2       2022         31
         4       2020         31
         5       2020         31
         6       2020         31
         7       2020         31
         8       2030         31
         9       2030         31
        10       2030         31
        11       2030         31
        12       2030         31

11 rows selected.
    SQL

Granting privileges on view and individual tables

To grant privileges on the view and individual tables using the grant information you collected, complete the following steps:

  1. Connect to the ORACLE SE instance with a database privilege user, create a user called oraadmin, and grant only connect privileges. See the following code: 
    sqlplus system/<password>@<TNSAlias>
SQL> create user oraadmin identified by oraadmin;

User created.

SQL> grant connect to oraadmin;

Grant succeeded.
    SQL
  2. Test and confirm that oraadmin doesn’t have access to the PARTTBL_MAIN view. The following query should return an ORA-00942 error: 
    SQL> conn oraadmin/oraadmin
Connected.
SQL> select 'PARTTBL_MAIN',count(*) rowcount from TESTPART.PARTTBL_MAIN
union all
select 'PARTTBL_MAIN_1',count(*) rowcount from TESTPART.PARTTBL_MAIN_1
union all
select 'PARTTBL_MAIN_2',count(*) rowcount from TESTPART.PARTTBL_MAIN_2
union all
select 'PARTTBL_MAIN_3',count(*) rowcount from TESTPART.PARTTBL_MAIN_3
union all
select 'PARTTBL_MAIN_4',count(*) rowcount from TESTPART.PARTTBL_MAIN_4;  
                                                    
ERROR at line 1:
ORA-00942: table or view does not exist
    SQL
  3. Connect as a database privilege user and grant privileges on the PARTTBL_MAIN view and all underlying tables: 
    
sqlplus system/<password>@<TNSAlias>
GRANT SELECT ON TESTPART.PARTTBL_MAIN to ORAADMIN;
GRANT SELECT ON TESTPART.PARTTBL_MAIN_1 to ORAADMIN;
GRANT SELECT ON TESTPART.PARTTBL_MAIN_2 to ORAADMIN;
GRANT SELECT ON TESTPART.PARTTBL_MAIN_3 to ORAADMIN;
GRANT SELECT ON TESTPART.PARTTBL_MAIN_4 to ORAADMIN;

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

SQL>
Grant succeeded.
    SQL
  4. Confirm that oraadmin has access to the PARTTBL_MAIN view and underlying tables: 
    select 'PARTTBL_MAIN',count(*) rowcount from PARTTBL_MAIN
union all
select 'PARTTBL_MAIN_1',count(*) rowcount from PARTTBL_MAIN_1
union all
select 'PARTTBL_MAIN_2',count(*) rowcount from PARTTBL_MAIN_2
union all
select 'PARTTBL_MAIN_3',count(*) rowcount from PARTTBL_MAIN_3
union all
select 'PARTTBL_MAIN_4',count(*) rowcount from PARTTBL_MAIN_4;

'PARTTBL_MAIN'   rowcount
-------------- ----------
PARTTBL_MAIN          372
PARTTBL_MAIN_1         93
PARTTBL_MAIN_2         93
PARTTBL_MAIN_3         93
PARTTBL_MAIN_4         93
    SQL

Limitations

A global, unique index key at an overall view level is unsupported; however, you can have unique local indexes at a table level.

Conclusion

In this post, we described how we could use a view and tables per partition and an INSTEAD OF trigger on the view as an alternate solution to implementing partitioning in Oracle SE. You can gather the grants and privilege information from the source database and replicate the same on an individual table and views. You can also use a similar technique for range-list partitioning. Additionally, you can migrate Oracle EE partition data to individual tables in Oracle SE with AWS Data Migration Service (AWS DMS).

In the next post in this series, we describe automating partition maintenance in Oracle SE.

About the Authors

Srinivas Potlachervoois a Lead Database Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help AWS customers to move their on-premises database environment to AWS cloud database solutions.

Bhavesh Rathod is a Senior Database Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.

Samujjwal Roy is a Principal DB Consultant with the Professional Services team at Amazon Web Services. He has been with Amazon for 17+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.