Twitter Linkedin RSS
Close
Miscellaneous

Thank you, thank you, thank you!

Sean D. Stuber3 Comments

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!

3 thoughts on “Thank you, thank you, thank you!”

  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.

    Reply

Leave a Reply