Re: Enhancing large table performance

pritamganguly_at_hotmail.com (Pritam) wrote:
> 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.

That is a small table. Do you have large LOBs in it?

> I am doing this through
> java application.

That doesn't mean anything. "My car won't accelerate, I'm pushing on the gas peddle with Nike tennis shoes."

> 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.

I'd start by running the query in SQLplus, seeing how long it takes and what the explain plan is.

> Like creating 4 threads in java to parrallely create those
> four tables. Will that help?.

Maybe, maybe not. If you have 4 processes now fighting each other for the same limited resource, it could make everything slower, rather than faster.

> Or ways to tune oracle to serve the
> purpose.

That would depend on how you have it tuned now, and what the purpose is.

> Or is there ways to write the SQL so that it takes less time.

That would depend on the SQL and the execution plan for it, neither of which we know.

> Its a simple query to create sub tables from a master table based on
> some fields.

What kind of logic is applied to those fields? indexes?

> 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.

Your memory seems small, one HDD is pretty quaint for a real Oracle system, but I doubt those would be the main culprits.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB