Re: Large number of columns

Howard,

Your discussion is very appropriate, however you didn't take into consideration the fact that we don't know how much data changes (aka "row growth") he has. He does say it's a legacy application, which tends to make me believe that not much new data is added or modified in the system.

If the data is pretty much static, he could reduce his PCTFREE to 5% and be happy. Alternatively, he could set his PCTFREE to 0%, import his legacy data, and reset the PCTFREE to a more "normal" value (say 10%) and then the new rows would be stored with the new value of PCTFREE. I'm not 100% sure if this is "legit" but I'd certainly try it.

As far as performance is concerned, if he seperates the table into 2 or more tables, the overhead of joining the two or more tables may just end up with a wash as far as performance is concerned, versus having just the one table.

Personally... I'd leave the legacy data just the way it is, UNLESS there's a problem.

JP

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:<aaq9o9$fm5$1_at_lust.ihug.co.nz>...
> Yes to your last question. Just as an example, if the average row length of
> these monster tables was, say, 1500 bytes then in an 8K block system, you
> can fit 4 rows per block (the computation is, roughly, 8192-819 (for
> PCTFREE) = 7373 bytes free per block. 7373/1500=4.9 -and since you can't
> have 0.9 of a row, you get 4).
>
> Now let's say your report, using an index, needs to retrieve 20 rows. You'll
> need to do 5 block reads (on average) to get the requisite data.
>
> Had your row length been, say, 500 bytes, then a block would have contained
> 14 rows, and you might have got your data back in 2 block reads.
>
> Incidentally, Oracle never chains rows unless it absolutely has to. And the
> only reason that it has to chain a row is if the row is longer than the
> block size (note that row chaining is not the same as row migration, which
> happens whatever the row length is, if there is insufficient space in the
> block for the row to *grow* in length). So, I'm not surprised that you have
> a low setting for CHAIN_CNT.
>
> What it all means, however, is that the density of data in your tables will
> be outrageously low, meaning a lot of I/O activity to retrieve required
> data. You'll also be wasting a lot of space in your blocks -consideraby more
> than the 10% you were expecting because of PCTFREE. Go back to that first
> example: we had 7373 bytes free after PCTFREE had been taken into account.
> Yet we could only store 4 rows of 1500 bytes, meaning that only 6000 bytes
> of row data could actually be stored. So that's 1373 bytes *in addition to
> PCTFREE* which is just sitting there, unused. That's going to affect the
> efficiency of full table scans, as well as your index accesses.
>
> Remember that in performance tuning, it's the application designers and
> developers who do the bulk of the work, long before the DBA gets anywhere
> near it. What you've got here is a classic example of why that's true (or
> supposed to be): you basically have now to re-design the entire application
> to sort this problem out, normalizing the tables from scratch.
>
> Good luck
> HJR
>
>
>
> "Andy" <enzoweb_at_hotmail.com> wrote in message
> news:8d4033cd.0205011826.66dc1c83_at_posting.google.com...
> > The Oracle Version is V806, running on Solaris, block size is 8k.
> >
> > I have just started work at a site where they have a legacy
> > application converted to Oracle, with no analysis done, it was just
> > picked up and dropped in.
> >
> > Consequently there are over 50 tables which have over 250 columns.
> > There are a couple with over 300 columns.
> >
> > What, if any, impact will this have on performance? I have done an
> > analyze on chained rows and there are amazingly very few, whereas I
> > expected a lot. PCTFREE is the default of 10.
> >
> > Do I need to worry about this, or can I leave them? I thought about
> > splitting the tables up, which would only result in queries having to
> > do joins. So what is worse, large number of columns on one table or
> > having to join 4/5 tables?
> >
> > If the tables are properly indexed, would the number of columns
> > matter?
> >
> > Thanks.
Received on Thu May 02 2002 - 07:47:59 CDT