My Oracle Support Banner

Create Materialized View On Prebuilt Table - slow with select rowidtochar(rowid) (Doc ID 1273809.1)

Last updated on FEBRUARY 28, 2019

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Symptoms


A partitioned table was created and loaded with data. Registering a MView against it with the statements below, and the following was noted:
It caused the subquery to be executed.
Expectation is that this command returns immediately without executing the subquery.

CREATE table pin.item_journal_mv tablespace GIA PCTFREE 0
PARALLEL nologging
PARTITION by RANGE (EFFECTIVE_T)
(
PARTITION previous_months
VALUES LESS THAN (1288584000) ,
PARTITION NOV2010
VALUES LESS THAN (1291179600) ,
PARTITION future_months
VALUES LESS THAN (maxvalue)
)
AS
select /*+ use_hash(i j) parallel(i 8) parallel(j 8) */ i.EFFECTIVE_T, i.POID_ID0, i.POID_DB, i.GL_SEGMENT,
j.POID_REV
,j.CREATED_T
,j.MOD_T
,j.READ_ACCESS
,j.WRITE_ACCESS
...
FROM JOURNAL_T J, ITEM_T I
WHERE I.POID_DB IS NOT NULL
AND J.ITEM_OBJ_ID0 = I.POID_ID0
AND J.GL_ID != 0 ;

CREATE MATERIALIZED VIEW pin.item_journal_mv
ON PREBUILT TABLE NEVER REFRESH
ENABLE QUERY REWRITE
AS
select /*+ use_hash(i j) parallel(i 8) parallel(j 8) */ i.EFFECTIVE_T, i.POID_ID0, i.POID_DB, i.GL_SEGMENT,
j.POID_REV
,j.CREATED_T
,j.MOD_T
,j.READ_ACCESS
,j.WRITE_ACCESS
...
FROM JOURNAL_T J, ITEM_T I
WHERE I.POID_DB IS NOT NULL
AND J.ITEM_OBJ_ID0 = I.POID_ID0
AND J.GL_ID != 0 ;

When the 10046 trace of the create mview was taken the following was highlighted as the main action consuming all the time.




Changes

Running with CURSOR_SHARING=SHARED

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.