Hi Tom,

We have following tables in our schema.

VERSIONS(versionbojid number,
itemobjid number,
versiondetail varchar2(100));

VIEW(viewobjid number,
viewdetail varchar2(100));

VERSIONINVIEW(versionobjid number, --references VERSIONS.versionobjid
viewobjid number); --references VIEW.viewobjid

We have an ODBC application that makes use of these tables.

Both VERSIONS and VERSIONINVIEW tables have millions of records. So, any join operation between these two tables is very costly. A simple select with a join between these two tables takes few seconds (2-12 usually) to complete (due to their row count). Hence, we have created the following materialized view to boost our application performance and make our queries faster.

create materialized view log on VERSIONS with rowid;
create materialized view log on VERSIONINVIEW with rowid;

CREATE MATERIALIZED VIEW VERSIONS_VW BUILD IMMEDIATE REFRESH FAST ON COMMIT AS
select v.rowid as v_rowid, viv.rowid as viv_rowid, v.versionobjid, hv.itemobjid, viv.viewobjid from VERSIONS v, VERSIONINVIEW viv where v.versionobjid = viv.versionobjid;

This helped us to run all our SELECT queries very efficiently.
But the problem is....

Whenever we delete a single row from VERSIONS table and commit the transaction through our ODBC application, the commit literally hangs. It does complete, but takes a lot of time. Any idea what is going wrong in this setup?

We have gone through other threads and already tried the following. But, they didn't help. The SELECTs are very fast, but a commit after a DML takes pretty long.
1. setting _mv_refresh_use_stats to FALSE
2. gathering stats on the schema
3. locking stats on MV logs

Note: If we delete the Materialized view, the deletes happen within no time, but of course the SELECT performance is hit.

Please help.
Thanks in advance.

~Mallik.