Monday, November 4, 2019

Aliases in Oracle in the cloud or not


Recently I posted in Twitter (https://twitter.com/RicVanDyke) that you shouldn’t use single character aliases.  And I made the point that using full table names is best.  This of course sparked a good dialog that went on for about two days.  

After tuning hundreds of queries with thousands of lines in each of them, I really think that full table names are best.  Next best is shortening the name by removing non-leading vowels.   And even more important is make aliases unique within the statement.  This means if you use the same table more than once, put a 01, 02 and so on after each use.  

But you say “What if the table name is really long?” Are you really that lazy?  You can’t use copy/paste? 

But you say “My query is really short, using single characters just makes sense.”   No, this is about the habit.  You start doing in a small query, 5-6 lines long.  Then you say, well this one is only 20 lines its OK to use single characters.  Then these queries get pushed together, and before you know it you have a several hundred line query with all these “A” and “E” and “C” and what not in it. Which “A” goes with which table?  

And yes this applies to columns and subqueries as well.  Don’t have subquery “A” unioned with subquery “B”.  Give them a name.  Heck I’m OK with SUBQ01 and SUBQ02.  That is way better to search a huge query for SUBQ01 then looking for “A”.  Trust me, that is painful. 

I’m sticking to my guns on this.  Never use single character aliases.  Shorter names are fine, but keep it close to the name of the table.  Sure for EMPLOYEES using EMP is fine, but not E.  Because you’ll find yourself with several subqueries all using “E” and then you’re looking at the execution plan trying to figure out which sub query this predicate goes with.  And instead of it taking seconds, it’s hunting expedition to figure it out. I’ve had to do this way too many times. 

Of course all is good when it’s your code; you (presumably) knew what you were doing when you wrote it.  What about when someone else is reading your code?  

Or you are reading it 5 years later?  Do you really think you’ll remember what you were doing 5 years ago?  Heck I have trouble working on code from yesterday. 

Help others and yourself, don’t use single character aliases.  Make your code easier to read and debug by being more verbose then terse.  You’ll thank yourself later.