Re: creating indexes script

From: Doug Anderson <dathedba_at_mindspring.com>
Date: 1997/05/15
Message-ID: <5lfstn$2e3@camel7.mindspring.com>#1/1

I know this may look a bit messy but it works for me. Since you may only have up to 16 columns in an index you only have to do 16 MAX(DECODE... lines. Also, I use DBA_SEGMENTS to establish how big the initial extents should be. Notice that I'm using parallel query, therefore, I have multiple initial extents.

---

REM Doug Anderson 15-MAY-1997

select 'create ' ||

       DECODE(max(i.uniqueness),'NONUNIQUE',null,max(i.uniqueness)) ||
       ' index ' || max(i.owner) || '.' || max(i.index_name) || '
       on ' || max(i.table_owner) || '.' || max(i.table_name) || ' ('
||
       MAX(DECODE(c.column_position,1,c.column_name,null))  ||
       MAX(DECODE(c.column_position,2,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,3,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,4,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,5,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,6,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,7,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,8,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,9,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,10,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,11,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,12,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,13,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,14,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,15,(','||c.column_name),null)) ||
       MAX(DECODE(c.column_position,16,(','||c.column_name),null)) ||
       ')' || '
       pctfree ' || max(i.pct_free) || '
       storage ( initial ' || ROUND(((max(s.bytes)/2)/1024)/1024) ||
       'M next ' ||
       max(i.next_extent) || ' pctincrease ' || max(i.pct_increase) ||
')
       tablespace ' || max(i.tablespace_name) || '
       unrecoverable
       parallel (degree 2);'
  from dba_indexes i, dba_segments s, dba_ind_columns c
 where (i.owner      = c.index_owner)
   and (i.index_name = c.index_name)
   and (i.owner      = s.owner)

   and (i.index_name = s.segment_name)
 group by c.index_owner, c.index_name
/

-Doug Anderson
 DAtheDBA_at_mindspring.com

"Eric W. Worden" <eworden_at_worldramp.net> wrote:

>anyone have any experience rebuilding their indexes into other
>tablespaces? i'm working on a script, but i'm stuck when it comes to
>composite indexes and how to populate the columns' field in the create
>index statement. this is going to come in handy for 800 indexes.
 

>appreciate the help.
 

>script:
 

>select 'create index ' || U.index_name || ' on ' || U.table_name || ' ('
>||
> column_name || ', ) ' || 'tablespace MY_INDEXES '
>from user_indexes U, user_ind_columns C
>where U.index_name = C.index_name

>--

>+------------------------------+
>+ Eric Worden +
>+ eworden_at_worldramp.net +
>+ +
>+ 407-306-1343 +
>+------------------------------+

• Doug Anderson Oracle Database Administrator DAtheDBA_at_mindspring.com

Received on Thu May 15 1997 - 00:00:00 CDT