Re: Need your suggestion.

Your better off recreating the summary table each time by first dropping it and then rebuilding it using

CREATE TABLE <table name> AS SELECT ...

Deleting is always a very slow operation - Avoid doing it if possible. CREATE TABLE AS SELECT ... is faster than INSERT INTO ... SELECT .. so is therefore the best option.

You can use the UNRECOVERABLE option with the CREATE TABLE AS SELECT which further increases the performance because ORACLE does not write anything to the redo logs

If you are unsure of the syntax look in the SQL Language Reference Manual in the CREATE TABLE section. Using this method should increase the performance of your operation by at least 50%

Further improvements can be got through the use of the PARALLEL QUERY OPTION. sirirut vanichayobon wrote:

> Hi,
>
> I have one very big table. I collect data every 5 minutes. The table
> has data about 2 years.
> I have to build a summary table (e.g., calculating MAX, MIX) for a
> day to store my summary data so I create a temporary table to keep data
> for specific day from big table. I am using PL/SQL.
> My question is when I want to calculate summary data for the next day
> and I have to REUSE the temporary table to keep data (retrieved from the
> big table) for that day, should I drop the table and recreate it, or
> should I only delete data in the table so I don't need to recreate it.
> Which method is better? Why? Any other method?
> Any suggestion would appreciate.
>
> Thanks in advance,
>
> -sirirut-
Received on Sat Aug 22 1998 - 16:29:50 CEST