Derived tables

[former_member672670]

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.

[Former Member · Accepted solution]

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.

[Former Member]

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) ..

[Former Member]

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..