?
Solved

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

Posted on 2008-11-05
6
Medium Priority
?
1,147 Views
Last Modified: 2013-12-19
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
Comment
Question by:Paradkar
  • 4
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 750 total points
ID: 22888661
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
 

Author Comment

by:Paradkar
ID: 22888880
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 22888961
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Paradkar
ID: 22889287
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 22889410
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 22889799
Essentially you need to create DBMS_JOB and refresh MV based on business needs.

Hope it helps
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question