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

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

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;
    /
  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.
    
  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.

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;
    /
  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.
  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.

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.
    
  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
    
  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.
  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
    

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 Potlachervoo is a 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 an Oracle Database Cloud Architect 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 Database Specialty Architect with the Professional Services team at Amazon Web Services. He has been with Amazon for 15+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.