Re: re-indexing per statement or per row?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 7 Apr 2002 11:59:01 -0700
Message-ID: <a8q4tl06ji@drn.newsguy.com>

In article <3cb050cc_1_at_mk-nntp-1.news.uk.worldonline.com>, "Paul says...
>
>"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
>news:a8n7mr0d49_at_drn.newsguy.com...

...

>>
>> If you can do it in a single sql statement do it (never code procedurally
>what
>> you can do in a single statement)
>>

....
>>
>> Never do procedurally what you can do in a single statement. I cannot
>tell you
>> how many times I see code like this:
>>

...

>
>Entirely, wholeheartedly and unreservedly agree.
>Intrigued though; this is the first time I've seen you use the N word, then
>twice in one posting ;-)
>
>Paul
>
>
>

Well, it should only count as one never since it said the same thing ;)

True -- I should not have said never, "you should hardly ever and only in the most special of cases do procedurally what can be done in a single SQL statement".

Case in point -- just finished a benchmark. One of the processes we had to do was something like:

truncate a table
insert into it a bunch of stuff

loop

   delete from table
    where (c1,c2) in ( select c1, min(c2)

                         from table 
                        group by c1 having count(*) > 1 );

   exit when sql%rowcount = 0;
end loop;

Never mind that we could write procedurally something better.

Never mind that this was a HUGE unindexed table.

Never mind that loop took three hours *just to delete nothing* (imagine if it deleted a single row -- it would take another 3 hours to figure out it had nothing left to delete!)

We turned that into:

create table T
( columns.... )
unrecoverable parallel
as
select ....
  from ( SELECT Distinct

                c1, c2, c3, c4, c5, ..., 
             last_value( c2 ) over
             ( partition by c1
               order by c2 nulls first
rows between unbounded preceding and unbounded following ) max_c2,
             count(c2) over ( partition by c1
                              order by c2 nulls first
                              range between 0 preceding and 0 following ) cnt
           FROM t1, 
                t2, 
                t3, 
                t4
          WHERE <lots of outer joins, ors and other nastiness> 
       )

 where c2 = max_c2
   and cnt = 1
/

That last_value found the max value. The count(c2) counted how many times that value was present in a given value of C1. We keep only the rows that have that max value AND the count is 1 -- that does exactly what they did procedurally. Thats it -- one line of SQL -- that ran in about 15 minutes (the duration of their INSERT) and it does the EXACT same thing.

So, we went from 3+ hours to under 15 minutes.

In all, we took a process that took 12-24 hours+ and did it about 2 (there were lots of other serial steps, a little "lets do things at the same time" and away we went). All by removing tons of procedural code and just letting SQL do it all...

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sun Apr 07 2002 - 13:59:01 CDT