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 :
1V11EJUDEF, D21V11EJUOBJ, D21V11EJUTIE WHERE
(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
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.
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...).
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
We are using 7.3.3. Materialized Views don't exist I think...