Re: creating tablespaces in parallel

Relevant to this post, I have a similar question:

Suppose I have a table of 100GB (Is it ok to have such a large table?) which has 200 columns (maybe 600 million records), I need to create few new columns from some existing columns (eg: c = a + b), which one is more efficient:

1. use "create table as" with "parallel" and "unrecoverable".
2. use "update set c=a+b".

Is it possible to have Oracle not creating redo and rollback in b)? Seems to me that significantly slow down the processing.

Also, does anyone have any idea of how long a,b might take?

Thanks in advance.

David

On Fri, 5 Dec 1997 08:44:43 +0200, "Billy Verreynne" <vslabs_at_onwe.co.za> wrote:

>Madhusudhan Nunna wrote in message <667fbp$69j_at_athos.cc.bellcore.com>...
>> Did anyone face the problem of creating a 500GB database
>>in less than a day? Well I did and tried the obvious option of creating
>>tablespaces in parallel, by running multiple server manager sessions.
>>I'am trying this with oracle 7.3.2.3 on a HP-UX11 machine with 16 CPUs.
>>But I'am not seeing the expected improvement in tablespace creation
 timings.
>>It is still taking as much time as it would take if they were to be created
>>sequentially.
>
>
>Exactly how are you trying to create tables - using the CREATE TABLE AS
>SELECT statement? Or running multiple UPDATE sessions? And I assume you're
>not using parallel query?
>
>First off, I will use the parallel query funtion - you seems to have more
>than enough horsepower on youe HP box. Secondly, any transaction orientated
>SQL statement is out. To load that amount of data I will rather use
>SQL*Loader with the direct and parallel load options. If some of the tables
>need to be created from existing tables, I will use the CREATE TABLE AS
>UNRECOVERABLE AS SELECT and add the PARALLEL clause. Lastly, I will make
>sure that I create the tables and datafiles and/or load the data in such a
>manner as not to hammer a single SCSI channel with multiple sessions trying
>to load data.
>
>It's also a good idea to turn off any unecassary oveheards before the time,
>like running the instance in noarhive log mode, turning off timed stats,
>etc. until all the data has been loaded. And if you can, kick everyone else
>of the system, shutdown any other database instances and configure your
>instance to grab all available resources.
>
>It's great being the only user on a big machine! :-)
>
>regards,
>Billy
>
>
>
Received on Sat Dec 06 1997 - 00:00:00 CST