Home » Server Options » Replication » Materialized View Geometry Column (Oracle 11g, R2, RHEL 6)
Materialized View Geometry Column [message #600775] Tue, 12 November 2013 06:59 Go to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
Good Day Guys,

I need to create a materialized view based on a table. Later i want to add a geometry column to this Mview. I was wondering is that possible to add the column later on ?

Earliest response is highly appreciated.

Thanks
-AS
Re: Materialized View Geometry Column [message #600776 is a reply to message #600775] Tue, 12 November 2013 07:22 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, you can't simply ALTER MATERIALIZED VIEW ... ADD COLUMN. One possible option is to drop and create the materialized view (MV) (that's what I used to do); that's OK for small MVs (my MVs were small), but large ones are painful because it might take ages to create them. Have a look at Ultra-Fast MV Alteration using Prebuilt Table Option, maybe you'll find it interesting.
Re: Materialized View Geometry Column [message #600833 is a reply to message #600776] Tue, 12 November 2013 22:45 Go to previous messageGo to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
Thanks for your reply,
can i add a column in MView which do not exist in the base table ?

[Updated on: Tue, 12 November 2013 22:45]

Report message to a moderator

Re: Materialized View Geometry Column [message #600837 is a reply to message #600833] Wed, 13 November 2013 00:54 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does that column belong to, then? You could add a "constant" column or create a function (which fetches data from another table), such as
SQL> create or replace function f_dname (par_deptno in dept.deptno%type)
  2    return dept.dname%type
  3  is
  4    retval dept.dname%type;
  5  begin
  6    select d.dname
  7      into retval
  8      from dept d
  9      where d.deptno = par_deptno;
 10
 11    return (retval);
 12  end;
 13  /

Function created.

SQL> create materialized view mv_emp as
  2    select e.empno,
  3           e.ename,
  4           f_dname(e.deptno) dname,
  5           'hello world' hi
  6    from emp e;

Materialized view created.

SQL> select * from mv_emp where rownum < 4;

     EMPNO ENAME      DNAME                HI
---------- ---------- -------------------- -----------
      7369 SMITH      RESEARCH             hello world
      7499 ALLEN      SALES                hello world
      7521 WARD       SALES                hello world

SQL>

Or, you could simply join two (or more) tables:
SQL> create materialized view mv_emp as
  2    select e.empno,
  3           e.ename,
  4           d.dname
  5    from emp e,
  6         dept d
  7    where e.deptno = d.deptno;

Materialized view created.

SQL> select * from mv_emp where rownum < 4;

     EMPNO ENAME      DNAME
---------- ---------- --------------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7934 MILLER     ACCOUNTING

SQL>

Otherwise, I don't see how you could do that (unless you explain what you meant).
Previous Topic: Materialised View identifying modifications 10g
Next Topic: conflict resolution involving TIMESTAMP
Goto Forum:
  


Current Time: Fri Mar 29 00:58:12 CDT 2024