Those of you that know me at work know that I don’t like complicated code — I call it “over-engineered”.  My staff now all know who Rube Goldberg is because that’s another thing I call over-engineered code — “Rube Goldberg code”.

Anyway, you know when you’re going down that path when you can’t get something to work — even though your code was copied straight from the Doc?  And then you keep hammering away at it, adding more and more functions, wrappers, etc until it finally works?  Now you have ugly code (but it works!) and still don’t understand why the code doesn’t work like the documentation says.

It’s often a lot of work, but worth it in the end to understand why it doesn’t work like it’s supposed to.

So, revisiting my migration story…

I get a call from the group and they’ve decided to use sqlldr and control files instead external tables.  Ok, I guess.  I think the external tables are cooler, but Ok.  And they try to load 9M rows into a partitioned IOT using a single stream, convential path.  Time to load = 28 minutes.  Too slow for me.  So I look at the control file, and I see something odd:

In the fields spec, I see:

VERSION_LOCK “to_number(trim(both ‘ ‘ from :VERSION_LOCK))”

The fields in the datafile are simple integers separated by tabs.  So why the complicated conversion routine?  They tell me they keep getting “invalid number” during the loads, so this is what they “HAD” to do.  Turns out that VERSION_LOCK is an optional column and some rows in data source don’t contain any data for the column.  And TRAILING NULLCOLS wasn’t taking care of it either.

This looks like overcoding to me.  So I did some digging.  And while I’m at it, I play some more with the external tables.  I declare the external table and lo and behold, I get the “invalid number” errors too.  So finally, I declare all of the external columns as CHAR to see what’s in them.  And then I use the good ole DUMP() function to see.

Guess what?  The column that was “empty” actually had a single blank character in it.  And here’s a simple quiz:

What does to_number(‘    100   ‘) return?  Answer, 100.
What does to_number(’59′) return?  Answer, 59.
What does to_number(’86 ‘) return?  Answer, 86.
What does to_number(NULL) return? Answer, NULL.
What does to_number(‘ ‘) return (single space inside the quotes)?

I think you could be forgiven if you guessed NULL — after all, it appears that to_number trims both leading and trailing spaces, and if it trims all the spaces, it should come up with NULL — but it doesn’t.  It says “invalid number”.

So, I need to convert the single space to NULL, because that’s what it is.  A simple LRTRIM option on the fields spec for the external table takes care of that — and now I can go back to declaring the columns as NUMBER because that’s what they are.

I do one other thing — I convert their partitioned IOT table into a simple heap table with a primary key index (actually I will create the index after the load is complete).  So far I haven’t gotten a reason why their 9M row table needs to be an IOT or partitioned, so I’ll try the heap one.

Time to load 9M records = 90 seconds.  Time to index 9M records = 150 seconds.  Total time = roughly 4 minutes.  Much better.

Do I think the time savings was due to the extra code? No.  It was probably due to the paritioned IOT.  But I still learned a lot.  Moral of the story?  One we all know and don’t apply enough — check your assumptions before starting to write “Rube Goldberg code”.

3 Responses to “Overcoded”

  1. Doug Burns Says:


    One of my favourite words, too. I find myself using that all the time ;-)

  2. Kate Says:

    Thanks for the tip! Made me figure out why a query I was working on wasn’t working.

  3. Peter Teoh Says:

    Well….it cannot be overemphasized…

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder