Creating an Alias vs. Duplicating a Physical Table (SQL & OBIEE)

by Kurt Wolff

When you duplicate a table, you create a new physical table with a new name. If this table is involved in a query, the SQL FROM clause will list this table. If the table does not exist in the database, then an error will occur.

Creating an alias creates a copy of the metadata table object that will be referenced in SQL with a new alias name. The alias name in SQL, as it is for all tables, will be derived its metadata ID.

To see the table IDs in OBIEE metadata, use the Oracle BI Query Repository utility. Here are some physical tables (and aliases) in a repository that I’ve created. It’s the last five digits of the ID that will be used to create the table aliases in SQL.

PRODUCT_10 is a table that will referenced in a SQL FROM clause as PRODUCT_10 and will be given the alias T25248.

PRODUCT_119 is another physical table in the database, identical in structure to PRODUCT_10. It might have been created by duplicating the metadata table PRODUCT_10 and then renaming the duplicate as PRODUCT_119. It will be referenced in the SQL FROM clause as PRODUCT_119 with the alias T23388.

The metadata table Product_Retailer is an alias of PRODUCT_10. It has its own metadata ID, 3001:46690, and will be given the alias of T46690.

SQL using both the original table, PRODUCT_10, and its alias, Product_Retailer, will look like this, with the metadata alias name included (optionally, depending on your database features) as a comment:

FROM
PRODUCT_10 T25248,
PRODUCT_10 T46690 /* Product_Retailer */…

If you duplicate a table in metadata, then that new table (with its new name) must map to a table in the physical database that has that name. If it doesn’t exist, the SQL issued will generate an error.

An alias is a reference to a table that already exists, not a separate database object.