Table extents
Question:
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.
Ans1:
This is because the HighWaterMark
of the table doesn't change when rows are deleted. The only alternative
to recreating is TRUNCATEing it. This will reset the HWM, while the table
remains in existence.
Ans2:
If you have multiple processors,
you could use Parallel Query option. E.G. Let say the extents come up to
150 MG(1 Million Rows) and there are 4 processors on box.
Break datafile into 3X51MG
files, re-create table into 3 separate extents of 50MG with a min extents
equal to 3(in version 8, use partitions). Also put the degree of parallelism
on table to 3.
If need table operational 24
hrs a day, truncating is obviously not an option.
Ans3:
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.
Ans4:
If you 're using the cost
based optimizer try to analyze the table when it goes slow.