Re: purging old data

Not forgetting to do this NOLOGGING of course, and using the /*+ Append */ hint.

Of course version 1 still gives you the foreign key problem, and version 2 gives you the privileges problem, and in both cases a user might slip in some inserts which get lost in the windows of opportunity between commands.

Now if you run 8.1, you can:

    delete redundant rows
    alter table XXX move .....
to rebuild the table with the same name, changing the physical storage, etc.

Moreover, if the table is an IOT, then
you can do this ONLINE (although you
pay the penalty of all the guesses in
the secondary index becoming invalid
and having to drop and recreate the
secondary index to get them back).

--

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

Jerry Gitomer wrote in message <7r5n7c$j35$1_at_winter.news.rcn.net>...
>Hi,
>
> Have you considered the following:
>
> CREATE new_table AS SELECT * FROM real_table WHERE ...
> TRUNCATE TABLE real_table
> INSERT INTO real_table SELECT * FROM new_table
>
>or:
>
> CREATE new_table AS SELECT * FROM real_table WHERE ...
> DROP TABLE real_table
> ALTER TABLE new_table RENAME TO real_table
>
>regards
>Jerry Gitomer
>
>
>Anurag Minocha wrote in message
><37D63DE7.B04D2DC9_at_synergy-infotech.com>...
>>Thanks for the help.
>>
>>The problem I am having is that i have to purge data based on a
>query and
>>truncate doesnt support that hence i will have to delete the
>records. The reason
>>i am purging the data is because the queries will run faster if
>there are less
>>number of records , but deleting wont help because oracle will
>go till the high
>>water mark and then produce the result. How do i use the delete
>command and make
>>my queries run faster on my tables.
>>
Received on Wed Sep 08 1999 - 08:36:26 CDT