The Question of the day...

Some days... Some days the questions just make me scratch my head....

ROW TO COLUMN CONVERSION   April 30, 2008 - 5am US/Eastern 

Reviewer: ROOPA from india 
HOW TO CONVERT YEARLY DATA INTO MONTHLY DATA? 
Followup   April 30, 2008 - 10am US/Eastern: 

BY MAKING IT UP I GUESS? 

Could it be more ambiguous?  I have yearly data (one presumes that is data aggregated to the level of a year).  How do I convert that into monthly data.  Short of "making it up", I have no idea... do you?

Now, they did followup later with

 

table1 format
MONTH                  AMOUNT_PAID
01.12.2006 00:00:00        5395
01.11.2006 00:00:00         567
01.11.2006 00:00:00        1974
01.04.2007 00:00:00        2462
01.04.2007 00:00:00        1974
01.11.2006 00:00:00        5395
01.02.2008 00:00:00        5395   

table2 format
MONTH         JAN  FEB MAR APR MAY JUN JUL AUG SEP OCT  NOV  DEC
01-DEC-2006   0    0   0   0   0   0   0   0   0   0    0   5395
01-FEB-2007   0   5395 0   0   0   0   0   0   0   0    0    0
01-NOV-2006   0    0   0   0   0   0   0   0   0   0   5395  0

how to convert table1 format into table2 format i.e yearly data to monthly data.

 

Now, I don't know about you - but table1 looks suspiciously like "discrete observations with an associated date - the date consisting of year, month and day".  I certainly do not see "yearly data".

I also like how they used 5,395 three times, just to make it as ambiguous as possible (wonder what happened to 567, 1,974 and so on?)  They skipped what are likely the interesting output examples - their "yearly data that is not yearly" that has more than one observation in a month.

I guess, I GUESS, their date format is DAY-MONTH-YEAR now, that changes table1 to look suspiciously like "discrete observations with an associate date - the date consisting solely of year and month".  But, we'd be GUESSSING.

And I see a 01-FEB-2007 in table2, but I see 01.02.2008 in table1.  I have to presume that is a "typo"

sigh, and there wasn't even a create table, insert into table supplied - they want me to do that.

And the output looks utterly useless.  If column 1 is "01-dec-2006", why bother having a DEC column in the output?  We already KNOW what month this is for - every row will have 11 zeros, every single one.  Seems a bit "silly".

Asking good, well formed questions is not an art, not magic.  It is however a skill.  And I find many times that when I frame my question for someone else - I find my answer.

Goes back to yesterdays post.  Writing software requires some things - a plan being one of them.  Until you can phrase your requirements in a detailed fashion - I'm not sure you know what they are or why you are doing something.....

 

We have a runner up for second place..

entire question is:

Record level Audit Old\New value same Error  April 30, 2008 - 9am US/Eastern

Reviewer:  sasirekha  from India

I have some problem using Audit Record.

Generally if we map a record to the audit Record, it will track the details of

the table insert, update, delete.

While I update the record, it will insert two different row in audit record

like  Audit Action K and N.

But both are contain the same values..

I need the old and new value.

Can any one please give me the solution with this !

questions from me:

• what is an "audit record", must be well defined - they are using it



• "if we map a record to the audit record" - not really sure what that means



• "like audit action K and N" - K and N?? huh?



• "but both are contain the same values - I need the old and new value" - well, why didn't they access the old and new values?



• where is the sample, the example, the thing that shows us what you are really doing....

And in a close 3rd place...

How to speed up the insert and update in a partion table of more than 60 millions Rows ?

Best regards,

Sam.

I don't get it some days, just do not get it.

How not to do it...

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.

The 'write' stuff...

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.

UKOUG Call for papers...

The UKOUG call for papers has been announced.  This is an event I've personally attended every year now since 2003.  I've met and made some very good friends at this conference.  I strongly recommend it as a technical forum.  It is a large but not overwhelming large event - with lots of technical content (and a bit of fun as well).

I've written in the past about participating in the user community - this is the perfect chance.  Everyone has something useful to contribute - everyone does. 

So, get on the OTHER SIDE of the podium.  Yes, it can be scary, but it is definitely worth the fear factor long term.

Looking for ideas...

I use a couple of venues to generate new material, new talking material every year.  Hotsos symposium has always been one, Oracle University Seminars have been another.  Oracle OpenWorld is definitely one as well.

Well, believe it or not, it is time for me to submit my abstract for Oracle OpenWorld already - even though it is not until September.  So, it is time to come up with yet another topic.  I've been known to do a "top ten" style presentation for a while at OpenWorld - the last few years have been top tens on 9i Release 2, 10g Releases 1 and 2 and most recently - 11g Release 1.  Intermixed with that was a database 'worst practices' presentation as well.

So, now - I'm looking for some fresh ideas.  A topic that can be well covered in an hour and would have broad general appeal.  In reality - if I get a couple of good ideas, I'll probably generate the material for all of them but just pick one (maybe two) for presentation at OpenWorld. 

So, I invite you to submit an idea - doesn't have to be fully fleshed out - just a concept, something you would find useful at a venue such as OpenWorld (or a presentation to a user group - as this is where this material will be used time and time again...)

Thanks in advance for any ideas!

It has been a while...

It has been a while since I've last posted.  Was waiting for inspiration I guess.  I'll be a bit more regular in writing in the future, just hit a dry spell for a bit.

I received inspiration this morning in the form of a question on asktom.  Nothing like a good rant to get going again... I really fear for our "profession" sometimes.  I'm not even really sure some days it is a profession - because if it were a profession - that would imply, well, qualifications. 

Ok, so here it is - the question du jour:

i need your help on how to write applications for mobile banking usin sql, pl/sql or any other product availabe, i am a applications developer and a junior DBA just starting and it is one of the targets i heve to meet in my job,the deadline is getting closer.
your help will be appreciated.
thank u tom for given me an oppurtunity to ask this question.
will be expecting ur reply

Ok, here we are - I want an education on how to write applications for mobile banking.  Now, "mobile banking"  isn't really something you want someone to cut their teeth on is it?  I mean - I really really want my bank to use developers that already sort of know how to build a transaction processing system.  Also, what sort of financial institution would use a single "junior DBA and part time application developer" as their development team?

"The deadline is getting closer" - and they do not know how to develop a database application.  That is something they should have possibly considered before accepting the job.

The IM speak of "ur" and "u" were just the icing on the cake.

Cary Millsap just wrote about something similar.  An application interface in the UK that offered him the first 2,000 qualifying bus trips he could take from one airport to another.  What were they thinking as they "designed" this - what were the developers thinking when they said "yeah, we'll make this user interface to find a bus easy - we'll be super efficient and terminate the search after 2,000 hits".  2,000 hits.  Seems like anything more than 10 would be overkill.

Another recent thread on asktom got under my skin as well.  A reader wrote in part:

My problem is that I have critical servers (24x7 with a 99.999% SLA)

I suggested, perhaps, that implementing some HA features would be useful in helping them get to 99.999% (a hard task indeed).  They write back:

I think that my customer don't have budget to HA.

The only thing I can think to say back to that is

So, stop saying "I have to have 5 9's" if you are going to say "don't have budget" - they are oxymoronic.

I really don't think that people understand what they are signing up for some days.  "teach me mobile banking - I have a deadline".  "Give me 5 9's, without any money".  Five nines takes a lot of up front design work, a lot of infrastructure, a lot of effort - it doesn't just "happen"

I am not generally in favor of "certification", but I seriously think we have a real problem here.  If just anyone is allowed to put themselves up as a developer - everyone will.  Regardless of ability, training, whatever.  Doctors and Lawyers and many other professionals have to meet some basic minimums in that area - perhaps the people that write the stuff that manages our money, our health, our airplanes, heck - virtually every aspect of our lives in many cases - should have to do the same. 

 

Updated: I think this link is somewhat relevant...  Long but relevant...