Oracle Database Implementation Guidelines

This section provides specific guidelines for implementing the Oracle database.

Oracle Partitioning

If you use a base index for the partitioning key, rename the index to CM**.

If you use the primary key index of the table as the partitioning key:

• Make the index non-unique.

• Primary constraints should still exist.

The upgrade on the partitioned table works best if the partitioning key is not unique. This allows the upgrade tool to drop the PK constraints if the primary key columns are modified and recreate the PK constraints without dropping the index.

Database Statistic

During an install process, new database objects may be added to the target database. Before starting to use the database, generate the complete statistics for these new objects by using the DBMS_STATS package. You should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values. New statistics should be gathered after a schema object's data or structure are modified in ways that make the previous statistics inaccurate. For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. After updating data in a table, you do not need to collect new statistics on the number of rows, but you might need new statistics on the average row length.

A sample syntax that can be used is as following:

BEGIN

SYS.DBMS_STATS.GATHER_SCHEMA_STATS (

OwnName => 'CISADM'

,Degree => 16

,Cascade => TRUE

,Method_opt => 'FOR ALL COLUMNS SIZE AUTO'

, Granularity => 'ALL' );

END;

Materialized View

Oracle Enterprise Edition supports query rewrite Materialized view. If you use Oracle Enterprise Edition, you can create following Materialized Views to improve performance of the Monitor batch jobs.

Prerequisites

Make sure to set up the following:

1. Set parameter QUERY_REWRITE_ENABLED=TRUE at database level.

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE; OR

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=TRUE SCOPE=BOTH;

2. To create a materialized view in another user's schema you must have the CREATE ANY MATERIALIZED VIEW system privilege. The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example: if the master tables are on a remote database) and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

3. To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges: If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.

To debug materialized views, refer the below URLs:

• Oracle 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-query-rewrite-materialized-views.html

• Troubleshoot Materialized View: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/troubleshooting-problems-with-read-only-materialized-views.html

CREATE MATERIALIZED VIEW F1_BO_LIFECYCLE_STATUS_MVW

(

BUS_OBJ_CD,

LIFE_CY CLE_BO_CD,

BO_STATUS_CD,

BATCH_CD

)

BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS

SELECT BO2.BUS_OBJ_CD,BO.LIFE_CYCLE_BO_CD,BOSA.BO_STATUS_CD,LCBOS.BATCH_CD as LC_BATCH_CD

FROM

F1_BUS_OBJ BO2,

F1_BUS_OBJ BO,

F1_BUS_OBJ_STATUS LCBOS,

F1_BUS_OBJ_STATUS_ALG BOSA

WHERE

BO2.LIFE_CYCLE_BO_CD =BO.LIFE_CYCLE_BO_CD AND

BO.BUS_OBJ_CD = BOSA.BUS_OBJ_CD AND

BOSA.BO_STATUS_SEVT_FLG = 'F1AT' AND

LCBOS.BUS_OBJ_CD = BO.LIFE_CYCLE_BO_CD AND

LCBOS.BO_STATUS_CD = BOSA.BO_STATUS_CD

create synonym SPLUSR.F1_BO_LIFECYCLE_STATUS_MVW for SPLADM.F1_BO_LIFECYCLE_STATUS_MVW;

grant select on F1_BO_LIFECYCLE_STATUS_MVW to FW_DEV;

grant select on F1_BO_LIFECYCLE_STATUS_MVW to SPL_USER;

grant select on F1_BO_LIFECYCLE_STATUS_MVW to SPL_READ;