SQL TUNING - Table Aliases

SQL TUNING - Table Aliases - 
The dynamic SQL column reference (*) gives you a way to refer to all of the columns of a table. For example, if you specify:

SELECT * FROM emp
Oracle references each column in the EMP table in turn. This is a helpful feature because it keeps you from having to identify every individual field. The SQL parser handles all the field references by obtaining the names of valid columns from the data dictionary and substituting them on the command line.

Usually, we recommend that you do not use the * feature because it is a very inefficient one. However, you can use this feature very effectively in some circumstances such as table auditing. Prefix the * operator with a table alias, and use it with other columns from other tables. For example, you can specify:

SELECT 'A', 'B', E.* FROM emp E
You cannot specify

SELECT 'A', 'B', *   FROM emp
You can easily develop a mirror image audit trail of a table, for example:

INSERT INTO emp_audit
SELECT USER, SYSDATE, A.*
FROM   emp A
WHERE  emp_no = :emp_no;
If two tables are identical (e.g., a source table and a mirrored audit table), you can copy data easily from one to the other this way. There is a big advantage to using the * operator. If you keep the audit table up to date (i.e., identical in field number, type, and sequence to the source table), you will never need to maintain the audit routine(s). If you add a column to the EMP table and the EMP_AUDIT table, the routines that access these tables will continue to work; you won't have to modify all of the routines in the EMP audit procedure that deal with updates.

This simple idea can be extended to simplify many programming situations. Another example is shown in the following code:

   SELECT E.emp_no, E.emp_name, D.*, C.*
   FROM   CAT  C,
          DPT  D,
          EMP  E
   WHERE  E.dept_no  = D.dept_no  (+)
   AND    E.cat_type = C.cat_type (+);