asked on

Materialized creation hangs

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';

SOLUTION

Milleniumaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Milleniumaire

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ODOTServer

ASKER

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

SOLUTION