• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1185
  • Last Modified:

Need to create a Materialized View for a table with a long column

I am trying to create a MV for a table with a long column, having around 6.5 million rows.
This is the description of the table:-

SQL> desc ec2.lxdesc_38043c1d;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 LXOID                                              NUMBER
 LXKIND                                             NUMBER
 LXDESC                                             LONG

SQL> select count(*) from ec2.lxdesc_38043c1d;

  COUNT(*)
----------
   6560768

When i create a MV reading the lxdesc, then I get this error.

CREATE MATERIALIZED VIEW EC2LINK.EMARS
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
NEVER REFRESH
AS
SELECT t.lxtype, t.lxname, t.lxmoddate,  decode(t.lxstate, -536841470, 'Released', 'Unknown') State, a.lxdesc, b.LXVAL DesignResponsiblity, c.lxval Mass
  FROM ec2.lxbo_38043c1d t, ec2.lxdesc_38043c1d a, ec2.LXSTRING_38043C1D b, ec2.LXreal_38043c1d c
 WHERE b.lxtype='1595247718' AND c.LXTYPE = '213294734'
   AND t.lxtype='2089847660'


I cannot afford to create another table and replicate all the data into VarChar, since the data is huge..Is there any way in which i can create this MV.
Any inputs will really help

Thanks
0
Paradkar
Asked:
Paradkar
  • 4
  • 2
1 Solution
 
ajexpertCommented:
Hi,
You cannot create MV on a table containing long column
You might have to convert to LOB.  Refer to these links
http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/statements_6002.htm
http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part4.html
0
 
ParadkarAuthor Commented:
Thank you for your response,

Well....I would prefer to have another temp table to have all the Long values read into Varchar. From this table I could create the MV. The issue in this case for me is -
How would I refresh the temp table for only delta objects once it gets changed in the base table..

In this case for example:-
If I create a temp table => tempdescriptiontable with Varchar2 for lxdesc column as stated above for the table lxdesc_38043c1d and copy all the long column data into it,
how do i refresh this"tempdescriptiontable " table for the delta changes that happens on the lxdesc_38043c1d table.
The changes to this lxdesc_38043c1d  table keeps happening and i need to have the snapshot of the data at a particular point...

I cannot add any triggers to the base table lxdesc_38043c1d or make any modifications to this since its a table managed internally by ematrix application..

If it can hold only the delta objects that have been changed then I could still use it..

I am sorry the table names are complex since its a ematrix DB...
Anything that you could suggest?
0
 
ajexpertCommented:
You can create DBMS job to referesh the MV.
It depends on the requirement of how frequently you want to refresh MV.

Hope it helps
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
ParadkarAuthor Commented:
You mean not having the MV, but having a temp table itself which acts like a Materialized View [that is refershed from the DBMS job]...

Could you please give me some pointers to the materials about creating DBMS jobs so that I could try?

Thanks!
0
 
ajexpertCommented:
0
 
ajexpertCommented:
Essentially you need to create DBMS_JOB and refresh MV based on business needs.

Hope it helps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now