Re: defrag needed

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 20 Aug 2001 09:10:40 +0100
Message-ID: <998295200.5883.0.nnrp-12.9e984b29@news.demon.co.uk>

Don't forget the cost of coding, testing, regression testing etc. In this case, the total cost of change for the partitioning solution could be very high even though
it may be strategically the best move.

The code/test cost of implementing a
table rebuild is pretty small by comparison. (BTW - use MOVE table rather than
copy and rename - it eliminates the
hassle of sorting out privileges and
invalidations due to dependencies).

Something like@

create or replace procedure rebuild_table(i_table in varchar2) as

begin

    execute immediate 'alter table '|| i_table || ' move';     for r1 in (

        select index_name
        from user_tables
        where table_name = i_table
    ) loop
        execute immediate
        'alter index ' || r1.index_name || ' rebuild';

    end loop;

end;
/

You might want to check that the table has moved (possibly by rebuilding only those indexes which are INVALID) so that you don't waste resources rebuilding indexes unnecessarily.

You might want to re-analyse the table after the move, and add the option to analyze the indexes to the index rebuild command. (cheaper than a separate index analyze).

Cost of regression testing is simply the problem of checking how long it takes, and finding a time at which it can be done for each table.

--
Jonathan Lewis

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

For latest news of public appearances
See http://www.jlcomp.demon.co.uk

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.




Kenneth Koenraadt wrote in message <3b7fa683.2160498_at_news.mobilixnet.dk>...


>

>1)Reorganise the table by

>

>  a) create table BACKUP as  (select * from ORIGINAL_TABLE);

>  b) drop table ORIGINAL_TABLE;

>  c) ALTER TABLE BACKUP RENAME TO ORIGINAL_TABLE;

>

>

>2) PARTITIONING the table (by the date field) could be very good idea.

>Then cut away the partition with the rows that have aged out.

>

>

>

>Solution 1) is simple, but expensive regarding I/O.

>

>Solution 2) is more elegant, more flexible and less costly.

>

Received on Mon Aug 20 2001 - 03:10:40 CDT