A An example of using Materialized Views in a Reporting environment

This appendix describes the process of creating materialized views for Oracle On Track Communication. The use of materialized views is strongly recommended to assist with potential performance issues.

This appendix includes the following sections:

Overview
Materialized Views for Oracle On Track

Overview

The SQL aggregate functions often used in data mining can impact the database performance of an online transaction processing (OLTP) environment like Oracle On Track Communication. In order to avoid this negative performance hit, Oracle On Track Communication looks at implementations of materialized views.

Unlike an ordinary Oracle Database View which does not contain any data, a materialized view is an Oracle Database object that contains the results of a query. Materialized views can be stored in the same database as their base tables or views, or in a separate database.

The materialized views described in this appendix are created in a separate Reporting database. These materialized views store data based on the result of a remote query to an Oracle On Track Business View working in the Reporting View schema. This approach uses a Database Link to query Oracle On Track Business Views remotely. The use of Database Link don't improve performance but it does help with maintenance in a Reporting environment.

Note:

For a very large deployment, the use of Materialized Views from a Database Link suggested in this appendix may no longer be practical as this mandates a Complete refresh.

Materialized Views for Oracle On Track

This section mentions the steps to create some Materialized Views that could be useful for Oracle On Track Communication.

These materialized views are created in a separate Reporting database and the process involves the creation of: new user to create the materialized views, the creation of the database link connection, and the creation of the Materialized Views in the Reporting database.

Note:

These steps describes an example of the process for creating Materialized Views for Oracle On Track Communication, administrators will need to adjust them based on their deployments requirements.

For more information on Materialized Views, please refer to the information available in Oracle Database Data Warehousing Guide and Oracle Database Advanced Replication.

As a user with SYSDBA privileges, create a tablespace in the Reporting database matching the schema prefix from your Oracle On Track Communication install. This example creates a tablespace named DEV_ONTRACK_REPORTING_MV_TS.
```
SQL> create tablespace DEV_ONTRACK_REPORTING_MV_TS;
```
As a user with SYSDBA privileges, create a temporary tablespace. This example creates a temporary tablespace named DEV_ONTRACK_REPORTING_TEMP_MV
```
SQL> create temporary tablespace DEV_ONTRACK_REPORTING_TEMP_MV;
```

Create a new user. This example creates a user named DEV_ONTRACK_REPORTING_MV.

SQL> create user DEV_ONTRACK_REPORTING_MV default tablespace DEV_ONTRACK_REPORTING_MV_TS temporary tablespace DEV_ONTRACK_REPORTING_MV_TEMP identified by <user_password>;

Grant the following permissions the user created in step 3: CONNECT, CREATE DATABASE LINK, CREATE SYNONYM, CREATE MATERIALIZED VIEW, CREATE VIEW, CREATE TABLE.

SQL> grant connect to DEV_ONTRACK_REPORTING_MV;SQL> grant create synonym to DEV_ONTRACK_REPORTING_MV;SQL> grant create database link to DEV_ONTRACK_REPORTING_MV;SQL> grant create materialized view to DEV_ONTRACK_REPORTING_MV;SQL> grant create view to DEV_ONTRACK_REPORTING_MV;
SQL> grant create table to DEV_ONTRACK_REPORTING_MV;

Change the quota for the user created in step 3. This example changes the quota to 2000M on the new tablespace DEV_ONTRACK_REPORTING_MV_TS.

Note:
The quota value of 2000M may need to change dependent on the deployment. However, this value doesn't equal the used space on the Oracle On Track Communication server as content information is not going to be reproduced.
```
SQL> alter user DEV_ONTRACK_REPORTING_MV quota 2000M on DEV_ONTRACK_REPORTING_MV_TS;
```
Add a tnsnames entry to the Oracle On Track Communication database in the tnsname.ora file of the Reporting database server.
As the user created in step 3, create the database link to the Oracle On Track Communication database. Use the user of the Reporting View schema in the Oracle On Track database to connect.
```
SQL> create database link ontrack connect to DEV_ONTRACK_REPORTIINGVIEW identified by <user_password> using 'ontrack';
```

As the user created in step 3, create synonyms for the Oracle On Track Business View. These examples create synonyms for Oracle On Track Business Views in the DEV_ONTRACK schema.

SQL> create synonym xcontent_rv for DEV_ONTRACK.xcontent_rv@OnTrack;
SQL> create synonym xcontentobjects_rv for DEV_ONTRACK.xcontentobjects_rv@OnTrack;
SQL> create synonym xcontentsizes_rv for DEV_ONTRACK.xcontentsizes_rv@OnTrack;
SQL> create synonym xuser_rv for DEV_ONTRACK.xuser_rv@OnTrack;
SQL> create synonym xusernames_s_rv for DEV_ONTRACK.xusernames_s_rv@OnTrack;

As the user created in step 3, create the materialized views. The examples listed create materialized views using refresh intervals staggered to avoid refreshing all at the same time. The normal use case for this data does not require fully up to data results as the data is often used in reports generated on a period basis.

Note:

Administrators should adjust the refresh dates to those outside of the normal peek working hours. For example, if the system operates in one timezone, administrators could set the refresh to midnight every weekend.

Note:

These examples are for illustrative purposes, administrators will need to adjust them based on the deployments requirements. For more information on Materialized Views, refer to the information available in Oracle Database Data Warehousing Guide and Oracle Database Advanced Replication.

To get the total number of users in Oracle On Track:

SQL> CREATE MATERIALIZED VIEW
XCountUser_MV 
REFRESH COMPLETE 
START WITH SYSDATE 
NEXT TRUNC(SYSDATE + 7) 
AS 
(SELECT COUNT(objectid) AS "NbrUsers" FROM dev_ontrack_reporting_mv.xuser_rv);

To get the total number of objects in Oracle On Track per object type:

SQL> CREATE MATERIALIZED VIEW
XTrackInfo_MV
REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+1/96
AS   
(SELECT COUNT(*), objecttype FROM dev_ontrack_reporting_mv.xcontentobjects_rv GROUP BY objecttype);

To get the nunmber of objects created in Oracle On Track per object type and creation day:

SQL> CREATE MATERIALIZED VIEW
XTrackCreation_MV 
REFRESH COMPLETE 
START WITH SYSDATE 
NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+2/96 
AS 
(SELECT count(*), trunc(createdtimestamp,'DD'), objecttype FROM dev_ontrack_reporting_mv.xcontentobjects_rv GROUP BY trunc(createdtimestamp,'DD'), objecttype);

To get the names of the users in Oracle On Track:

SQL> CREATE MATERIALIZED VIEW
Xusernames_s_MV
REFRESH COMPLETE
START WITH SYSDATE
NEXT trunc(SYSDATE+7)+2/96
AS
(SELECT * FROM dev_ontrack_reporting_mv.xusernames_s_rv);

To get the number of objects created in Oracle On Track in the last 7 days per object type:

SQL> CREATE MATERIALIZED VIEW
XContentCount_7_Days
REFRESH COMPLETE
START WITH SYSDATE
NEXT trunc(SYSDATE+7)+3/96 
AS 
(SELECT COUNT(*) AS "Count" , objecttype AS Name FROM dev_ontrack_reporting_mv.xcontentobjects_rv WHERE createdtimestamp > SYSTIMESTAMP-7 GROUP BY objecttype);

To get the number of objects created in Oracle On Track per user:

SQL> CREATE MATERIALIZED VIEW
XContent_MV(createdbyid, parentobjecttype, contentbloblength)
REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+1/96
AS(SELECT createdbyid,          COUNT(*) as "COUNT",          parentobjecttype     FROM dev_ontrack_reporting_mv.xcontentobjects_rv  GROUP BY createdbyid, parentobjecttype);

To get size information of the content created in Oracle On Track per object type:

SQL> CREATE MATERIALIZED VIEW
XContent_MV(parentobjecttype, contentbloblength, percentage)
REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+1/96
AS(SELECT SUBSTR(parentobjecttype,8) AS "Type",        SUM(contentbloblength) AS "Total Content Size",       (SUM(contentbloblength)/(SELECT SUM(contentbloblength)                                  FROM xcontentsizes_rv                                 WHERE createdbyid IS NOT NULL))*100 AS "Percentage"   FROM dev_ontrack_reporting_mv.xcontentsizes_rv  WHERE createdbyid IS NOT NULLGROUP BY SUBSTR(parentobjecttype,8)HAVING SUM(contentbloblength)>0);

To get the total of content created in Oracle On Track per object type, MIME content-type, and creation day:

SQL> CREATE MATERIALIZED VIEW
XTrackCreation_MV ("COUNT","CREATIONDATE","OBJECTTYPE","CONTENTMIMETYPE") REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+2/96
AS(SELECT COUNT(*), TRUNC(createdtimestamp,'DD'), objecttype, contentmimetype FROM dev_ontrack_reporting_mv.xcontentobjects_rv GROUP BY trunc(createdtimestamp,'DD'), objecttype, contentmimetype);

To get information about Oracle On Track users as well as their log in and log out status:

SQL> CREATE MATERIALIZED VIEW
XUser_MV("OBJECTID","LASTCONNECTEDDATE","LASTDISCONNECTEDDATE")
REFRESH COMPLETE
START WITH SYSDATE
NEXT NEXT_DAY(TRUNC(SYSDATE), 'SUNDAY')AS  (SELECT * FROM dev_ontrack_reporting_mv.XUSER_RV);

To get new content created in Oracle On Track in the last 7 days.

SQL> CREATE MATERIALIZED VIEW
XNewContent_MV("NAME","COUNT_NEWCONTENT")REFRESH COMPLETE
START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+7/96 
AS (SELECT 'Conversation with new content' , count(distinct conversationid)FROM dev_ontrack_reporting_mv.XCONTENT_RV WHERE objecttype = 'waggle/version' AND createdtimestamp > systimestamp - 7);

To get users who have created new content in Oracle On Track in the last 7 days:

SQL> CREATE MATERIALIZED VIEW
XActiveUser_MV("USER_ACTIVE","OBJECTTYPE")
REFRESH COMPLETE
START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+8/96
AS (SELECT count(distinct createdbyid) AS "User Active" , objecttype AS ObjecttypeFROM dev_ontrack_reporting_mv.XCONTENT_RV WHERE objecttype IN ('waggle/version', 'waggle/file' , 'waggle/tag')   AND createdtimestamp > systimestamp - 7GROUP BY objecttype);

To get the size of the new content created in the last 7 days in Oracle On Track:

SQL> CREATE MATERIALIZED VIEW
XContentSizeNewFile_MV("CONTENTBLOBLENGTH") 
REFRESH COMPLETE 
START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+8/96 
AS 
(SELECT SUM(contentbloblength) AS "Total Content Size" FROM dev_ontrack_reporting_mv.xcontentsizes_rv WHERE CREATEDTIMESTAMP > SYSTIMESTAMP - 7);

To get size information of the content created in Oracle On Track per object type:

SQL> CREATE MATERIALIZED VIEW
XContent_MV("TYPE", "CONTENTSIZE", "COUNT" )
REFRESH COMPLETE
START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+10/96
AS
(SELECT SUBSTR(parentobjecttype,8) AS "Type",       SUM(contentbloblength) AS "ContentSize",       COUNT(parentobjecttype) AS "Total" FROM dev_ontrack_reporting_mv.xcontentsizes_rvWHERE createdbyid IS NOT NULL  AND contentbloblength IS NOT NULLGROUP BY SUBSTR(parentobjecttype,8)HAVING SUM(contentbloblength)>0);

To get size information of the content created in Oracle On Track per user and crreation day:

SQL> CREATE MATERIALIZED VIEW
XUserContent_MV("USERID","CONTENTSIZE","CREATIONDATE")
REFRESH COMPLETE START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+12/96
AS
(SELECT createdbyid, ROUND(SUM(contentbloblength/1024/1024),6), TRUNC(createdtimestamp,'DD')  FROM dev_ontrack_reporting_mv.xcontentsizes_rv WHERE createdbyid IS NOT NULL   AND contentbloblength IS NOT NULL GROUP BY createdbyid, TRUNC(createdtimestamp,'DD'));

To get the detail of MIME content-type created in Oracle On Track per user:

SQL> CREATE MATERIALIZED VIEW
XUserContentType_MV("USERID","TYPE","COUNTTYPE")
REFRESH COMPLETE
START WITH SYSDATE NEXT NEXT_DAY(TRUNC(SYSDATE),'SUNDAY')+13/96
AS(
SELECT createdbyid,  REGEXP_SUBSTR(contentmimetype,'[^/]+',1,2), COUNT(contentmimetype)  FROM dev_ontrack_reporting_mv.xcontentsizes_rv WHERE createdbyid IS NOT NULLGROUP BY createdbyid, REGEXP_SUBSTR(contentmimetype,'[^/]+',1,2));