Tables instead of Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Tables instead of Views

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    64

    Post

    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
    Sofiane

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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

  3. #3
    Join Date
    Sep 2000
    Posts
    64
    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
    Sofiane

  4. #4
    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.
    Ramon Caballero, DBA, rcaballe@yahoo.com

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    how about using Materialized Views

  6. #6
    Join Date
    Sep 2000
    Posts
    64
    Hello,

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


    Thanks...
    Sofiane

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width