Alias Vs Derived Tables....

building semantic layer
1

Hi All,
I have a requirement in which i either use the

  1. Alias of the Table 1 and use the Self Join on the Alias and Create my Object or
  2. Create a Derived table off of that Table 1 and use the Object from the Derived Table.
    Which object do you think will have a better performance and why. Can you please throw some light on this one.

Thanks,
Kon

konquistador :us: (BOB member since 2006-01-04)

2

For simplicity I would go with the alias. The Alias is used when you need the same table for 2 differnt “meanings” or calculations.

Derived tables are very usefull if you try to create on the fly calcuations or other “complex” functions not easily available in SQL.

Perfromance wise it will really depend on how your database is tuned and configured. In most cases I would assume that the nested query from the derived table would be slower than a simple alias.
All this will depend on the indexes and database engine settings.

Clearly an Alias will be a lot easier to maintain in 24month, oppsoed to analyse the SQL and try to figure out what it is doing.

ClaireB :de: (BOB member since 2002-08-09)

3

I agree totally with Klaus.

It should be remebered that derived table is really just a form of view, therefore no indexes and full ‘table’ SQL generated at run time, even if you wish to only use one or two items from the derived table.

Mak 1 :uk: (BOB member since 2005-01-06)

4

Too generic of a statement for my taste.
A view as well as Inline Views (a.k.a. Derived Tables in Business Objects) can utilize DB indexes of the underlying tables.
This is why derived tables do perform real well when when using the @PROMPT function in the SQL WHERE clause definition of the derived table, assuming the column on which the @PROMPT is operating has been indexed properly.

Andreas :de: (BOB member since 2002-06-20)

5

Fair enough, although I thought in Oracle - at least - that a materialised view was the only type that could be indexed.

In this case, however, I would go with the alias option myself as it would be easier to understand and maintain.

Mak 1 :uk: (BOB member since 2005-01-06)

6

Thats is true to the best of my knowledge, but even though one cannot create an index directly on a view (Oracle) indexes of the underlying tables can be utilized by the DBMS when executing a query against the view.

In other words, create the proper database indexes on the tables, then build your view on top if it.

Andreas :de: (BOB member since 2002-06-20)