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

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 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.



select rowidtochar(rowid)
from
"PIN"."JOURNAL_T" where rownum=:"SYS_B_0" union select rowidtochar(rowid)
from dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 1856.52 2012.01 8519184 11080467 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1856.53 2012.01 8519184 11080467 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT UNIQUE (cr=11080467 pr=8519184 pw=0 time=829346040 us)
2 UNION-ALL (cr=11080467 pr=8519184 pw=0 time=829346158 us)
1 COUNT (cr=11080464 pr=8519184 pw=0 time=829346038 us)
1 FILTER (cr=11080464 pr=8519184 pw=0 time=829346031 us)
555065967 PARTITION RANGE ALL PARTITION: 1 45 (cr=11080464 pr=8519184 pw=0 time=1665198065 us)
555065967 TABLE ACCESS FULL JOURNAL_T PARTITION: 1 45 (cr=11080464 pr=8519184 pw=0 time=1665239833 us)
1 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=93 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 266354 0.30 772.46



Changes

Running with CURSOR_SHARING=SHARED

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