Re: HELP: Large ORACLE Tables > 3million rows

I need some help with the design of a system with some large ORACLE tables.

The table will have 100 rows inserted 100 times per day. This will lead to approx 3.5 million rows per year, all of which must be accessible online.

Each row is approx 150 byte, so the table for one year will be approx 500Mb (plus one index of about 50 to 100Mb).

The routine which inserts the 100 records must run quickly and I am worried about the time to update the index.

1. Is the index overhead going to be noticeable.

Index will be definitely be an overhead, but if you are using index for enforcing uniqueness you're left with no choice.

I can split the table so that data over a certain age goes into a history table.

Creating two tables is a good option provided you meet certain conditions:

1. Most queries are performed against current or recent data.
2. There are limited number of queries to retreive time-series information. For example querying a table of share prices (company,effective_date,share_price) for reference of 10 years worth of data. In such cases you'll have to perform queries to go against both current and historic data table(s).

Advantages:

1. Size of the database objects (index,tables) is manageable.
2. Inserts of 100 record is much faster as all data will fit in one extent and index size will be small.
3. Queries against smaller table will give better response time.
4. You can export historic table at less frequency thus saving operations task.
5. Extent growth can be controlled as size of current data table will be fixed.
6. How many rows shoudl I keep in my main table before inserts are slowed.

 It will mostly depend on what is current and historic and also what kind of data  availability you are looking for; For example:  Month to date(MTD) is current, Year To Month(YTM) is historic or  Year to date(YTD) as current and previous year and past is historic. You'll have to study  more about data before drawing a line between current and historic.

3) If I use a view (UNION ALL so avoid remove distinct rows) to combine the two tables will I get a large performance hit?

Release 7.3 supports parallel execution of UNION and UNION ALL operations commonly used in decision support applications to assemble result sets from multiple underlying tables.

Hope I make sense

Sanjay D.S.
Oracle Consultant
Prudential Securities, Inc. Received on Wed Apr 24 1996 - 00:00:00 CEST