Hi All,
I need some clarifications and suggestions. Any help on this is appreciated.
We are on 8.1.7.4 , windows 2000 server.

We have a business process where in seed data( data which is static and is same for all new customers) is inserted with the new customer's ent_id. Seed data is identified with ent_id=0. The data is spread across 40 tables (the data to be picked up includes blobs and clobs). When a new coustomer is added, then a stored procedure is run, which does:

1. Accesses table#1, finds matching data with ent_id=0, and then inserts the same data with "new" ent_id of customer.

2. The above process is repeated for all the other 39 tables.

3.In step 1 above, out of 40 tables about 25 are matched with a simple where predicate 'ent_id=0'. However for other 15 tables the matching criteria to select the seed data, is more complex having joins(including outer joins).

The entire thing runs slowly due to the nature of how we are trying to do the work.
I am trying to see if views/materialized views help in this situation. The idea is, since the seed data remains same for any new customer(very little update), capture this data from 40 tables and store it in 3 or 4 materialized views (if 1 becomes too complex). However, in the implimentation tests, I got stuck with cartesian products like below.
For tests, I just tried to create a MV like below:

CREATE MATERIALIZED VIEW STORE_SEEDDATA1
PCTFREE 0 TABLESPACE data_local
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
a.gm_id,
a.gm_domestic_cd,
a.gm_desc,
b.ctc_id,
b.ctc_domestic_cd,
b.ctc_desc,
c.cc_id as cc_id_1,
c.ci_country_cd,
c.ci_country_desc,
c.ci_currency_desc,
c.sysdef as sysdef_1
FROM
INT_GROUP_MASTER a
,INT_CORP_TYPE b
,INT_COUNTRY_INFO c
WHERE
(a.ENTERPRISEKEY = 0) and
(b.ENTERPRISEKEY = c.ENTERPRISEKEY) and
(a.ENTERPRISEKEY= b.ENTERPRISEKEY);

The problem is above is returning cartesian product. (9, 13, and 269 records in a, b and c tables matching enterprisekey=0, and the MV has 31473 records). I need 269 records only, because of my requirement. For example, when a new customer with enterprisekey 101 comes, I will do something like "insert into INT_GROUP_MASTER(enterprisekey,(select gm_id,gm_domestic_cd,gm_desc from STORE_SEEDDATA1)".
Can anyone tell me whether my approach is correct to attend this problem, or am I trying something which does not make sense.

Thanks
Manjunath