What types of materialized views can be created in an Oracle database?

There are three types of Materialized Views that can be created in an Oracle database:

1. Materialized Views with Aggregates

* The most commonly used materialized view, particularly in Datawarehouse databases.

Example:

CREATE MATERIALIZED VIEW SEQ_AGGR_INFO 
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens
from DATE_TO_SEQNO
group by dato
order by 1, 2;

Requirements:

* The SELECT-list must contain all of the GROUP BY columns (if present)
* There must be a COUNT(*) and a COUNT(column) on any aggregated columns.
* A materialized view logs must be present on all tables referenced in the query that defines the materialized view.
* Valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX.

2. Materialized Views containing only joins

* Materialized views contain only joins and no aggregates

Example:

CREATE MATERIALIZED VIEW active_data
TABLESPACE data1
CACHE
REFRESH FORCE ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT 
    act.q_id, 
    act.doc_type, 
    act.next_q_id, 
    act.prev_q_id,
    act.created, 
    act.created_by, 
    act.last_modified, 
    act.last_modified_by, 
    act.contents, 
    act.accum_value, 
    act.accum_total
FROM active_data act JOIN archived_data arc
ON arc.q_id = act.q_id;

An advantage with these types of materialized views is that expensive joins are precalculated.
Only REFRESH ON COMMIT or REFRESH ON DEMAND is legal with mviews containing only joins. When ON COMMIT is used, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.


3. Nested Materialized Views

* A nested materialized view is a materialized view whose definition is based on another materialized view.

I have no personal experience using this type of materialized view, but Oracle has supplied some examples if you follow the link above.

Sources: Oracle 12.2 Data Warehousing Guide

Some simple timestamp expressions

Here is a simple template for how to add hours, minutes and seconds using Oracle timestamp expressions:

select sysdate, sysdate+1/24 "+ 1hr",  sysdate+1/24/60 "+ 1 min",  sysdate+1/24/60/60 "+ 1 sec", sysdate+10/24/60/60 "+ 10 sec"
from dual;

SYSDATE	+ 1hr	+ 1 min	+ 1 sec	+ 10 sec
17.01.2019 13:34:38	17.01.2019 14:34:38	17.01.2019 13:35:38	17.01.2019 13:34:39	17.01.2019 13:34:48

What is a "fast refresh" of materialized views?

A FAST REFRESH of a materialized view applies only changed data to the materialized view. This way you don't have to rebuild the materialized view every time data in the base tables' change. Oracle keeps a log of changed data in a different object called a materialized view log.

To indicate a FAST REFRESH, use the following syntax:

CREATE MATERIALIZED VIEW SEQ_AGGR_INFO
REFRESH FAST ON COMMIT | ON DEMAND | ON STATEMENT | START WITH SYSDATE NEXT SYSDATE+1/24/60
ENABLE QUERY REWRITE
AS
select RDATE, count(SEQNO) as antall, max(SEQNO) as makssekvens
from DATE_TO_SEQNO
group by dato
order by 1, 2;

ON COMMIT instructs oracle to refresh the materialized view whenever a transaction against the master table commits.

ON DEMAND instructs oracle that the materialized view should not be refreshed unless the user manually executes one of the DBMS_MVIEW refresh procedures.

ON STATEMENT instructs oracle to automatically refresh refresh the materialized view every time a DML operation is performed on any of the materialized view's base tables.

START WITH specifies a datetime expression for the first automatic refresh time. NEXT indicates the interval between automatic refreshes

General restrictions for FAST REFRESH are listed here

The simplest way to see if your database is part of a data guard setup

Execute this query:

select * from v$dataguard_config;

DB_UNIQUE_NAME	PARENT_DBUN	DEST_ROLE	CURRENT_SCN	CON_ID
proddb01	NONE	PRIMARY DATABASE	238029367683	0
proddb01_stby1	proddb01	PHYSICAL STANDBY	238029353772	0

In this example, I am logged into the database proddb01, the primary database.

The columnn PARENT_DBUN is interesting. It displays "DB_UNIQUE_NAME of the parent database, also known as the database that supplies live redo to the destination."

If executed on the standby database, the output will be reversed, but the principle the same.

Documentation for the view V$DATAGUARD_CONFIG can be found here

How to troubleshoot query rewrites

1. Run the script $ORACLE_HOME/rdbms/admin/utlxrw.sql logged in as the schema who owns the materialized view.

2. Analyze the query by logging on as the schema owning the materialized view and execute:

set serveroutput on

begin
      DBMS_MVIEW.EXPLAIN_REWRITE('select dato, max(sekvensnummer)'||
            'from DATE_TO_SEQNUM where dato <= :B1 group by dato','SEQ_AGGREGATED','x');
end;
/

where the first argument is the one you expect to use query rewrite, and the second argument is the name of the materialized view. 3. The potential reasons for the failed rewriting can be found in the table REWRITE_TABLE:

select message from rewrite_table;

What are Index-organized tables and how are they created?

An example of creating an IOT table from my own experience:

CREATE TABLE DATE_TO_SEQNUM (
    DATO DATE, 
    SEQNUM NUMBER(19) NOT NULL,
CONSTRAINT PK_DTS_IOT PRIMARY KEY (DATO,SEQNUM)
)
ORGANIZATION INDEX
TABLESPACE USERS;

If you expect a lot of repeating entries in the IOT, you can use index compression, like this:

CREATE TABLE DATE_TO_SEQNUM (
    DATO DATE, 
    SEQNUM NUMBER(19) NOT NULL,
CONSTRAINT PK_DTS_IOT PRIMARY KEY (DATO,SEQNUM)
)
ORGANIZATION INDEX
TABLESPACE USERS 
COMPRESS;

In my case, the IOT was really this simple. There are some other important directives when creating an IOT, too, which may be applicable in other cases. They are the use of OVERFLOW partition, the INCLUDING keyword, and the PCTTHRESHOLD-clause. I will come back to them when I have had a chance to put them into practice. In the mean time, read about them here.

Here are some statements from the Oracle documentation which defines what an IOT is, and the potential benefits the can provide:

* Index-organized tables are tables stored in an index structure.

* In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index.

* Index-organized tables provide faster access to table rows by primary key or a valid prefix of the key. The presence of non-key columns of a row in the leaf block avoids an additional data block I/O.

* Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order.


Sources: Oracle Database 12.2 documentation

How to create a LIST-RANGE sub-partitioned table in PostgreSQL

Create the table:

CREATE TABLE orders(
  id            integer,
  country_code  VARCHAR(5),
  customer_id   integer,
  order_date    DATE,
  order_total   numeric(8,2)
)
PARTITION BY LIST (country_code);

Create partition for your table. I have defined two. Define it so that it is ready to be further diveded into sub-partitions:

CREATE TABLE orders_NO
 PARTITION OF orders 
 FOR VALUES IN ('no') 
 PARTITION BY RANGE (order_total);

CREATE TABLE orders_SE
 PARTITION OF orders 
 FOR VALUES IN ('se') 
 PARTITION BY RANGE (order_total);

Finally, create your sub-partitions and set their boundaries:

CREATE TABLE small_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_no
 PARTITION OF orders_NO FOR VALUES FROM (4001) to (maxvalue);

CREATE TABLE small_orders_se
PARTITION OF orders_SE FOR VALUES FROM (0) TO (2000);

CREATE TABLE medium_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (2001) TO (4000);

CREATE TABLE large_orders_se
 PARTITION OF orders_SE FOR VALUES FROM (4001) to (maxvalue);