ORA-01427 ERROR while loading IMV

[forgetarun]

Using EDS 5.3.1, Teiid 7.7.5: 

Created an Oracle view on  imported it source model. Created a virtual view using 'select * from oracle-view'. Enabled IMV for this view.

On testing the VDB via Designer, using a query like 'select * from oracle-view where filed1=value1', Teiid tries to load the IMV with data.

The data load fails due to "ORA-01427: single-row subquery returns more than one row tips". But this same query works with Result cache but fails with this error when IMV is enabled.

In another scenario, data load from a oracle-view fails due to an error that reads "Batch is removed"( when the data to be loaded is high). For a smaller data set, the same works. 

Is there any config settings that needs to be changed? 

[shawkins]

> The data load fails due to "ORA-01427: single-row subquery returns more than one row tips". But this same query works with Result cache but fails with this error when IMV is enabled.

You'll need to produce query plans both ways.  In theory the only difference for the materialized view load will be that the user criteria here won't be used initially as all data is needed to populate the materialization table.

> Is there any config settings that needs to be changed?

No, that is never expected.  You may be hitting a known issue that is addressed by a later patch level.  You'll want to contact GSS with your stacktrace.