1) there is some truth to it. What they are saying (incompletely) is that you should use qualified identifiers. Its not the table alias that as is important as qualifying the identifiers in the query itself.
It could be easier to parse:
select emp.ename, dept.dname
from emp, dept
where emp.deptno = dept.deptno
then
select ename, dname
from emp, dept
where emp.deptno = dept.deptno
even though both work and return the same results. In the first query , the parser doesn't have to look up which table could contribute ENAME and which could contribute DNAME - it only has to check that emp has an ename and dept has a dname.
I fail to see how using a table alias could reduce the amount of rescursive sql however. It is easy to see how using table aliases (or the name itself) on all identifiers makes it easier on the parser (shaves a tiny bit of CPU off the runtime). It also protects you from changes in the schema -- if someone adds a DNAME or ENAME column to EMP/DEPT -- the first query will contain to function, the second will fail.
2) Well, in Oracle8i I would strongly -- very strongly -- encourage you to use the cost based optimizer (CBO). There are many features (partitioned tables, bitmapped indexes, parallel query, function based indexes, parallel query, etc) that work only with the CBO.
If you use the CBO, the order of the predicate doesn't count, doesn't matter, isn't taken into consideration.
Using the RBO, the driving table is actually taken primarily from the order of the tables in the FROM clause. Consider:
ops$tkyte@ORA817.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA817.US.ORACLE.COM> create table dept as select * from scott.dept;
Table created.
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> create index emp_idx on emp(deptno);
Index created.
ops$tkyte@ORA817.US.ORACLE.COM> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from emp, dept
2 where dept.deptno = emp.deptno
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'DEPT'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
4 3 INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)
ops$tkyte@ORA817.US.ORACLE.COM> select * from dept, emp
2 where dept.deptno = emp.deptno
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'EMP'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4 3 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace off
see, just flip the order of tables and the optimizer will change the plan. Running the above two queries again using the CBO:
ops$tkyte@ORA817.US.ORACLE.COM> analyze table emp compute statistics;
Table analyzed.
ops$tkyte@ORA817.US.ORACLE.COM> analyze table dept compute statistics;
Table analyzed.
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> set autotrace traceonly explain
ops$tkyte@ORA817.US.ORACLE.COM>
ops$tkyte@ORA817.US.ORACLE.COM> select * from emp, dept
2 where dept.deptno = emp.deptno
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=700)
1 0 NESTED LOOPS (Cost=5 Card=14 Bytes=700)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=72)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=14 Bytes=448)
4 3 INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)
ops$tkyte@ORA817.US.ORACLE.COM> select * from dept, emp
2 where dept.deptno = emp.deptno
3 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=14 Bytes=700)
1 0 NESTED LOOPS (Cost=5 Card=14 Bytes=700)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=72)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=14 Bytes=448)
4 3 INDEX (RANGE SCAN) OF 'EMP_IDX' (NON-UNIQUE)
the order of the tables/predicate -- it just doesn't matter.
3) an "x IN (a,b,c)" does not result in 3 full scans. It might result in 1 full scan if x is not indexed but if x is indexed -- it'll do three INDEX range scans and concatenate the results together.
the change you propose would ALWAYS return zero rows. I assume you mean OR -- in which case it would be processed just like the IN (a,b,c)
4) see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1063759517155 <code>
....