Creating Materialized View Using Group By Cube Then It Takes Long Time (Doc ID 1251614.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.4 [Release 11.1 to 11.2]
Information in this document applies to any platform.
Reviewed 26-Mar-2013.

Symptoms

Creating Materialized View for query rewrite using group by cube and many columns, then it takes long
time even if the tables are empty.

SQL> CREATE TABLE SAMPLE
(
CL_ID NUMBER(38) NOT NULL,
DK_DAT_MO NUMBER(38) NOT NULL,
DK_DAT NUMBER(38) NOT NULL,
DK_PCS_SR NUMBER(38) NOT NULL,
DK_DIR NUMBER(38) NOT NULL,
DK_BRA_RT NUMBER(38) NOT NULL,
DK_BRA NUMBER(38) NOT NULL,
DK_ORG_CY_SNW NUMBER(38) NOT NULL,
DK_ORG_SNW NUMBER(38) NOT NULL,
DK_AFS_TAF NUMBER(38) NOT NULL,
DK_CED NUMBER(38) NOT NULL,
DK_NWS_AT NUMBER(38) NOT NULL,
DK_NWS_GR NUMBER(38) NOT NULL,
DK_NWS_FY NUMBER(38) NOT NULL,
DK_CAU_CS NUMBER(38) NOT NULL,
DK_SBP NUMBER(38) NOT NULL,
DK_ADT NUMBER(38) NOT NULL,
NO_IMSI NUMBER(38) NOT NULL,
DK_CDT_DC NUMBER(38) NOT NULL,
DK_SVL NUMBER(38) NOT NULL,
CL_GRP_ID NUMBER(38) NOT NULL,
DK_ORG_CY_SMP NUMBER(38) NOT NULL,
DK_ORG_SMP NUMBER(38) NOT NULL,
DK_ORG_HNW NUMBER(38) NOT NULL
)
TABLESPACE USERS;
/

SQL> CREATE TABLE SAMPLE_MVIEW (
CL_ID NUMBER(38) NOT NULL,
DK_DAT_MO NUMBER(38) NOT NULL,
DK_DAT NUMBER(38) NOT NULL,
DK_PCS_SR NUMBER(38) NOT NULL,
DK_DIR NUMBER(38) NOT NULL,
DK_BRA_RT NUMBER(38) NOT NULL,
DK_BRA NUMBER(38) NOT NULL,
DK_ORG_CY_SNW NUMBER(38) NOT NULL,
DK_ORG_SNW NUMBER(38) NOT NULL,
DK_AFS_TAF NUMBER(38) NOT NULL,
DK_CED NUMBER(38) NOT NULL,
DK_NWS_AT NUMBER(38) NOT NULL,
DK_NWS_GR NUMBER(38) NOT NULL,
DK_NWS_FY NUMBER(38) NOT NULL,
DK_CAU_CS NUMBER(38) NOT NULL,
DK_SBP NUMBER(38) NOT NULL,
DK_ADT NUMBER(38) NOT NULL,
NO_IMSI NUMBER(38) NOT NULL
)
TABLESPACE USERS;
/

SQL> set timing on

SQL> SELECT
T.CL_ID, T.DK_DAT_MO, T.DK_DAT,
T.DK_PCS_SR, T.DK_DIR, T.DK_BRA_RT,
T.DK_BRA, T.DK_ORG_CY_SNW, T.DK_ORG_SNW,
T.DK_AFS_TAF, T.DK_CED, T.DK_NWS_AT,
T.DK_NWS_GR, T.DK_NWS_FY, T.DK_CAU_CS,
T.DK_SBP, T.DK_ADT, COUNT(T.NO_IMSI) NO_IMSI
FROM SAMPLE T
group by cube (T.CL_ID, T.DK_DAT_MO, T.DK_DAT,
T.DK_PCS_SR, T.DK_DIR, T.DK_BRA_RT,
T.DK_BRA, T.DK_ORG_CY_SNW, T.DK_ORG_SNW,
T.DK_AFS_TAF, T.DK_CED, T.DK_NWS_AT,
T.DK_NWS_GR, T.DK_NWS_FY, T.DK_CAU_CS,
T.DK_SBP, T.DK_ADT);

no rows selected

Elapsed: 00:00:01.43

SQL> exec dbms_monitor.session_trace_enable ('','',TRUE,TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13

SQL> CREATE MATERIALIZED VIEW SAMPLE_MVIEW
ON PREBUILT TABLE WITH REDUCED PRECISION
ENABLE QUERY REWRITE AS
SELECT
T.CL_ID, T.DK_DAT_MO, T.DK_DAT,
T.DK_PCS_SR, T.DK_DIR, T.DK_BRA_RT,
T.DK_BRA, T.DK_ORG_CY_SNW, T.DK_ORG_SNW,
T.DK_AFS_TAF, T.DK_CED, T.DK_NWS_AT,
T.DK_NWS_GR, T.DK_NWS_FY, T.DK_CAU_CS,
T.DK_SBP, T.DK_ADT, COUNT(T.NO_IMSI) NO_IMSI
FROM SAMPLE T
group by cube (T.CL_ID, T.DK_DAT_MO, T.DK_DAT,
T.DK_PCS_SR, T.DK_DIR, T.DK_BRA_RT,
T.DK_BRA, T.DK_ORG_CY_SNW, T.DK_ORG_SNW,
T.DK_AFS_TAF, T.DK_CED, T.DK_NWS_AT,
T.DK_NWS_GR, T.DK_NWS_FY, T.DK_CAU_CS,
T.DK_SBP, T.DK_ADT);
/

Materialized view created.

Elapsed: 00:14:40.80

SQL> exec dbms_monitor.session_trace_disable ('','');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms