Hello,

Many views were created in order to store some complicated requests. (jointure of many tables...). 3 hours is needed in some case to have data from a view. Instead of using views, the responsible of the application decided to create tables (using the same view definition) and insert data into tables (this operation will be done the night).

So if you have the following Select order in a given view V1 :

SELECT D21V11EJUDEF.C04,D21V11EJUDEF.C01,D21V11EJUDEF.C06,to_char(D21V11EJUDE
07,'YYYY'),D21V11EJUDEF.CB,D21V11EJUDEF.C03,D21V11EJUDEF.C02,D21V11EJU
1V11EJUOBJ.C01,D21V11EJUTIE.C01,D21V11EJUTIE.C02,rtrim(D21V11EJUDEF.C05)
1V11EJUDEF, D21V11EJUOBJ, D21V11EJUTIE WHERE
and D21V11EJUTIE.CB(+)=D21V11EJUDEF.CB

(alias are defined for the view : suppose that we have : A1, A2, A3....).

We will create a table :

Create table T1 as
SELECT * from V1

We need to create indexes in the new table T1 using indexes in the tables D21V11EJUDEF, D21V11EJUOBJ, D21V11EJUTIE. So if D21V11EJUDEF.C04 is indexed, we must index T1.A1... (in other word, we will index column A1 of T1).

The table creation using views are easy to automate. The problem is to create indexes in the big new tables.... (and do the matching between (New table column name = alias name in the view) & indexed columns in the original tables... : matching between D21V11EJUDEF.C04 and T1.A1....).

Your suggestions are welcome ....

Thanks a lot

Sofiane