Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Materialized views, question
SickOfOracle
post Jul 23 2008, 10:42 AM
Post #1


Advanced Member
***

Group: Members
Posts: 72
Joined: 19-October 04
From: DC
Member No.: 855



I have been trying to create a materialized view...

LOG

CREATE MATERIALIZED VIEW LOG
ON cdb.reporter
TABLESPACE cdb_views
WITH PRIMARY KEY
INCLUDING NEW VALUES;

VIEW

create materialized view
mviews.mv_reporter
USING NO INDEX
REFRESH FAST
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
com_code1,
com_code2,
pro_factor
from cdb.reporter
where payroll_indicator = 0
and ref_yy||ref_mm >= to_char((sysdate-790.955),'YYYYMM'));

When I do I get the error ...

from cdb.reporter
*
ERROR at line 16:
ORA-12015: cannot create a fast refresh materialized view from a complex query

When I take out the last line "and ref_yy||ref_mm >= to_char((sysdate-790.955),'YYYYMM')" it is created fine.

I supposed that the materalized view just couldn't handle that last line. But a developer ran the following on his oracle instance and he says it worked ...

CREATE MATERIALIZED VIEW LOG ON CDB.REPORTER;

create materialized view
cdb.reporter_est_view
USING NO INDEX
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/96
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
com_code1,
com_code2,
pro_factor
from cdb.reporter
where payroll_indicator = 0
and ref_yy||ref_mm >= to_char((sysdate-182.625),'YYYYMM'));

So from what he's told me the last line is valid in a materialized view. From what I read on line, some functions are now allowed with the refresh fast option. I figured the sysdate was one.

The only other difference I see is that we're version 10.2.0.2 and he's 10.2.0.3.

Can someone explain this one to me?

THanks
JJ
Go to the top of the page
 
+Quote Post
burleson
post Jul 23 2008, 11:40 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,495
Joined: 26-January 04
Member No.: 13



Hi JJ,

First:

- Did you create a MV log for the table?

- Have you set set cursor_sharing=exact?

- Have you set the query rewrite parms?

That's a great question, what exactly constitutes a "complex" query and a "simple" materialized view.

I was taught that any query with aggregation (sum, etc.) or a join was considered complex.

In your case, you are concatenating two columns together (ref_yy||ref_mm) in a Boolean.

Is that complex? Evidently Oracle thinks so. You may want to open an SR.

Also, if 11g is a possibility, the new "virtual columns" may work, as you could reference the concatenation as a single column:

http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm

CODE
ORA-12015 cannot create a fast refresh materialized view from a complex query

Cause: Neither ROWIDs nor primary key constraints are supported for complex queries.

Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.




***************************************************
Here we are, from the docs:

The following are restrictions for fast refresh materialized views with subqueries:

- Materialized views must be primary key materialized views.

- The master's materialized view log must include certain columns referenced in the subquery. For information about which columns must be included, see "Logging Columns in the Materialized View Log".

- If the subquery is many to many or one to many, join columns that are not part of a primary key must be included in the materialized view log of the master. This restriction does not apply to many to one subqueries.

- The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.

- The subquery must use EXISTS to connect each nested level (IN is not allowed).

- Each table can be in only one EXISTS expression.

-The join expression must use exact match or equality comparisons (that is, equi-joins).

- Each table can be joined only once within the subquery.

- A primary key must exist for each table at each nested level.

- Each nested level can only reference the table in the level above it.

- Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row. Multiple OR conditions within a subquery can be connected with an AND condition.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
HAL9000
post Jul 23 2008, 11:54 AM
Post #3


Advanced Member
***

Group: Members
Posts: 880
Joined: 25-September 07
Member No.: 12,336



JJ,

See Metalink Note:179466.1:

QUOTE
The restrictions that prevent snapshots from being fast refreshed depend on
the version of Oracle being used, a full list of these by version is included
in section 3. In all cases the snapshot defining query should:

- refer to fully qualified table names rather than to partial table names.

- refer to remote tables only, not to remote master views or synonyms.

- not generate context sensitive data. For example, do not create a simple
snapshot with a query that uses the SQL functions :SYSDATE, UID or USER.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th September 2014 - 10:58 AM