Re: Oracle 7.3 Very Large Tables

On Mon, 04 May 1998 18:44:16 GMT, keith_at_glorify.comcom (Keith Fulton) wrote:

>snip
>We have several DBAs who are saying that Oracle cannot support this
>requirement, and that Oracle really can't handle more than about 25 million
>rows in a table, but this seems much smaller than a lot of data warehouses and
>other big installations. >snip

Garbage! I have a small (50GB) data warehouse that has over 25 million rows in one table alone. I have built a 500 GB database with 200+ million rows in one table in Oracle 7.3.3. Your DBA's don't know what they are talking about. It DOES require very careful physical design, including placement of every tablespace.

>Those same DBAs recommended that we use O7.3 table partitioning with views to
>accomplish the scalability we need, and (not to my surprise) that was a factor
>of 10 slower than just the big table.

Again, garbage! I put a partitioned view on top of 60 months' worth of tables (each approx 1.5 GB) in 7.3.3 and it was faster, not slower. The critical thing is that the constraints on the underlying tables MUST be identical for all tables in the partitioned view, or the optimizer doesn't work. Also, you need to run ANALYZE on all the tables in the view and enable parallel query (but no more than 4 degrees per available CPU).

> Now they are saying the only solution
>is to go to Oracle8, which handles partitioning much better (but which I
>think is still not mature enough).
>

I am currently using partitions in 8.0.3 on Sun Solaris 2.6 and it works just fine. I suspect that they just need an excuse to migrate to Oracle8 for resume enhancement purposes.

>Another idea we had to accomplish our scability and history needs are to split
>up these tables into 2 parts--a) the 'current' data for say the last 60 days,
>and b) historical data only there for inquiry purposes. This solution, in
>effect, splits the table into the TP and data warehouse pieces, and seems
>better, but leaves the data warehouse piece at 100+ million rows.
>

With some careful design work that should be fine. Report back on how things go.
Good luck!

Colin Davies
Delete the SPAMOFF. from my email address to reply. Spammers are sewer rats! Received on Mon May 04 1998 - 22:37:35 CDT