Re: Partitioning existing tables, building large indices

create each of the partitions as a totally separate table

    create table t1
    nologging -- 8.1 syntax perhaps
    as
    select * from big_table
    where range_column >= {bottom value}     and range_column < {top value}

    Index as desired
    Analyze as desired

Then create an empty partitioned table
with the correct index sets.
Analyze this empty table.

Then use the EXCHANGE partition to swap the small tables into the correct place. Use the 'including indexes, without validation' option on Exchange.

Rename the big table, rename the partition table

You need to sort out your strategy for ensuring that no-one changes the big table whilst you are copying it.

Oracle 8.1.5 allows on-line rebuild of indicesm otherwise no. For earlier versions an 'unrecoverable' create index in parallel is probably the fastest thing to do.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Greg Stark wrote in message
<87emf92d80.fsf_at_HSE-Montreal-ppp19508.qc.sympatico.ca>...
>
>Is there any efficient way to convert non-partitioned tables into
partitioned
>tables? I would __really__ like to be able to do this to a few tables which
>have grown rather large. Doing it with a select insert would take a really
>long time.
>
>On the same note is there any way to build indices without locking up the
>server? I tried to build an index on one of these large tables (after doing
>alter session set sort_area_size to 10M and choosing a large temporary
>tablespace) and it completely blocked all the other queries running. I
>wouldn't mind if it took hours to build the index as long as the server
>remains operational during that time.
>
>--
>greg
Received on Wed Oct 06 1999 - 02:29:27 CDT