[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Materialized creation hangs

Posted on 2008-01-29
7
Medium Priority
?
1,922 Views
Last Modified: 2011-09-20
I have several views that work just fine.. I have been changing them to materialized views.. most of them work just fine as materialized views.. but I have a few that wont compile..
Below is the code from one that works as a view, but when I try to compile it as a materialized view it just hangs.. it never finishes and never errors.. why is this?

----------------------------
create materialized view ADHOC_SOFTWARE_FAST
refresh force on demand
start with to_date('15-01-2008 12:56:46', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
enable query rewrite
as
SELECT distinct "IS_INVENTORY"."ASSET_TAG",
 "IS_INVENTORY"."INV_DESCRIPTION" "MODEL_NO",
 "IS_INVENTORY"."MFGR_NO" "SERIAL_NO",
 "IS_INVENTORY"."ALTERNATE_TAG" "SOFTWARE_TRAILER",
 "VALUATION"."DATE_ACQUIRED",
 "VALUATION"."ACQUISITION_VALUE",
 "CONTRACT_PURCHASE"."CONTRACT_NUMBER" "PURCHASE_CONTRACT",
 "CONTRACT_MAINTENANCE"."CONTRACT_NUMBER" "MAINTENANCE_CONTRACT",
--
 "PM_ATTRIB_VALUE_BIN_NUMBER"."PHYSVALUE" "BIN_NUMBER",
--
--
 "PM_ATTRIB_VALUE_COMMENTS"."PHYSVALUE" "COMMENTS",
--
--
 "PM_ATTRIB_VALUE_CONNECTION"."PHYSVALUE" "CONNECTION",
--
--
 "PM_ATTRIB_VALUE_DESCRIPTION"."PHYSVALUE" "DESCRIPTION",
--
--
 "PM_ATTRIB_VALUE_INSTAL_DATE"."PHYSVALUE" "INSTALLATION_DATE",
--
--
 "PM_ATTRIB_VALUE_INSTALLED_BY"."PHYSVALUE" "INSTALLED_BY",
--
--
 "PM_ATTRIB_VALUE_MANUFACTURER"."PHYSVALUE" "MANUFACTURER",
--
--
 "PM_ATTRIB_VALUE_NO_OF_LICENSES"."PHYSVALUE" "NO_OF_LICENSES",
--
--
 "PM_ATTRIB_VALUE_SYSTEM"."PHYSVALUE" "SYSTEM",
--
--
 "PM_ATTRIB_VALUE_TYPE"."PHYSVALUE" "TYPE",
--
--
 "PM_ATTRIB_VALUE_VERSION"."PHYSVALUE" "VERSION",
--
--
 "PM_ATTRIB_VALUE_VENDOR"."PHYSVALUE" "VENDOR"
--

 FROM
"EXAV"."IS_INVENTORY" "IS_INVENTORY"

LEFT OUTER JOIN "EXAV"."VALUATION" "VALUATION"
ON "IS_INVENTORY"."IS_INVENTORY_ID"="VALUATION"."IS_INVENTORY_ID"

LEFT OUTER JOIN "EXAV"."INV_CONTRACT" "INV_CONTRACT_PURCHASE"
ON ("IS_INVENTORY"."IS_INVENTORY_ID"="INV_CONTRACT_PURCHASE"."IS_INVENTORY_ID")
LEFT OUTER JOIN "EXAV"."CONTRACT" "CONTRACT_PURCHASE"
ON "INV_CONTRACT_PURCHASE"."CONTRACT_ID"="CONTRACT_PURCHASE"."CONTRACT_ID"

LEFT OUTER JOIN "EXAV"."INV_CONTRACT" "INV_CONTRACT_MAINTENANCE"
ON ("IS_INVENTORY"."IS_INVENTORY_ID"="INV_CONTRACT_MAINTENANCE"."IS_INVENTORY_ID")
LEFT OUTER JOIN "EXAV"."CONTRACT" "CONTRACT_MAINTENANCE"
ON "INV_CONTRACT_MAINTENANCE"."CONTRACT_ID"="CONTRACT_MAINTENANCE"."CONTRACT_ID"

----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_BIN_NUMBER"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_BIN_NUMBER"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_BIN_NUMBER"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_BIN_NUMBER"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_BIN_NUMBER"."ATTRIB_ID"="PM_ATTRIB_VALUE_BIN_NUMBER"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_COMMENTS"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_COMMENTS"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_COMMENTS"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_COMMENTS"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_COMMENTS"."ATTRIB_ID"="PM_ATTRIB_VALUE_COMMENTS"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_CONNECTION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_CONNECTION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_CONNECTION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_CONNECTION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_CONNECTION"."ATTRIB_ID"="PM_ATTRIB_VALUE_CONNECTION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_DESCRIPTION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_DESCRIPTION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_DESCRIPTION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_DESCRIPTION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_DESCRIPTION"."ATTRIB_ID"="PM_ATTRIB_VALUE_DESCRIPTION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_INSTAL_DATE"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_INSTAL_DATE"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_INSTAL_DATE"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_INSTAL_DATE"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_INSTAL_DATE"."ATTRIB_ID"="PM_ATTRIB_VALUE_INSTAL_DATE"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_INSTALLED_BY"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_INSTALLED_BY"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_INSTALLED_BY"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_INSTALLED_BY"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_INSTALLED_BY"."ATTRIB_ID"="PM_ATTRIB_VALUE_INSTALLED_BY"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_MANUFACTURER"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_MANUFACTURER"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_MANUFACTURER"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_MANUFACTURER"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_MANUFACTURER"."ATTRIB_ID"="PM_ATTRIB_VALUE_MANUFACTURER"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_NO_OF_LICENSES"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_NO_OF_LICENSES"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_NO_OF_LICENSES"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_NO_OF_LICENSES"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_NO_OF_LICENSES"."ATTRIB_ID"="PM_ATTRIB_VALUE_NO_OF_LICENSES"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_SYSTEM"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_SYSTEM"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_SYSTEM"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_SYSTEM"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_SYSTEM"."ATTRIB_ID"="PM_ATTRIB_VALUE_SYSTEM"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_TYPE"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_TYPE"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_TYPE"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_TYPE"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_TYPE"."ATTRIB_ID"="PM_ATTRIB_VALUE_TYPE"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_VERSION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_VERSION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_VERSION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_VERSION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_VERSION"."ATTRIB_ID"="PM_ATTRIB_VALUE_VERSION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_VENDOR"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_VENDOR"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_VENDOR"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_VENDOR"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_VENDOR"."ATTRIB_ID"="PM_ATTRIB_VALUE_VENDOR"."ATTRIB_ID")
----


 WHERE (("CONTRACT_PURCHASE"."CONTRACT_TYPE" = 'PURCHASE'
         OR "CONTRACT_PURCHASE"."CONTRACT_TYPE" is null)
 OR ("CONTRACT_MAINTENANCE"."CONTRACT_TYPE" = 'MAINTENANCE'
 OR "CONTRACT_MAINTENANCE"."CONTRACT_TYPE" is null))

------
 AND "ATTRIBUTE_DEF_BIN_NUMBER"."ATTRIB_NAME" = 'BIN NUMBER'
------
------
 AND  "ATTRIBUTE_DEF_COMMENTS"."ATTRIB_NAME" = 'COMMENTS'
------
------
 AND  "ATTRIBUTE_DEF_CONNECTION"."ATTRIB_NAME" = 'CONNECTION'
------
------
 AND  "ATTRIBUTE_DEF_DESCRIPTION"."ATTRIB_NAME" = 'DESCRIPTION'
------
------
 AND  "ATTRIBUTE_DEF_INSTAL_DATE"."ATTRIB_NAME" = 'INSTALLATION DATE'
------
------
 AND  "ATTRIBUTE_DEF_INSTALLED_BY"."ATTRIB_NAME" = 'INSTALLED BY'
------
------
 AND  "ATTRIBUTE_DEF_MANUFACTURER"."ATTRIB_NAME" = 'MANUFACTURER'
------
------
 AND  "ATTRIBUTE_DEF_NO_OF_LICENSES"."ATTRIB_NAME" = 'NUMBER OF LICENSES'
------
------
 AND  "ATTRIBUTE_DEF_SYSTEM"."ATTRIB_NAME" = 'SYSTEM'
------
------
 AND  "ATTRIBUTE_DEF_TYPE"."ATTRIB_NAME" = 'TYPE'
------
------
 AND  "ATTRIBUTE_DEF_VERSION"."ATTRIB_NAME" = 'VERSION'
------
------
 AND  "ATTRIBUTE_DEF_VENDOR"."ATTRIB_NAME" = 'VENDOR'
------
 AND "IS_INVENTORY"."ASSET_TAG" LIKE '%SW%'
 AND "IS_INVENTORY"."INV_DELETED" <> '1';
0
Comment
Question by:ODOTServer
  • 3
  • 3
7 Comments
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 240 total points
ID: 20775952
Hi, this is a long shot but try reformatting the view to cut down on the number of characters.  You could:

remove all double quotes - as far as I can see these aren't necessary
remove the blank lines between conditions
remove the comment lines

I realise these things are included for readability, but try taking a copy of your original and running it with the changes mentioned.
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 240 total points
ID: 20775993
Is it possible that the objects reference by the materialised view are being used (locked)?
To confirmthe sql for your creation statement isn't causing the problem, try creating your own copies of the tables used by the view and create the view on these copy tables.  You don't really need to put any data in the tables if they are large.

If this works it suggests the issue is due to accessing the tables used by the view.
0
 

Author Comment

by:ODOTServer
ID: 20777554
here is a much shorter example.. I get same results with this one.. it just hangs creating it..
These tables are isolated (not being used by anyone) the database shows no locks.
and it is a smaller character count..
create materialized view VENDOR_CLAIM_DUPLICATES
refresh force on demand
start with to_date('30-01-2008 20:01:00', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1 
as
select distinct
     vendor.c_vendor_no,
     vendor_claim_detail.c_enc_no,
     vendor_claim.a_claim_amount
from
     vendor,
     vendor_claim_detail,
     vendor_claim
where
     vendor.c_vendor_no = vendor_claim_detail.c_vendor_no (+) and
     vendor.c_vendor_no = vendor_claim.c_vendor_no (+) and
     vendor.rowid = (select max(v.rowid) 
                     from 
                            vendor v, 
                            vendor_claim_detail c, 
                            vendor_claim a
                      where
                        v.c_vendor_no = vendor.c_vendor_no and
                        c.c_enc_no = vendor_claim_detail.c_enc_no and
                        a.a_claim_amount = vendor_claim.a_claim_amount
                        group by v.c_vendor_no having count(*)>1)

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 6

Assisted Solution

by:jwittenm
jwittenm earned 80 total points
ID: 20778154
Do you have an MLOG on the base tables (I see you are doing a force refresh)?
Do you have an index on the snaptime$$ columns of the MLOGs?
0
 

Author Comment

by:ODOTServer
ID: 20778282
no i dont
0
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 240 total points
ID: 20778353
Can you provide me with the sql to create the tables and I'll try to create the view myself.  Either an export (preferably without data) or a script would be okay.

What version of Oracle are you using?

How big are the tables (number of rows, number of columns)?
0
 

Accepted Solution

by:
ODOTServer earned 0 total points
ID: 20895599
Found a newer database version to try it in, using a database link to the tables in the old database and it worked there... must be a bug that was fixed..
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

591 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