Thank you, thank you, thank you!


A little while ago Oracle announced the winners of the Oracle Database Developer Choice Awards and I was a winner in both of categories I was nominated,

SQL and PL/SQL

I was surprised and overjoyed when I was notified that I had not only been nominated; but named a finalist.
I’m truly humbled by the supportive votes I received.

I’m also inspired to try to give back even more and I’m got a few ideas brewing for my next few articles.

Thank you again!

Advertisements

3 Responses to Thank you, thank you, thank you!

  1. Matthew Layton says:

    Hi Sean,

    Well done on winning.

    I was wondering if you could help me. I’m trying to write a PLSQL code for parsing a file. You responded saying you improved performance by parsing clob to 32K chunks per time. Long shot but I was wondering if you still had that code?

    https://christopherbeck.wordpress.com/2012/04/03/parsing-a-csv-file-in-plsql/

    Thanks in advance!

  2. Here’s a little test case I ran

    declare
    l_clob clob;
    l_start_time number;
    begin
    for i in 1 .. 20000 loop
    l_clob := l_clob || ‘11111,22222,333333,444444,555555,’ ||
    ‘666666,777777,888888,999999,101010101010’ ||
    ‘aaaaa,bbbbb,ccccc,ddddd,eeeee,fffff,ggggg’ ||
    ‘hhhhh,iiiiiii,jj,kkkkkkkkkkkk,llllllllllll,m’ || chr(10);
    end loop;

    insert into etexts values (‘christopher beck’,l_clob);

    commit;
    end;

    Then using sqlplus

    SQL> set timing on
    SQL> set autotrace traceonly
    SQL> set arraysize 5000
    SQL> SELECT y.COLUMN_VALUE
    2 FROM etexts, TABLE(split_clob(text, CHR(10))) x, TABLE(str2tbl(x.COLUMN_VALUE)) y
    3 WHERE name = ‘christopher beck’;

    420000 rows selected.

    Elapsed: 00:00:01.17

    str2tbl is a function Tom Kyte published years ago. I’ve seen several variations but all are pretty similar. Here’s a link to one version: http://tkyte.blogspot.com/2010/04/something-new-i-learned-about-estimated.html

    The biggest time crunch is simply fetching 420K rows across the network, the parsing is subsecond.

Questions and Comments always welcome

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: