Please visit www.oracle-class.com for Videos, Free posts, Books, Webinar and Free forum … ALL about Oracle!

Table Alias speed up database access

Today, I have learnt something new then I wanted to share.
According to the Oracle Sql fundamentals written by Nancy Greenberg and reviewed by a few technical contributors in the page 151, the Table aliases speed up database access and the Table aliases can help to keep SQL code smaller by conserving memory.

Reference:
http://www.jamaa.net/files/SQL_Fundamentals_I_VOL_2.pdf

Table aliases speed up database access :

In the following demonstration I will test.

The plan of the Demonstration :
1- Environment Setup
2- Explain Plan ; the fetch , execution step
3- Session Trace ; the Parse step

1- Environment Setup :

a11

a12

a13

2- Explain Plan ; the fetch , execution step :

a14

a17

a18

a19

First Conclusion : Both Sql queries with or Without Alias have the same execution plan, same cost of execution.

3- Session Trace ; the Parse step :

Lets perform a more deelply analisis using session Sql Trace.

a19

a19

a19

a19

a19

As shown above, the query with Alias spent a time of “tim=4142243294” in the parse process.
The query without the use of the alias, the parse process spent a time of “tim=4142345831”
The no-alias takes longer to parse. The question is now : If my application response time is of 0.3 seconds, I should tune it more to descrease it to 0.01 seconds, but will the end user be able to perceive it?!

Conclusion : Table Alias speed up database access at the level of the parse process.

Hope it helps!

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: