SQL & PL/SQL :: How Query Call Embedded Materialized View Automatically
Jul 13, 2010
I have to optimize the queries for user online panel. We have already created 2 materialized views:
mv_channel, mv_area
mv_channel (article_id, area, aweg_txt3, sum(value), sum(units))
mv_area (article_id, area, sum(value), sum(units))
For this query:
SELECT /*+ REWRITE*/
sum(value) as value,
sum(units) as unit,
article_id
from
...
where
...
and area in ('RB','RR','RS')
and aweg_txt3 n ('SB')
...
is the matview mv_channel very useful. The execution time was just 8s-13s instead fo 190s. But if the user also asks for this query but without AWEG_TXT3 in the where-clause, then it lasted 1:25min (longer than if the matview mv_area will be used).
The problem is that, the matview mv_area cannot be used in the case, in which the AWEG_TXT3 is in where clause (because it doesn't have aweg_txt3).
How can i do, so that :
- in case of AWEG_TXT3 in where-clause the matview mv_channel will be used and
- in another one (without AWEG_TXT3 in where-clause) the matview mv_area will be called automatically from the query
View 1 Replies
ADVERTISEMENT
Jan 25, 2011
I am confused to see one materialized view is not refreshing automatically, whereas the same thing I can do manually.
View 2 Replies
View Related
Feb 14, 2012
I have created a MV on Oracle 10gR2 using dblink. Source database is AS400 DB2.MV script:
CREATE MATERIALIZED VIEW MV_BU
TABLESPACE TB_XXX
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
[code]...
Above query should be refreshing the data every minute but I realized that its not doing it.
View 1 Replies
View Related
Aug 17, 2012
tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies
View Related
Nov 15, 2012
I have got a simple materialized view question.
I have a view which is taking long time to get back results and this view is being used in various user queries. I noticed that the SQL of the view access some tables, data of them changes rarely. So it makes sense to retrieve that data in a materialized view and somehow re-use it.
My question is how can I use the materialized view and it's data from a query.
View 2 Replies
View Related
Sep 26, 2013
create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd , xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details) columns s1 VARCHAR2 (255) path 'string[1]' ,s2 VARCHAR2(255) path 'string[2]' ,b VARCHAR2(255) path 'boolean' ,d VARCHAR2(255) path 'date' ) i WHERE pd.mandator_key = 'VSO01' AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and
I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"
*Cause: Neither ROWIDs and 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.
I have found this URL....
View 2 Replies
View Related
Aug 24, 2012
i have a little doubt in Materialized view. i created a materialized view and log with the following query,
create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);
[Code]...
Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with
*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*
But it throwing error,
Error starting at line 5 in command:
alter MATERIALIZED VIEW product_sales_mv AS SELECT b from test_mv
Error report:
SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+
*Cause:+
*Action:+
i guess i am doing wrong. i want to update it without drop and recreate
View 2 Replies
View Related
Apr 27, 2011
I'm trying to create materialized view, successfully create logs for all the tables involved.
CREATE MATERIALIZED VIEW LOG ON tbJournal
WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TBTABS
WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS)
INCLUDING NEW VALUES;
[code]....
The result is ora-12015: cannot create a fast refresh materialized view from a complex query
It does work fine if I remove
AND EXISTS (SELECT 1 FROM tbJournal J
WHERE j.tabstatus NOT BETWEEN -6 AND -4
AND j.linetype = 1
AND T.tabid = J.tabid
AND T.companyid = J.companyid
AND T.storelocid = J.storelocid)
from the where clause
View 10 Replies
View Related
Sep 3, 2012
Is it possible to automate the execution of some reports build with Oracle report builder? Like I want to run such and such report to run daily at 2:00pm or at the end of every month/year.
View 5 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Dec 11, 2012
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
View 5 Replies
View Related
May 2, 2012
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
View 3 Replies
View Related
Nov 5, 2010
We have two schemas(sdt & sdm) in one database.
We have base tables in "sdt" ,
Views are created in "sdm" schema based on the "sdt" schema tables We have granted privileges on the base tables of "sdt" schema to "sdm" schema.
Sometimes views are not able to access by sdm schema.Again we are granted privileges on the base tables of "sdt" schemathen views are able to access.How grants are revoking automatically.
View 15 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
View 7 Replies
View Related
Jul 7, 2010
I have requirement to create materialized views. The design states to use Complete refresh. Now I am using WITH ROW_ID clause.
1. Question - what are the criteria to decide between WITH PRIMARY KEY & WITH ROW_ID clauses? I referred oracle doc, but couldn't exactly get this.
2. Background - The oracle doc mentions that - Primary key materialized views are the default type of materialized view. The master table must contain an enabled primary key constraint, and the defining query of the materialized view must specify all of the primary key columns directly.
Question - I saw some existing materialized views in my project using WITH PRIMARY KEY clause, but all of the primary key columns are not part of the Select query, but still these views are working fine, how is this possible?
3. Background - The oracle doc mentions that - Rowid materialized views must be based on a single table and cannot contain any of the following:
■Distinct or aggregate functions
■GROUP BY or CONNECT BY clauses
■Subqueries
■Joins
■Set operations
Question - But I have created a Rowid materialized view selecting data from more than one table & defining query involving outer joins. This materialized view got created & getting refreshed (complete refresh). But again this is contradicting with Oracle' statement.
View 1 Replies
View Related
Jun 4, 2013
Can one use the CommandType.TableDirect to access a VIEW in oracle?
I have a view that I want to access and rather than call a Stored Procedure that in turn accesses the view, can I do this directly from with .NET?
I am using VS2012, and I am using Oracle 11.2g.
View 2 Replies
View Related
Feb 7, 2013
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
View 3 Replies
View Related
Jul 14, 2011
i have created FAST refresh materialized view.it is eligible for FAST refresh because when i executed SELECT * FROM USER_MVIEWS i could see FAST_REFRESHABLE=DIRLOAD_DML and STALENESS=STALE and it is not getting refreshed(through DBA_JOBS). Why it is in STALE status and how to resolve it. and one of my materialized views is in NEEDS_COMPILE status
View 3 Replies
View Related
Sep 10, 2010
A scheduler job is there to refresh some materialized views. All the views are run under single job name. Some mviews are not refreshing and those are not refreshing in the next run too.if we run those mviews manually then they are working fine from then . after some days those views which i told before are not refreshing and this happens frequently and i check the job_run_details views also and i am not seeing any errors in that.
View 6 Replies
View Related
Dec 11, 2008
I need redefine a Materialized View (add some columns). I try it with:
drop materialized view TCPIP preserve table;
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
ALTER TABLE TCPIP ADD (SUM_IP_OUT_OF_SEQ NUMBER)
/
and than create a new MV:
CREATE MATERIALIZED VIEW TCPIP
ON PREBUILT TABLE USING NO INDEX NEVER REFRESH ENABLE QUERY REWRITE as
SELECT ...
But with Oracle error: ORA-12060: shape of prebuilt table does not match definition query
I check the new MV definition, it is contain the 2 added columns.
View 6 Replies
View Related
Mar 16, 2011
Why we can create indexes only on materialized view and not on normal views?
View 8 Replies
View Related
Sep 9, 2010
Can u explain the syntax of materialized view??????
View 1 Replies
View Related
Oct 20, 2010
How can we find how much space is used by the materialized view.
View 1 Replies
View Related
Jul 5, 2011
I have created a Materialized View (MV) and within the MV DDL have included a statement as per below. However, the MV does not seem to be refreshing on a daily basis. I can see the job in dba_jobs table. So the question, is there some system setting i need to execute or create in order for Oracle to know that it needs to run the Job defined in the dba_jobs table on a daily Basis?
View 8 Replies
View Related
Oct 26, 2012
I have created materialized view which hold few million records.Should i have to analyze the view and compute the statistics after i create the materialized view?
Also, just in case i need further indexing, should i have to take the statistics for the table again?
View 4 Replies
View Related
Jun 15, 2013
I am running into an issue and trying to ascertain issue. Scenario:I have 2 MV creation scripts. The MV is supposed to get populated by connection from schema to another schema USING DB Links.Basically, SAME HOST, SAME RAC DATABASE, just separate schemas. The MV creations are just hanging. I see NO alert log mentions. I ran a SQL trace and yes,
I see: call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 0 0.00 0.00 0 0 0 0Execute 1011 1.40 73.80 0 0 0 0Fetch 1010 1.08 317.57 0 0 0 1010------- ------ -------- ---------- ---------- ---------- ---------- ----------total 2021 2.49 391.38 0 0 0 1010 Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: 109 (recursive depth: 2) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to dblink 2022 0.00 0.00 SQL*Net message from dblink 2021 0.46 242.58
Understandable, but when I do a selective query, the results come back pretty much within 5 seconds.DB version is 11.2.0.3. Is there a BUG that I should know about?
Here is a snippet:
CREATE MATERIALIZED VIEW "SCHEMA"."MV_NAME_MV" USING INDEX REFRESH COMPLETE ON DEMAND AS (SELECT distinct mapguide_persons(pl.location_code) "FULL_NAME_COSTCENTER",mapguide_jobemp(pl.location_code) "TRUNCNAME_JOB",mapguide_empnum(pl.location_code) "FULLNAME_EMPNUMBER_PHONE",mapguide_english(pl.location_code)
[code]....
View 7 Replies
View Related
Oct 2, 2012
we have a materialized view (in Oracle 11g), usually this MV we easily drop and recreate it.
But last few days, i am trying to drop a MV, but running a long time but no result and it keeps running. not dropped also.
Bcos, db performance is good since we tried to a view using script, it is fine. Problem seems only with Materialized view.
where and how to check what is the issue?
View 1 Replies
View Related
Aug 20, 2013
some of MVs become invalid (yes, we did app upgrade over the weekend). I looked on every object reference in the MV, all permissions - can't find anything.I dropped MVs and created them no errors. Status changed to VALID. I decided to re-COMPILE them (because I saw public synonyms for MVs as INVALID). After compilation status changed back to INVALID.Oracle 11.2, Solaris 10.
View 2 Replies
View Related
May 7, 2010
I have got a materialized view which is created over a dblink as below:
CREATE MATERIALIZED VIEW CRIMEREPORTODSV
TABLESPACE ODS_DATA
BUILD IMMEDIATE REFRESH FAST AS
(SELECT * FROM CRIME_INT@CRISP);
This is all fine and works apart from any changes to the source table (CRIME_INT) isn't reflected unless I perform a refresh. Whereas I want any changes to be reflected straight away. I have had to use a Materialized view in this case as one of the columns in the source table is a CLOB and Oracle won't allow creation of a view with a CLOB field over DBLINK.
View 4 Replies
View Related