Why I can enable ON QUERY COMPUTATION on this Materialized view ??

User_45P0B

Hi guys

I have a set of tables with Materialized view logs enabled ( with their corresponding sequence, rowid and primary key ) and I can create this materialized view for FAST Refresh and Enable query rewrite, which works fine. When I try to enable on query computation, I've got an error which I don't understand.

SQL> create materialized view alfaods.mv_ext_dealer_01

TABLESPACE TBDATA

CACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT

count(*) as contador,

schedule.agreementId as SchAgrId,

schedule.maturityDate,

schedule.id AS schedule_id ,

schedule.alfascheduleidentifier AS scheduleId,

asset.assetidentifier AS assetId,

asset.modelName as assetModelName ,

agreement.id as agreementid,

agreement.agreementNumber as loanId,

schedule.scheduleStatus AS scheduleStatus,

schedule.terminationDate,

supplier.thirdPartyNumber as SupplierThirdNumber ,

schedule.totalAssetCostFinanced as loanAmount,

schedule.capitalOutstanding as capitalOutstanding,

schedule.activationDate as activationDate,

365 as "KBSTATE"

FROM ALFAODS.OdsScheduleMain schedule

INNER JOIN ALFAODS.OdsAgreement agreement on schedule.agreementId = agreement.id

INNER JOIN ALFAODS.OdsAsset asset on asset.scheduleId = schedule.id

INNER JOIN ALFAODS.OdsThirdParty thirdPartyDealer on schedule.invCusId = thirdPartyDealer.id

INNER JOIN ALFAODS.OdsThirdParty submitter on schedule.dealerid=submitter.id

LEFT JOIN ALFAODS.OdsThirdParty supplier on supplier.id = asset.supplierId

LEFT JOIN ALFAODS.OdsCompany odsInvoicingCompany on odsInvoicingCompany.id = schedule.invCompanyId

LEFT JOIN ALFAODS.OdsCompany agrCompany on agrCompany.id = schedule.agrCompanyId

group by

schedule.agreementId ,

schedule.maturityDate ,

schedule.id ,

schedule.alfascheduleidentifier ,

asset.assetidentifier ,

asset.modelName ,

agreement.id ,

agreement.agreementNumber ,

schedule.scheduleStatus ,

schedule.terminationDate ,

supplier.thirdPartyNumber ,

schedule.totalAssetCostFinanced ,

schedule.capitalOutstanding ,

schedule.activationDate;

Materialized view created.

However, if I try to enable ON QUERY COMPUTATION to make it real time, I got an error. Same command with ENABLE ON QUERY COMPUTATION

SQL> create materialized view alfaods.mv_ext_dealer_01

TABLESPACE TBDATA

CACHE

LOGGING

NOCOMPRESS

NOPARALLEL

BUILD IMMEDIATE

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

ENABLE ON QUERY COMPUTATION

AS

SELECT

count(*) as contador,

schedule.agreementId as SchAgrId,

schedule.maturityDate,

schedule.id AS schedule_id ,

schedule.alfascheduleidentifier AS scheduleId,

asset.assetidentifier AS assetId,

asset.modelName as assetModelName ,

agreement.id as agreementid,

agreement.agreementNumber as loanId,

schedule.scheduleStatus AS scheduleStatus,

schedule.terminationDate,

supplier.thirdPartyNumber as SupplierThirdNumber ,

schedule.totalAssetCostFinanced as loanAmount,

schedule.capitalOutstanding as capitalOutstanding,

schedule.activationDate as activationDate,

365 as "KBSTATE"

FROM ALFAODS.OdsScheduleMain schedule

INNER JOIN ALFAODS.OdsAgreement agreement on schedule.agreementId = agreement.id

INNER JOIN ALFAODS.OdsAsset asset on asset.scheduleId = schedule.id

INNER JOIN ALFAODS.OdsThirdParty thirdPartyDealer on schedule.invCusId = thirdPartyDealer.id

INNER JOIN ALFAODS.OdsThirdParty submitter on schedule.dealerid=submitter.id

LEFT JOIN ALFAODS.OdsThirdParty supplier on supplier.id = asset.supplierId

LEFT JOIN ALFAODS.OdsCompany odsInvoicingCompany on odsInvoicingCompany.id = schedule.invCompanyId

LEFT JOIN ALFAODS.OdsCompany agrCompany on agrCompany.id = schedule.agrCompanyId

group by

schedule.agreementId ,

schedule.maturityDate ,

schedule.id ,

schedule.alfascheduleidentifier ,

asset.assetidentifier ,

asset.modelName ,

agreement.id ,

agreement.agreementNumber ,

schedule.scheduleStatus ,

schedule.terminationDate ,

supplier.thirdPartyNumber ,

schedule.totalAssetCostFinanced ,

schedule.capitalOutstanding ,

schedule.activationDate ;

INNER JOIN ALFAODS.OdsThirdParty submitter on schedule.dealerid=submitter.id

*

ERROR at line 33:

ORA-32361: cannot ENABLE ON QUERY COMPUTATION for the materialized view