OraFAQ Forum: Replication » Simple MV not being used.

Home » Server Options » Replication » Simple MV not being used.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Simple MV not being used. [message #246545]

Thu, 21 June 2007 05:05

Jason Mount
Messages: 1
Registered: June 2007

Junior Member

I have created a simple MV based on a query that has been causing me the performance problems. However when i run the query it still uses the base table? Any Ideas?

--Create the Log
CREATE MATERIALIZED VIEW LOG ON BLOCK_EVENT
TABLESPACE SCRATCH NOLOGGING WITH ROWID (COMPANY_ID,TIMESTAMP,EVENT_SEQUENCE,SERVICE_NAME),SEQUENCE INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON BLOCK_EVENT ADD ROWID, SEQUENCE (COMPANY_ID,TIMESTAMP,EVENT_SEQUENCE,SERVICE_NAME) INCLUDING NEW VALUES;

--Now Create the MV

CREATE MATERIALIZED VIEW DBO_BLOCK_EVENT.MV_S_HOME_USERS TABLESPACE SCRATCH USING INDEX TABLESPACE SCRATCH REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS
SELECT TRUNC (TIMESTAMP, 'HH24') TIME, COUNT(*) ,company_id,service_name,event_sequence
FROM BLOCK_EVENT
WHERE company_id = 1272652853
AND event_sequence = 0
GROUP BY TRUNC (TIMESTAMP, 'HH24') ,company_id ,service_name ,event_sequence;

analyze table MV_S_HOME_USERS compute statistics;
alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

-- This is the query that is not using the above MV?

SELECT TRUNC (TIMESTAMP, 'HH24') TIME, COUNT(*)
FROM block_event
AND company_id = 1272652853
AND service_name = 'wf'
AND event_sequence = 0
GROUP BY TRUNC (TIMESTAMP, 'HH24') ;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS
HASH GROUP BY
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID DBO_BLOCK_EVENT.BLOCK_EVENT
INDEX RANGE SCAN DBO_BLOCK_EVENT.BE_TIMESTAMP_IDX

The user has query rewrite and the MV is rewrite capable. Any ideas Many thanks

Attachment: create_block_event.sql
(Size: 4.19KB, Downloaded 2480 times)

Report message to a moderator

Re: Simple MV not being used. [message #246684 is a reply to message #246545]

Thu, 21 June 2007 11:28

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Quote:

However when i run the query it still uses the base table? Any Ideas?

A materialized view is a stored summary containing precomputes results (originating from an SQL select statement).
As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers

CREATE MATERIALIZED VIEW DBO_BLOCK_EVENT.MV_S_HOME_USERS TABLESPACE SCRATCH USING INDEX TABLESPACE SCRATCH REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS
SELECT TRUNC (TIMESTAMP, 'HH24') TIME, COUNT(*) ,company_id,service_name,event_sequence
FROM BLOCK_EVENT
WHERE company_id = 1272652853
AND event_sequence = 0
GROUP BY TRUNC (TIMESTAMP, 'HH24') ,company_id ,service_name ,event_sequence;

Your MV

and now your querry

SELECT TRUNC (TIMESTAMP, 'HH24') TIME, COUNT(*)
FROM block_event
AND company_id = 1272652853
AND service_name = 'wf'
AND event_sequence = 0
GROUP BY TRUNC (TIMESTAMP, 'HH24') ;

you should have MV like

CREATE MATERIALIZED VIEW DBO_BLOCK_EVENT.MV_S_HOME_USERS TABLESPACE SCRATCH USING INDEX TABLESPACE SCRATCH REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT TRUNC (TIMESTAMP, 'HH24') TIME, COUNT(*)
FROM block_event
AND company_id = 1272652853
AND service_name = 'wf'
AND event_sequence = 0
GROUP BY TRUNC (TIMESTAMP, 'HH24') ;

check it for MV

Attachment: The Secrets of Materialized Views.doc
(Size: 46.50KB, Downloaded 3493 times)

[Updated on: Thu, 21 June 2007 11:28]

Report message to a moderator

Previous Topic:	Reg:Read-only Replication
Next Topic:	Replication

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 08 18:36:47 CST 2025