Tables instead of Views

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

Did you folks ever think of using snap shots. Creating a snapshot you can refresh them at your convenience. My suggestion would be to go for that instead of creating a joint tables.

Sam

Hello,

We would like to indexed joint tables. Using snapshot, we will not have indexes in the joint tables (we would like to use the original indexes in the joint tables...).

Thanks

You cannot insert in a view with join.
If you create a table from the view you can't use the indexes of the tables behind the view for your new table.
If you insert data in the new table the next refresh it will be gone.
Instead try to enhance the performance for your queries, it is not all about indexes.

how about using Materialized Views

Hello,

We are using 7.3.3. Materialized Views don't exist I think...


Thanks...