Re: Storage/Tuning Questions for large tables

That sounds like 4Gb tables. I think I would aim for about 40 extents of 100Mb each.

If you don't update the tables, set PCTFREE=0. Since you delete all the rows for a day in one big batch you could probably set PCTUSED down to a very small value, but it probably won't matter if you leave it at 40.

The delete will require a LOT of rollback if you do it in one statement - have a look at my web site for the bit on bulk updates: it would be smart to do a covering set of ROWID RANGE deletes to delete 2 million rows in a number of discrete batches (say 50,000 at a time), otherwise you will need a rollback that can grow to about 150 - 200Mb (2M rows at 50bytes per row plus overhead to be recorded, and that's if you have NO indexes on the table.).

--

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

mikea730_at_my-deja.com wrote in message <8jcaci$q5r$1_at_nnrp1.deja.com>...


>I like the idea of partitions, and I think I'm going to impliment your

>suggestion, but I think I need more time to plan out that

>configuration.  But in the short term (the next few months) I need to

>unfortunatly keep these tables in a non-partitioned tablespace.  I have

>to have the database up and going by thursday.

>

>Would you suggest I create a separate "large" tablespace for these

>tables?  Would it still be efficient to create my initial and next

>extents 10meg in size even though I'm not partitioning?  If so, should

>I also specify a minextents value also?  Would you recommend adjusting

>the PCTFREE and PCTUSED defaults of 10 and 40?

>

>I know I'm asking many questions, but I've got a short time to setup

>this database.  I'm working toward my Oracle Certification, but as you

>can tell, I've just started that process. (but learning fast thanks to

>this newsgroup!!!)

>

>Thanks,

>Mike

>

>In article <962163421.26083.0.nnrp-02.9e984b29_at_news.demon.co.uk>,

>  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>>

>> --

>>

>> Jonathan Lewis

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

>>

>> mikea730_at_my-deja.com wrote in message <8jb5tb$vrs$1_at_nnrp1.deja.com>...

>> >So if I hear what your saying is store a days worth of data in each

>> >partition/tablespace?  We need to keep 40 days depth, would that mean

>> >40 datafiles?  then I could just drop the partition???

>> >

>>

>> That would be 40 table partitions, plus a couple spare - not

 necessarily 40


>> data files.

>> create table XYX (

>>     dated    date,

>>     etc...

>> )

>> partition by range (dated) (

>>     partition p_2000_151 values less than

>> (to_date('22-jun-2000','dd-mon-yyyy'))

>>         tablespace monday_data,

>>     partition p_2000_152 values less than

>> (to_date('23-jun-2000','dd-mon-yyyy'))

>>         tablespace tuesday_data,

>>     etc.

>> )

>>

>> >One problem is that the data continually trickles in.  It might be

>> >difficult to make a "clean" break between the days.

>> >

>>

>> A clean break is not needed -' insert into table' and Oracle

>> will choose the correct partition to insert into.

>>

>> >How bad would it really be if I created a single datafile with a very

>> >large initial extent?

>>

>> Probably very bad.

>>

>> >

>> >Thanks for your help!!!!

>> >

>> >

>> >In article <962136675.24296.1.nnrp-11.9e984b29_at_news.demon.co.uk>,

>> >  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>> >>

>> >> At first sight this looks like an ideal

>> >> set-up for using partitioned tables

>> >> with range partitioning.

>> >>

>> >> Each day gets its independent partition,

>> >> but the table as a whole is viewed as

>> >> a single logical unit.  Create LOCAL

>> >> indexes on the table, so each table

>> >> partition has its own index partition.

>> >>

>> >> Keep a rolling process to add new

>> >> partitions a few days ahead of time.

>> >> Each day you can:

>> >>     alter table XXX drop partition YYY;

>> >> to get rid of one day's data.  No rollback,

>> >> no redo, just a few seconds of invalidation.

>> >>

>> >> There are a few implementation details

>> >> to worry about, but with 40 partitions

>> >> you should have few problems.

>> >>

>> >> At 2M rows per table, and 50 bytes per

>> >> row, aim for 10mb extents to get about

>> >> 10 extents per partition.  I would

>> >> then think about 7 tablespaces, one

>> >> for each day of the week, to help with

>> >> spreading visibility of I/O, keeping

>> >> tablespace sizes down, and making it

>> >> easy to generate new partitions in the

>> >> right place.

>> >>

>> >> Make sure you use locally managed

>> >> tablespaces (uniform size 10M).

>> >>

>> >> --

>> >>

>> >> Jonathan Lewis

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

>> >>

>> >> mikea730_at_my-deja.com wrote in message <8j9ihd$n57

 $1_at_nnrp1.deja.com>...


>> >> >I'm going to be setting up a database used for data collection

>> >> >(inserts) and reporting.  There will be very little updating.

>> >> >

>> >> >My questions are based around two tables that will be continually

>> >> >collecting data via inserts.  The average row size for the two

 tables


>> >> >are 50 bytes each.  I expect to collect roughly +2 million rows in

 each


>> >> >table per day.  The only updates that will occur to the tables is

 a


>> >> >daily delete of rows older than 40 days running a single "delete"

 SQL


>> >> >statement against each table.

>> >> >

>> >> >1) Any recommendations for storage allocations on these two

 tables?


>> >> >Should they have a huge initial extent?

>> >> >

>> >> >2) What impact will the daily deletes have on the tables?  I'll

>> >> >basically be deleting 2 million rows a day.  Could this cause

 problems?


>> >> >(fragmentation?) What should I watch out for?

>> >> >

>> >> >3) Will I need a huge rollback segments to run the deletes?

>> >> >

>> >> >I'm running Oracle 8i on Solaris 2.7

>> >> >

>> >> >Thanks in advance for any advice!!!!

>> >> >Mike

>> >> >

>> >> >

>> >> >Sent via Deja.com http://www.deja.com/

>> >> >Before you buy.

>> >>

>> >>

>> >

>> >

>> >Sent via Deja.com http://www.deja.com/

>> >Before you buy.

>>

>>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.