Home » RDBMS Server » Performance Tuning » Performance Standard Edition without partitioning (Oracle 11g - RHEL 5)
Performance Standard Edition without partitioning [message #511949] Thu, 16 June 2011 04:31 Go to next message
Messages: 1
Registered: June 2011
Junior Member
Hi everybody:

How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?

300 Millions in only one table with 500K transactions / day is too much?

Simple database with simple schema.

How many records begin to be too many?

Experiences / examples?

Re: Performance Standard Edition without partitioning [message #511951 is a reply to message #511949] Thu, 16 June 2011 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68502
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As far as you can store.
There is no too many.

Re: Performance Standard Edition without partitioning [message #514168 is a reply to message #511951] Sat, 02 July 2011 01:05 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If your queries are only looking at the rows they need then the number of rows in a table is in many ways irrelevant since if your queries are looking only at the rows they need, then they are doing the minimum amount of work they can do regardless of how many rows they actually look at.

That said, there are ways to make some query operations faster using techniques that rely on limiting the number of rows being worked with during the operation (partitioning for example).

I would offer three liberal answers to your question of WHEN IS A TABLE TOO BIG:

1) your table is getting too big when accessing the table is taking longer as you add more rows to it.

2) your table is getting too big if maintenance operations are encroching upon your SLAs (backups are not finishing on time or row cleanup operations are not finishing on time).

3) your table is getting too big if partitioning strategies can be used to reduce your runtimes dramatically but you have failed to take advantage of them.

See how this aligns with

1) your normal day-to-day operations are suffering performance problems.
2) your support operations are suffering based on specific table sizes.
3) there is an opportunity you are not exploiting which could have helped you do better.

Notice that in these gross definitions of "A TABLE TOO BIG", I have not anywhere referenced a specific number of rows. TOO BIG is all about your pain level.

Where do you think you are at?

Good luck, Kevin
Previous Topic: Oracle hash join
Next Topic: SQL tuning
Goto Forum:

Current Time: Tue Sep 26 10:24:13 CDT 2023