Skip to Main Content
  • Questions
  • use of table aliases and reduction in recursive sql queries

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, rd.

Asked: August 09, 2001 - 3:41 pm UTC

Last updated: November 10, 2003 - 7:39 pm UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Tom

1.How far is the below statement true. Can you explain me the fundamentals behind the below statement.

"
Table aliases should be used in all queries
that have more than one table in the FROM clause.
The use of table aliases speeds up the parse phase
of an oracle query, by reducing the number of
recursive sql queries.
"

Infact can you give me an example and explain how use of table aliases help in query performance.

2.
From what I grasped, while choosing the execution plan for a query oracle optimiser works looks at each of the WHERE clauses and assigns the tables concerned a number based on the type of predicate e.g. field = 'const' or field(+) = field. It then chooses the
table with the lowest score as the driving table.

Tom suppose Iam joining 10 tables for my query, how can I determine, how many rows are returned from each table.

can you give an example and explain how should we arrange our where clause so that the query gets executed with the least cose. What Iam asking is can you show me a query involving more than 3 to 4 tables where by changing the order of the predicates , there is improvement in the query performance.

3.Tom after going through all your answers on in and exists, I have come to the following conclusion

if the subquery returns few rows, but, the main query returns a
lot of rows for each row from the subquery use the IN operator.

But what in cases where an in clause includes constants like
where where name in ( 'sdfs', 'sfsd', '234sdff'). Doesnt it result in 3 full table scans.

what if I change it like
where name='sdfs' and
where name='sfsd' and
where name='234sdff'

4.

I read this stuff from internet, can you explain more about
bind variable and host variable and what is the relationship between them. If possible use an example

"
The binding phase takes place after the parse
phase. It is at this stage that the user variables
are decided. For instance in the query select 'x'
from person where name = :1 the host variable 1
would have its value 'bound' into the query during
binding by having the address of :1 associated
with the host variable.
"

Thank you very much


and Tom said...

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>
....

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

look at your statement

Bibi Za, August 09, 2001 - 9:44 pm UTC

--
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.
--

what is the difference between looking up which table could contribute enamem, and which could contribute dname, AND
CHECKING WHETHER EMP HAS AN ENAME AND DEPT HAS A DNAME.

in both the cases is oracle not doing the same amount of work, isnt cpu cost both for the same


recursive sql

A reader, August 09, 2001 - 9:47 pm UTC

What factors will lead to a lot of recursive sql.

Is a lot of recursive sql good or bad.

How can we reduce recursive sql.

Responses

Connor, August 10, 2001 - 5:24 am UTC

In response to post 1:
The parser must check BOTH tables for each of the column names if they are not aliased - thats why there is more effort.
In response to post 2:
Probably the biggest (and worst) thing which causes lots of recursive sql is not use bind variables.
Response for Tom:
Of course you can override the CBO using the ORDERED_PREDICATES hint

Good example showing actions of CBO

Harrison, August 11, 2001 - 10:55 pm UTC

Hi Tom:

I liked the example showing the effect of the CBO; I am
always surprised at how easy you find it to demonstrate
something that I imagine it would take all to day to
prove (and would take me all day, anyway). If I see
enough of this, I may learn something. Thanks

Please Explain this...

Reader, May 21, 2003 - 1:06 am UTC

Recently I read that if we use smaller aliases for table
like instead of emp.ename -> e.ename etc.
the execution speed will be improved.
is this true ?


Tom Kyte
May 21, 2003 - 8:15 am UTC

did they supply you the scientific proof to back this up?  if not, I would stop reading that persons material.  every statement like that should be "proven".

is there a teeny nugget of almost truth in there?  sure, it takes neglibly less cpu power to parse such a statement.

does it matter in real life?  not a bit.  

when would it matter the most?  if you hard parse every query in your system.  But -- then you have a much much much larger problem then worrying about a nanasecond of cpu spent parsing a bigger string.  You have a large bug in your system in that you are not using bind variables.

when would it matter the second most?  when you soft parse a query before each execute.  But -- then you have a much much much larger problem then worrying about a nanasecond of cpu spent parsing a bigger string.  You have a large bug in your system in that your developers don't know how to write good code.

When you write code properly, it matters not a twit.


Consider case 1, you hard parse lots:


ops$tkyte@ORA920> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA920> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
  2      type rc is ref cursor;
  3      l_cursor rc;
  4  begin
  5      for i in 1 .. 10000
  6      loop
  7          open l_cursor
  8          for
  9          'select e.empno from emp e where e.empno = ' || i;
 10          close l_cursor;
 11      end loop;
 12  end;
 13  /
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
  2      type rc is ref cursor;
  3      l_cursor rc;
  4  begin
  5      for i in 1 .. 10000
  6      loop
  7          open l_cursor
  8          for
  9          'select e12345678901234567890123456789.empno
 10             from emp e12345678901234567890123456789
 11            where e12345678901234567890123456789.empno = ' || i;
 12          close l_cursor;
 13      end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 1467 hsecs
Run2 ran in 1622 hsecs
run 1 ran in 90.44% of the time

Name                                Run1      Run2      Diff
LATCH.library cache              200,131   190,895    -9,236
LATCH.child cursor hash table     79,999    70,191    -9,808
LATCH.library cache pin           90,062    70,418   -19,644
LATCH.library cache pin alloca    80,036    60,242   -19,794
STAT...session uga memory              0    65,464    65,464
LATCH.shared pool                155,169   230,454    75,285
STAT...session pga memory              0   131,072   131,072

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
690,196   707,917    17,721  97.50%

PL/SQL procedure successfully completed.

<b>neither piece of code is acceptable on my system, so the fact one is marginally faster is not relevant.  they would both be rejected out of hand.

Next, we softparse:</b>


ops$tkyte@ORA920> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      type rc is ref cursor;
  3      l_cursor rc;
  4  begin
  5      for i in 1 .. 10000
  6      loop
  7          open l_cursor
  8          for
  9          'select e.empno from emp e where e.empno = :n' using i;
 10          close l_cursor;
 11      end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
  2      type rc is ref cursor;
  3      l_cursor rc;
  4  begin
  5      for i in 1 .. 10000
  6      loop
  7          open l_cursor
  8          for
  9          'select e12345678901234567890123456789.empno
 10             from emp e12345678901234567890123456789
 11            where e12345678901234567890123456789.empno = :n'
 12            using i;
 13          close l_cursor;
 14      end loop;
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 351 hsecs
Run2 ran in 334 hsecs
run 1 ran in 105.09% of the time

Name                                Run1      Run2      Diff

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
233,986   233,752      -234 100.10%

PL/SQL procedure successfully completed.

<b>due to the variances in "load" on the system, this time, the first query is actually slower parsing.  Note how the latching went way down, that is good but this code is still "buggy" to me.  There is a soft parse for each execute -- nasty.  Lets fix that</b>


ops$tkyte@ORA920> exec runStats_pkg.rs_start;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      cursor c(p_number in number)
  3      is
  4      select e.empno
  5        from emp e
  6       where e.empno = p_number;
  7  begin
  8      for i in 1 .. 10000
  9      loop
 10          open c(i);
 11          close c;
 12      end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_middle;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
  2      cursor c(p_number in number)
  3      is
  4      select e12345678901234567890123456789.empno
  5        from emp e12345678901234567890123456789
  6       where e12345678901234567890123456789.empno = p_number;
  7  begin
  8      for i in 1 .. 10000
  9      loop
 10          open c(i);
 11          close c;
 12      end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec runStats_pkg.rs_stop(1000);
Run1 ran in 61 hsecs
Run2 ran in 62 hsecs
run 1 ran in 98.39% of the time

Name                                Run1      Run2      Diff

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
53,368    53,473       105  99.80%

PL/SQL procedure successfully completed.


<b>there you go, now there is in fact no measurable difference between them -- but look at the times here, 61 hsecs vs 1500 hsecs.  NOW you see where the difference is, where the measurable, massive performance opportunities are... They are not in the size of your variable names -- they are in the way you write your code!</b>

 

use of table aliases and reduction in recursive sql queries

Jean Antoine, November 10, 2003 - 7:39 pm UTC

You have a way to write responses most of us can read. You are patient, informative and accurate. Thanks for sharing the wealth!

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.