Re: Build Indexes as Scheduled task?

From: Marc Blum <marc_at_marcblum.de>
Date: Sun, 01 Sep 2002 20:58:35 +0200
Message-ID: <s7o4nuge5trgkfo555s9gg48o3lbkdqb8h@4ax.com>

On 27 Aug 2002 05:02:42 -0700, iain.dickens_at_wgrant.com (Iain Dickens) wrote:

>Hi,
>
>ETL (informatica) is on Win2K and does have pre/post session where I
>can call batch file that runs scripts to drop/build indexes. This is a
>little messy as I have to maintain batch files, SQL files and ensure
>if ETL schedules are altered that the PRE/POST session is amended.
>I was hoping to submit from DBMS_JOBS and maintain only two procedures
>(drop and create) where I can apply conditions at database level. As
>PL/SQL does not support DDL directly I am really looking for an
>alternative where I maintain 2 procedures (or similar). But obviously
>less maintenance than post session from ETL tool.
>It may well be I would be better sticking to what I have at the moment
>as it does work.
>
>Thanks
>
>Iain

Hi,

I'm a little bit late on this thread, so perhaps there's nothing new what I can tell?!

if data is loaded via SQL*Loader and direct path, index rebuild is suspended until completion of each load. Comes in very handy.
if data loaded in some other fashion, I would suggest the following:

pre load task:

DECLARE
   CURSOR cur
   IS
   SELECT index_name
     FROM user_indexes;
   --
   l_sql VARCHAR2(1000);
BEGIN
   FOR rec IN cur
   LOOP

      l_sql := 'ALTER INDEX '||rec.index_name||' UNUSABLE';
      --
      EXECUTE IMMEDIATE l_sql;
      --

END LOOP;
END;
/

do the load, do the twist, do the matched potato...

post load task:

DECLARE
   CURSOR cur
   IS
   SELECT index_name
     FROM user_indexes;
   --
   l_sql VARCHAR2(1000);
BEGIN
   FOR rec IN cur
   LOOP

      l_sql := 'ALTER INDEX '||rec.index_name||' REBUILD';
      --
      EXECUTE IMMEDIATE l_sql;
      --

END LOOP;
END;
/

advantages:

you don't have to worry about new indexes
you don't have to worry about changes in existing indexes

Attention:
I put the SQL into a local variable because of a nasty bug fixed in 8.1.7.2 (you're still on 8.1.7.0, aren't you?)

hth

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun Sep 01 2002 - 13:58:35 CDT