Re: My first large table

John Leslie wrote:

> Currently have a small database with no table having more than 50000
> rows.
> Now we have a requirement to load large amounts of data into a table
> which will be held for 3 months, resulting in several million rows.
> The data will be loaded using sqlldr and thereafter queried for adhoc
> report purposes only.
>
> Just want to know, what are the basic things I need to do in setting
> up this table to ensure decent performance.
>
> Do I need to reorganize the table indexes on regular basis to retain
> performance? If so how? As you can tell I'm quite new at this!
>
> Thanks in advance,
> JL

Several million rows is still a very small table. I'd do absolutely nothing other than that which constitutes good practice.

1. Make sure the table is in a locally managed tablespace with uniform extents.
2. If you can't do the first make sure initial and next extents are identical and pctincrease is zero.
3. Make sure you have indexes appropriate to your WHERE clauses.
4. Make sure statistics are run with DBMS_STATS and are always current.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)