Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Materialized View problem 2

Status
Not open for further replies.

emblem

Programmer
Jun 25, 2002
26
I am trying to create this MView for a PeopleSoft CRM project. Oracle 10.2.0.4
The A table has 480,000 rows, but only 100 of them meet the A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER' condition. This SQL is hard part of a larger SQL executed constantly. Even with an index scan, it takes 3 sec. With an Mview, it should be << 1 sec.

Code:
CREATE MATERIALIZED VIEW LOG ON PS_RA_CMPGN_WAVE 
WITH ROWID, SEQUENCE(BUSINESS_UNIT,RA_CAMPAIGN_ID,RA_CMPGN_WAVE_ID,RA_OFFER_ID)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON PS_RA_PROMO_OFFER 
with ROWID, SEQUENCE(SETID,RA_PROMOTION_CODE, CAB_SOURCE_YEAR)
INCLUDING NEW VALUES;


  1  CREATE MATERIALIZED VIEW SYSADM.PS_CAB_PROMCD_MATVW
  2       REFRESH FAST ON COMMIT WITH ROWID
  3       ENABLE QUERY REWRITE
  4            AS
  5       SELECT
  6      A1.rowid
  7     ,A.rowid        
  8     ,a1.setid
  9     ,A.RA_CAMPAIGN_ID
 10     ,A.RA_CMPGN_WAVE_ID
 11     , A.RA_OFFER_ID
 12     , count(*)
 13     from PS_RA_CMPGN_WAVE A
 14         ,PS_RA_PROMO_OFFER A1
 15     WHERE  A.BUSINESS_UNIT = A1.BUSINESS_UNIT               and
 16            A.RA_CAMPAIGN_ID = A1.RA_CAMPAIGN_ID             and
 17            A.RA_CMPGN_WAVE_ID = A1.RA_CMPGN_WAVE_ID and
 18            A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER'
 19     group by
 20          a1.rowid
 21         ,a.rowid
 22         ,a1.setid
 23         ,A.RA_CAMPAIGN_ID
 24         ,A.RA_CMPGN_WAVE_ID
 25*        ,A.RA_OFFER_ID
 26  /
          A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER'
                           *
ERROR at line 18:
ORA-00904: "ROWID": invalid identifier

but line 18 has nothing to do with rowid.

I have read rwijk's excellent mongraph about MVs at
but he does not mention this error.

Any suggestions appreciated.
Many thanks - emblem
 
emblem,

I believe that you're doing something fundamentally wrong here. The oracle documentation states

ROWID Materialized Views
For backward compatibility, Oracle supports ROWID materialized views in addition to the default primary key materialized views. A ROWID materialized view is based on the physical row identifiers (rowids) of the rows in a master. ROWID materialized views should be used only for materialized views based on master tables from an Oracle7 database, and should not be used when creating new materialized views based on masters from Oracle8 or higher databases.

You simply should not be creating this view. Rowid MV's are also subject to further restrictions, which I suspect your query is contravening.

See for further details.


Regards

T
 
Thargtheslayer, thank you for these insights. Being new to materialized views, I was following the instructions and examples in the post I cited, but your source is very authoritative. I think it is time to rewrite this view!
 
Emblem,

I concur with your idea to re-write the thing.
I also am suspicous of the requirement here. Third party tools generally don't produce the best queries.

Can you find out what the business activity is here, and obtain a statement of requirement? I suspect that this whole thing can be avoided by suitably fast queries, instead of a MV.

Regards

T
 
Your error is not related to the antiquated type of materialized view you are trying to create. Rather, it is because you can't create a materialized view with a column named "rowid". Think about it: How is Oracle supposed to know whether you are referencing the actual column rowid or the pseudocolumn rowid if it allowed a table or MV that had both?

So, I can recreate your error with the rowid column:

Code:
SQL> create table test_tbl (c1 varchar2(10));

Table created.

SQL> create materialized view test_mv
  2    refresh force on commit with rowid
  3    as
  4      select a.rowid
  5            ,a.c1
  6            ,count(*)
  7      from test_tbl a
  8        where a.c1 <> 'SOME_VALUE'
  9      group by a.rowid
 10              ,a.c1;
      where a.c1 <> 'SOME_VALUE'
                    *
ERROR at line 8:
ORA-00904: "ROWID": invalid identifier

But if I rename rowid as something else, there is no error.

Code:
  1  create materialized view test_mv
  2    refresh force on commit with rowid
  3    as
  4      select a.rowid as myrowid
  5            ,a.c1
  6            ,count(*)
  7      from test_tbl a
  8        where a.c1 <> 'SOME_VALUE'
  9      group by a.rowid
 10*             ,a.c1
SQL> /

Materialized view created.

So you should rethink what you are actually trying to accomplish. Probably you don't have to include the rowid column in your MV at all.
 
Thanks, Karluk.
That was my original idea. But when I try to build the MV without rowid, I get a strange error

A.RA_OFFER_ID <> 'CAB_DUMMY_OFFER'

ERROR at line 19:
ORA-01031: insufficient privileges

This message has nothing to do with the inequality, and I have full MV privileges per the DBA.
I have opened a SR with Oracle support to get to the bottom of this.

 
You would have to provide the statement that generated the ORA-01031 error. It's not because you leave out rowid from the MV definition. That works just fine:

Code:
  1  create materialized view test_mv2
  2    refresh force on commit with rowid
  3    as
  4      select a.c1
  5      from test_tbl a
  6*       where a.c1 <> 'SOME_VALUE'
SQL> /

Materialized view created.
 
Incidentally, I suspect that thargtheslayer's suggestion to use primary key materialized views, although admirable in intention, will not work in practice. I happen to have a PeopleSoft schema available from a previous project and it appears that PS has the amusing habit of creating its tables without any primary keys defined. Although it might be possible in theory to define primary keys on the two tables in your materialized view, I seriously doubt it's a good idea. You're a braver dba than I am if you're willing to make ad-hoc changes to anything as complicated as a PeopleSoft installation.
 
I see why you were getting the ORA-01031: insufficient privileges error. You must have been logged in with your dba account rather than as the SYSADM schema owner. The syntax of your MV create statement will create the MV in your dba schema. Once you corrected the error with the rowid, Oracle tried to go ahead and actually create the MV. But to create an MV in a different schema requires a whole grab bag of privileges that need to be granted directly to the id, not granted through your dba role. See
Oracle SQL reference said:
The privileges required to create a materialized view should be granted directly rather than through a role.

To create a materialized view in your own schema:

You must have been granted the CREATE MATERIALIZED VIEW system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.
You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a materialized view in another user's schema:

You must have the CREATE ANY MATERIALIZED VIEW system privilege.
The owner of the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own (for example, if the master tables are on a remote database), and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.
To create a refresh-on-commit materialized view (ON COMMIT REFRESH clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges:

The owner of the master tables must have the QUERY REWRITE system privilege.
If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege or the QUERY REWRITE object privilege on each table outside your schema.
If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside the schema.
If you are defining the materialized view on a prebuilt container (ON PREBUILT TABLE), you must have the SELECT privilege WITH GRANT OPTION on the container table.
The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the materialized view's master table and index or must have the UNLIMITED TABLESPACE system privilege.
 
Great points, karluk! Yes, I was thinking that rowids were required precisely because PeopleSoft databases avoid declarative referential integrity like the plague. Even though it is now an Oracle product.

I will follow steps you give and should get past this error.

PeopleSoft also has no concept of materialized views. But this does not mean that they are prohibited. Same with partitioning. In general, Oracle RDBMS fancy features may be used on PSFT instances, provided these are adequately tested and documented.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top