Skip to Content

Derived tables

Hie Guys,

Just had a quick question. When a table in the database is updated with additional data, is the derived table automatically updated as well? What are some major drawbacks of derived tables when it comes to changes in the database? Also, are those drawbacks resolved by using a view? Thanks.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2014 at 10:08 AM

    Think of derived tables as views stored in a different place. Instead of storing views in the database, you're maintaining them in the universe. The pros and cons of this should be obvious - views are visible to the dba and therefore their performance can be maintained and any new database development will include impact analysis. With derived tables, they are fully under the universe designer's control but the dba has no visibility of them so will not proactively work to tune them or consider them from an impact analysis point of view.

    Now let's consider the two ways of adding more data - adding columns and adding rows.

    If extra rows are added to a table that the derived table is based on, they will be selected the same as the existing rows - they are subject to the same where clauses and having clauses as any other row in the table.

    If extra columns are added to a table that the derived table is abased on, they will have no impact on the derived table. If you want to use them in the derived table, you need to add them as extra columns in your SQL for the derived table.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2014 at 02:59 AM

    When a database is updated with additional data then all the table including derived table will fetches the most recent updated data as queries are executed in the run time..

    When your database table structure changes (unless its just a addition of fields ) you will have to refresh the structure of the whole universe ; does not matter if you have derived table in universe or not..

    Major drawback of derived table is just that it makes the universe not very flexible and can some some time lead to confusion from a user end.. and some time if not designed correctly lead to bad performance..

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2014 at 01:12 PM

    Hi,

    The additional data will fetch automatically, because your writing the sql to fetch the records in the derived tables

    Drawback:

    The derived table query will fire at run time, so it is not possible to perform the tuning(like applying indexes on columns) ..

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.