Re: Enhancing large table performance

If you have multiple CPU's, you might want to try to use the PARALLEL clause of your "create table ... as select ... from ..." statement. Also use NOLOGGING. The result will be "create table ... nologging parallel X as select ...", where X is the number of parallel processes you want to use. Finding the optimum value for X is a whole science/art in itself. I find myself that 4 or 5 is often the optimum, if you have enough CPU's.

Daniel

> "Pritam" <pritamganguly_at_hotmail.com> wrote in message
> news:166770ed.0309282344.f374aec_at_posting.google.com...
> > Hi all
> > I am quite new to oracle. I have a problem in hand. I have a table
> > which has around 300,000 entries. I am creating four more tables from
> > this table each of size around 70,000 entries. I am doing this through
> > java application. The time oracle server takes create each table is
> > around 3 hours. So the total time taken is around 12 hours. I need to
> > shorted this time to around 3-4 hours. Can anyone give me some
> > directions. Like creating 4 threads in java to parrallely create those
> > four tables. Will that help?. Or ways to tune oracle to serve the
> > purpose.Or is there ways to write the SQL so that it takes less time.
> > Its a simple query to create sub tables from a master table based on
> > some fields. The oracle database is on a PC having the following
> > configuration. 256MB RAM, 40GB HDD, P4 2Ghz. Do i need to upgrade my
> > machine. Plz give me any leads you can to reduce the table creation
> > time.
> >
> > Thanks in advance
> > Pritam
>
> Pritam
>
> It's always helpful to state Oracle version and OS.
>
> Why are you creating the four tables from the larger one ?
>
> How often will you be doing this ?
>
> If you really do need the four tables is it not possible to execute
> 'CREATE TABLE DEST_TABLE_1 AS SELECT A,
> B, .. FROM SOURCE_TABLE' ?
>
> Your PC lacks RAM - should be at least 512MB.
> What would be the consequences of your hard disk going duff ?
>
> eric
Received on Mon Sep 29 2003 - 15:16:43 CDT