Home » SQL & PL/SQL » SQL & PL/SQL » Materialized Views and Fast Refresh
Materialized Views and Fast Refresh [message #192765] Wed, 13 September 2006 08:45 Go to next message
Hiskrtapps
Messages: 4
Registered: September 2006
Junior Member
Hi.

I need help about Materialized Views.

I created a view using this select but this is not able for fast refresh

select
coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE) as INSERT_DATE,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'YYYY') as XYEAR,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'MM') as XMONTH,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'DD') as XDAY,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'HH24') as XHOUR,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'MI') as XMINUTE,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'SS') as XSECOND,
count(AUDIT_LOG_ID) AS LOGS_NUM,
count(ERROR_LOG_ID) AS ERRORS_NUM
from AUDIT_LOG full join ERROR_LOG
on AUDIT_INSERT_DATE = ERROR_INSERT_DATE
group by
AUDIT_INSERT_DATE, ERROR_INSERT_DATE

the two base tables are like these
AUDIT_LOG (AUDIT_LOG_ID [key], AUDIT_INSERT_DATE, ...)
ERROR_LOG (ERROR_LOG_ID [key], ERROR_INSERT_DATE, ...)

hI created view logs setting both columns in the row_id but it doesn't work
How can I do?

Someone can help me?
thx!
Re: Materialized Views and Fast Refresh [message #192775 is a reply to message #192765] Wed, 13 September 2006 09:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @Anacedent said:
Quote:

It doesn't work
isn't a standard oracle error.

The actual error message was?
Re: Materialized Views and Fast Refresh [message #192779 is a reply to message #192775] Wed, 13 September 2006 09:21 Go to previous messageGo to next message
Hiskrtapps
Messages: 4
Registered: September 2006
Junior Member
"It doesn't work
isn't a standard oracle error.

The actual error message was?"

this
MV with UNION ALL in a view is too complex
subquery or named view in FROM list even after view merging
the materialized view is BUILD DEFERRED
see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

but there isn't any union all (I think it's put by the optimizer, and when I create the view I set "build immediate")


Re: Materialized Views and Fast Refresh [message #192844 is a reply to message #192779] Wed, 13 September 2006 21:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the query. It probably has an OR condition, or an inline view in the FROM clause.

Ross Leishman
Re: Materialized Views and Fast Refresh [message #192893 is a reply to message #192844] Thu, 14 September 2006 02:25 Go to previous messageGo to next message
Hiskrtapps
Messages: 4
Registered: September 2006
Junior Member
this is the complete DDL...
I think the problem is the join


CREATE TABLE TIPIF.AUDIT_LOG (
AUDIT_LOG_ID NUMBER(22,0) NOT NULL,
TRACKING_ID VARCHAR2(50) NOT NULL,
TRACKING_SEQUENCE VARCHAR2(4000) NOT NULL,
AUDIT_INSERT_DATE DATE DEFAULT sysdate NOT NULL,
COMPRESSED CHAR(1) DEFAULT 0 NOT NULL,
COMPRESSION_TYPE VARCHAR2(15) NULL,
COMPRESSED_SIZE NUMBER(22,0) NULL,
MESSAGE BLOB NOT NULL,
MSG_TYPE VARCHAR2(20) NULL,
PRIMARY KEY(AUDIT_LOG_ID)
)
GO
ALTER TABLE TIPIF.AUDIT_LOG
ADD ( CONSTRAINT CHK_AUDIT_LOG_COMPRESSED
CHECK (COMPRESSED IN (0, 1))
NOT DEFERRABLE INITIALLY IMMEDIATE )
GO


CREATE TABLE TIPIF.ERROR_LOG (
ERROR_LOG_ID NUMBER(22,0) NOT NULL,
ERROR_INSERT_DATE DATE DEFAULT sysdate NOT NULL,
ERROR_TYPE VARCHAR2(20) DEFAULT 'LISTENER' NOT NULL,
ERROR_STACK_TRACE VARCHAR2(4000) NOT NULL,
ERROR_MSG VARCHAR2(255) NOT NULL,
ERROR_FULL_CLASS VARCHAR2(255) NOT NULL,
ERROR_CLASS VARCHAR2(255) NOT NULL,
ERROR_PROCESS_STACK VARCHAR2(4000) NOT NULL,
ERROR_MSG_CODE VARCHAR2(50) NOT NULL,
REPUBLISH_SUBJECT VARCHAR2(255) NOT NULL,
PRIMARY KEY(ERROR_LOG_ID)
)
GO
ALTER TABLE TIPIF.ERROR_LOG
ADD ( CONSTRAINT CHK_ERROR_LOG_ERRTYPE
CHECK (ERROR_TYPE IN ('LISTENER', 'MANUAL'))
NOT DEFERRABLE INITIALLY IMMEDIATE )
GO

CREATE MATERIALIZED VIEW LOG ON AUDIT_LOG
WITH SEQUENCE, ROWID (AUDIT_LOG_ID, AUDIT_INSERT_DATE)
INCLUDING NEW VALUES
GO

CREATE MATERIALIZED VIEW LOG ON ERROR_LOG
WITH SEQUENCE, ROWID (ERROR_LOG_ID, ERROR_INSERT_DATE)
INCLUDING NEW VALUES
GO


CREATE MATERIALIZED VIEW MV_COUNT_FULL_LOG
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH sysdate NEXT sysdate + 1/1440
ENABLE QUERY REWRITE
AS
select
coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE) as INSERT_DATE,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'YYYY') as XYEAR,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'MM') as XMONTH,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'DD') as XDAY,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'HH24') as XHOUR,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'MI') as XMINUTE,
to_char(coalesce(AUDIT_INSERT_DATE, ERROR_INSERT_DATE), 'SS') as XSECOND,
count(AUDIT_LOG_ID) AS LOGS_NUM,
count(ERROR_LOG_ID) AS ERRORS_NUM
from AUDIT_LOG full join ERROR_LOG
on AUDIT_INSERT_DATE = ERROR_INSERT_DATE
group by
AUDIT_INSERT_DATE, ERROR_INSERT_DATE
GO
Re: Materialized Views and Fast Refresh [message #192979 is a reply to message #192765] Thu, 14 September 2006 07:56 Go to previous message
Hiskrtapps
Messages: 4
Registered: September 2006
Junior Member
Wow!

this works fine!
this is the solution.
tables are similar!

thx to all!

CREATE MATERIALIZED VIEW LOG ON ATABLE
WITH SEQUENCE, ROWID (A_ID, A_DATE)
INCLUDING NEW VALUES
GO

CREATE MATERIALIZED VIEW LOG ON BTABLE
WITH SEQUENCE, ROWID (B_ID, B_DATE)
INCLUDING NEW VALUES
GO

CREATE MATERIALIZED VIEW MV_AB_LOG
NOLOGGING
PARALLEL
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
(SELECT
A_DATE as X_DATE,
A_DATE,
null AS B_DATE,
to_char(A_DATE, 'YYYY') as XYEAR,
to_char(A_DATE, 'MM') as XMONTH,
to_char(A_DATE, 'DD') as XDAY,
to_char(A_DATE, 'HH24') as XHOUR,
to_char(A_DATE, 'MI') as XMINUTE,
to_char(A_DATE, 'SS') as XSECOND,
'A' AS const,
count(*) as cnt,
count(A_ID) AS numA,
0 AS numB
from ATABLE
group by A_DATE)
union all
(SELECT
B_DATE as X_DATE,
null AS A_DATE,
B_DATE,
to_char(B_DATE, 'YYYY') as XYEAR,
to_char(B_DATE, 'MM') as XMONTH,
to_char(B_DATE, 'DD') as XDAY,
to_char(B_DATE, 'HH24') as XHOUR,
to_char(B_DATE, 'MI') as XMINUTE,
to_char(B_DATE, 'SS') as XSECOND,
'B' as const,
count(*) as cnt,
0 AS numA,
count(B_ID) AS numB
from BTABLE
group by B_DATE)
GO
Previous Topic: how to insert txt file content into my oracle table
Next Topic: Unique SQL Requirement -- Urgent
Goto Forum:
  


Current Time: Fri Dec 02 21:04:29 CST 2016

Total time taken to generate the page: 0.05358 seconds