Home » Server Options » Replication » Simple MV not being used.
Simple MV not being used. [message #246545] Thu, 21 June 2007 05:05 Go to next message
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

Re: Simple MV not being used. [message #246684 is a reply to message #246545] Thu, 21 June 2007 11:28 Go to previous message
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

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

Report message to a moderator

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


Current Time: Wed Oct 01 00:37:37 CDT 2014

Total time taken to generate the page: 0.05183 seconds