Monday, August 4, 2008

Optimisation of "SELECT * FROM"

This is my first post on the blog that I created recently. The name of the blog implies to have
regular updates on the blog with articles about variety of database administration aspects.
Will it happen? Well... at least it is in the name :)

The first one is going to be the short one and it is about optimization of the important for every
dba statement "SELECT * FROM". You probably think that I am going to tell you some secret
about optimizer not exposed in all details recently by gurus (since it is my first post, right?)
No, it won't happen. I just want you to think how many times per day you type
"SELECT * FROM " or similar statement (with addition of rownum for example)?
Do you remember how many times you made errors in that simple statement
when you were in hurry fixing something and/or using unaccustomed keyboard?

How about to simplify work using a script like this:


SELECT * FROM &1
/
undef 1


If you save it under the name s.sql you can use it like in the manner:


SQL> @s dual


Moreover, you can enhance scripts to select 10, 20, etc or based on like criteria rows from tables.

I use it to select table names from dictionary when I can't recall exact names and underscore
symbols whereabouts there.


dict.sql:

select table_name from dict where table_name like upper('%&1%')
/
undef 1


@d tables


As soon as you get how helpful your scripts are you can start using them over Internet:

@http://<...>/s.sql



That's it for today.
Have a good one!

No comments: