Re: Oracle Table extents

Hi,

You are experimenting internal table fragmentation, that mean, because of the numerous insert, delete and update, the rows a no more contiguous, so every search that dont use an index will result in a full table scan to retrieve the rows you need (the worst example is in fact SELECT COUNT(*)). As bjin(Hi!) said, DEALLOCATE UNUSED only remove the free space at the end of the table, so it could help you for a while but wont correct it at all.

I had the problem with a 2Mo rows/1Go table from which i keept only 26000 rows, and use the DEALLOCATE feature. I then get back only 400Mo, the problem was, the remaining rows where for most of them at the end off the table, and all subsequent search (or export) where slower than ever. The trick : i had to export the table,truncate it an reimport. The second : create the good index (tkprof could help you)

Krout!

In article <36E336E4.1F8CCD62_at_hpsgns1.sgp.hp.com>,   bjin <jinbo_at_hpsgns1.sgp.hp.com> wrote:
> Hi,
>
> Try ALTER TABLE DEALLOCATE UNUSED statement. It deallocates unused
> space starting from the end of the table. If the last few extents don't have
> records
> when the table has only a few records, it will help.
>
> Regards
> bjin
>
> noams_at_ncc.co.il wrote:
>
> > Hello,
> >
> > I have a problem with a large online table (260 bytes per row X 600,000
rows)
> > that is accessed a lot by different users that are inserting, selecting and
> > deleting from it instantly.
> >
> > The initial extent that I gave the table was 50MB and next extents 25MB (it
> > can climb up to a million records at some point in the day and can decrease
> > to a minimum of 0 records at some other point).
> >
> > The table and tablespace should be 24H online and ready for transactions.
> >
> > My problem is that a simple select (even count(*)) from the table takes a
lot
> > of time when it has only a few records , the same time as it takes to select
a
> > million.
> >
> > I know that recreating the table every night with a job would do the trick ,
> > but the table should be always online and there are views that use it.
> >
> > What is my best solution?
> >
> > Thanks in advance.
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Mar 10 1999 - 17:00:42 CST