ORACLE-BASE Blog Aggregator

Two years ago at Oracle Open World, I delivered a "worst practices" talk - how not to do stuff.  I used the word "probably" a lot, for example "you probably don’t need to use bind variables" (there were slides stuck in this slide deck throughout that said ‘hey, these are all the opposite of reality - just in case you are reading this - they are not true!’).

In the same genre - we have "Top Ways How NOT To Scale Your Data Warehouse".  This comes from the Structured Data blog penned by Greg Rahn - that article as well as the others make for some really good reads.

While I’m pumping that blog - another one to definitely look at is Richard Foote’s blog.  He’s been undertaking the destruction of many a myth regarding indexing (like ‘indexes like large blocks’, ‘rebuild when height hits N’, ‘Separate tables from indexes for performance’ and the like).  Very easy to read, very enjoyable to read.

If you use multiple computers like I do - you might be interested in http://www.instapaper.com/ - find something you like, but don’t have time to read right now - save it for later.  Very nice.

Not long ago - I wrote about some frustrations with the state of software ‘development’.  This morning I read an article written not too long ago "They Write the Right Stuff".  I liked it a lot.  Maybe not practical or reasonable for every piece of software (but then again, why not?) - but definitely sound techniques and processes for everyone.

The sections are

• The product is only as good as the plan for the product.  Ah, they are talking specifications, communication, documentation…
• The best teamwork is a healthy rivalry.  Indeed!  I’ve said before the best was to test your ability to recover in a DBA team would be to set up two teams - one is responsible for damaging a test database in any way they want to.  The other team is responsible for recovering from that catastrophe.  Next week - switch sides.  Not only fun but very enlightening (when I poll audiences, less than 5%, way less, have done a recovery in the last six months - could it be they might not be able to today if needed?).
• The database is the software base.  Now, they did not mean the database is the center of the application itself (I would say something like that) but rather the history, change control, reason for all change is.  The history, the legacy of the code is as important as the plan for the product.  You need to understand why things were done the way they were - in order to safely change it later.
• Don’t just fix the mistakes - fix whatever permitted the mistake in the first place.  My favorite one!  How many times will I have to hear in my life "we have to do X, but you cannot tell us to use method Y to do it - Y cannot be done".  I get that all of the time - tell me how to make it go faster, but don’t look at or mention touching the application.  The mistakes are typically to be found there - in the application (typically means 99.99999999999999999% give or take a small amount).

As an aside, anyone that knows me, knows my mantra - written many times:

• You should do it in a single SQL statement if at all possible.
• If you cannot do it in a single SQL Statement, then do it in PL/SQL (as little PL/SQL as possible!).
• If you cannot do it in PL/SQL, try a Java Stored Procedure.  The number of times this is necessary is extremely rare today with Oracle9i and above.
• If you cannot do it in Java, do it in a C external procedure.  This is most frequently the approach when raw speed, or the use of a 3rd party API written in C is needed.
• If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…

Therefore - I just loved this Oracle-WTF.  And you know what - it pairs up with the "write stuff" article nicely.  I’ll bet you that that original stored procedure was not planned (no specs), peer reviewed (no health rivalry), change managed as it was tweaked over time, and until now - never "fixed".  Can you imagine how long it took to reverse engineer that into a single SQL statement (I’d guess a minimum of an hour - and likely more).  An hour well spent, but I know personally the frustration of that person for that hour - cursing every developer that touched the code before them.

And I cannot tell you how much I hate code like this:

    BEGIN        v_temp_runs := 0;        SELECT COUNT(*)        INTO   v_temp_runs        FROM   dual        WHERE  EXISTS               ( SELECT *                 FROM   temp_runs );    END;

    IF v_temp_runs > 0 THEN

Why count something and do something else if there was something to be counted?  JUST DO IT, if there is nothing there - SO WHAT?

At least the original code did not end in "when others then null;" - there is that.