RE: Query tuning with tablename alias

[Khedr, Waleed]

Title: Message

I do not think Oracle will run the sql below. Once you have an alias for a table, only that alias could be used as an alias (not the table name).

 

Example:

 

Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production

 

SQL> desc dual;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 DUMMY                                              VARCHAR2(1)

 

SQL> select a.dummy from dual a;

 

D
-
X

 

SQL> select dual.dummy from dual a;
select dual.dummy from dual a
       *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier

 

SQL> select dual.dummy from dual;

 

D
-
X

 

 

 

Waleed

-----Original Message-----
From: Hopper, Wendy S [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 20, 2002 1:13 PM
To: Multiple recipients of list ORACLE-L
Subject: Query tuning with tablename alias

Hello list.

 

I have recently been tasked with trying to optimize some slow performing queries (Oracle 8.1.7) for an application that generates reports in a data warehouse type environment.  I have noticed in most of the queries that the table names have been aliased, but not used elsewhere in the query.  

 

IE.   SELECT  table1.column1,

                      table1.column2,

                      table2.column1

       FROM     table1 A,

                      table2 B

       WHERE   table1.key = table2.key;

 

Are there any advantage/disadvantages to giving these tables aliases and not using them anywhere else?   I am thinking that if the tables have been assigned alias names, they should be referred to by alias names, but I guess I have not seen anything documented on this officially.   These queries join tables against remote tables in a different instance, if that makes a difference.  Any insight on this would be great.

               

Thanks.

Wendy Hopper