"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view


If Oracle doesn't let you run so many times "substr" function in that mv - do some trick to cheat him ;) You can make this "substr" calls before fast refresh by using virtual column like that:

alter table YOUR_TABLE add History_substr as
(DBMS_LOB.SUBSTR (History,2000, 1)) virtual;

do here other "3 more blob columns with similar conversions" as above and then you can use your virtual columns:

CREATE MATERIALIZED VIEW Employee_MV
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT UTL_RAW.CAST_TO_VARCHAR2(History_substr), //BLOB column
       <3 more blob columns with similar conversions>,
       <misc columns from different tables>,
       <rowid columns for tables for REFRESH FAST to work>
FROM   <list of tables with JOINs>


Refreshing a materialized view does not include added column

SELECT * is expanded at execution time, just like with all similar operations (CREATE VIEW, CREATE TABLE AS)

The key word is "early binding" as opposed to "late binding". Postgres saves the list of columns present at execution time of SELECT *, columns added later are not included automatically. The query string itself is not saved, only the internal representation after expanding SELECT * and other stuff like resolving all identifiers.

REFRESH MATERIALIZED VIEW never changes the data definition, only data:

REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view.

The manual could be more explicit about it, but the comparison to the behavior of CREATE TABLE AS made it clear for me:

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view.


When refreshing a materialized view, are indexes also refreshed automatically?

The indexes will of course be kept up-to-date.

A quick test seems to indicate they maintain their size after each REFRESH too, which suggests they are being recreated from scratch. That would make sense because I think the view is created anew for each refresh and basically renamed into place.


Materialized view created successfully but giving query too long error on refreshing

The CREATE process for a Materialized View is different to the REFRESH process.

During CREATE, the Materialized View can be created using any length SQL statement. During REFRESH, the 64K limit described below is enforced:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm

A possible workaround is to chain your materialized view creation. If you can breakdown your query into smaller chunks you can build your final materialized view using a subquery that itself references materialized views.


Materialized view not refreshing

You should check the following points:

  • You specified refresh period as 1 minutes(1/1440 day = 1 min). Is the materialized view refreshed after 1 minute?
  • Try to execute the select part and it is finished running in 1 minute.

Refreshing an existing materialized View

For periodic refresh you must use NEXT clause. To refresh everyday at 00:00:

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT TRUNC(SYSDATE) + 1

To refresh every 6 hours:

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT SYSDATE + 6/24

From documentation (ALTER MATERIALIZED VIEW):

START WITH Clause

Specify START WITH date to indicate a date for the first automatic refresh time.

NEXT Clause

Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then Oracle Database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then Oracle Database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the alter_mv_refresh entirely, then Oracle Database does not automatically refresh the materialized view.

At the time of the next automatic refresh, Oracle Database refreshes the materialized view, evaluates the NEXT expression to determine the next automatic refresh time, and continues to refresh automatically.


"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view

If Oracle doesn't let you run so many times "substr" function in that mv - do some trick to cheat him ;) You can make this "substr" calls before fast refresh by using virtual column like that:

alter table YOUR_TABLE add History_substr as
(DBMS_LOB.SUBSTR (History,2000, 1)) virtual;

do here other "3 more blob columns with similar conversions" as above and then you can use your virtual columns:

CREATE MATERIALIZED VIEW Employee_MV
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT UTL_RAW.CAST_TO_VARCHAR2(History_substr), //BLOB column
       <3 more blob columns with similar conversions>,
       <misc columns from different tables>,
       <rowid columns for tables for REFRESH FAST to work>
FROM   <list of tables with JOINs>


- Technology - Languages
+ Webmasters
+ Development
+ Development Tools
+ Internet
+ Mobile Programming
+ Linux
+ Unix
+ Apple
+ Ubuntu
+ Mobile & Tablets
+ Databases
+ Android
+ Network & Servers
+ Operating Systems
+ Coding
+ Design Software
+ Web Development
+ Game Development
+ Access
+ Excel
+ Web Design
+ Web Hosting
+ Web Site Reviews
+ Domain Name
+ Information Security
+ Software
+ Computers
+ Electronics
+ Hardware
+ Windows
+ PHP
+ ASP/ASP.Net
+ C/C++/C#
+ VB/VB.Net
+ JAVA
+ Javascript
+ Programming
Privacy Policy - Copyrights Notice - Feedback - Report Violation 2018 © BigHow