472,975 Members | 1,329 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,975 software developers and data experts.

Definition of a 'Very Large Table'

Hi all,
I have heard and read this many times: "Partitions should only be used for
'very large' tables".
What actually determines whether a table is 'very large' or not?
I have tables containing 0.5 million rows, 8 million rows, 14 & 29 million
rows as well.
How do I categorize them?

Any comments will be helpful.

Cheers,
San.
May 12 '06 #1
2 2135
"shsandeep" <sa**********@gmail.com> wrote in message
news:ab******************************@localhost.ta lkaboutdatabases.com...
Hi all,
I have heard and read this many times: "Partitions should only be used for
'very large' tables".
What actually determines whether a table is 'very large' or not?
I have tables containing 0.5 million rows, 8 million rows, 14 & 29 million
rows as well.
How do I categorize them?

Any comments will be helpful.

Cheers,
San.


Partitioning (with DB2 DPF) should be used when there are a significant
number of queries run that would benefit from parallel processing. These
usually include queries where it necessary to read all (or a large
percentage) of the rows in a table in order to return the answer, and there
are a lot of rows in a table, and response time is unacceptable in a
non-parallel environment. This usually occurs when the access plan is one or
more table scans of large tables. There is no magic number of rows that
would tell you when DPF is recommended.

An OLTP system which uses indexes to quickly return only a few rows, may not
benefit at all from DPF, regardless of the number of rows in the table. In
fact, OLTP queries may run slower with DPF because of the overhead required
for each partition to process its portion of the table and for DB2 to
assemble the results into one answer. (There are some situations where DPF
can be used effectively with OLTP, but only experienced professionals should
attempt this).

DPF (data partitioning feature) enables inter-partition parallelism. But you
can also do intra-partition parallelism with only one partition (and without
DPF). UNION ALL views is one way to accomplish this, and I presume that
range-partitioning in V9.1 ( (Viper) due out later summer will do the same.


May 12 '06 #2
San,

My definition is:
table/database is very large if you are approaching some limits, that
hurt your business.

Possible limits are:
time to perform backup, response time of a query, maximum number of
rows that one table/tablespace can hold (per partition is 4 x 10^9),
tablespace size (fe. 512 GB for 32KB page in version 8; 16 TB in
version 9), high machine utilization, etc.

For example, if your environment is powerful enough to serve your
workload you can think that you have "small enough database", even if
it's 10 times larger than your neighbor has.

29 million, wide row table can be large enough to hit maximum
tablespace size. In that case probably you should use larger page size,
partition table using union all, or wait for version 9 (DPF? Probably
not, unless you have terabyte data warehouse).

-- Artur Wronski

May 12 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
8
by: robin | last post by:
I need to do a search through about 50 million records, each of which are less than 100 bytes wide. A database is actually too slow for this, so I thought of optimising the data and putting it all...
9
by: Dave H | last post by:
Hello, I have a query regarding definition lists. Is it good practice semantically to use the dt and dd elements to mark up questions and answers in a frequently asked questions list, or FAQ? ...
7
by: Neil Zanella | last post by:
Hello, I have posted the following message before but got no replies... I am trying to format an HTML definition list with CSS so that it appears as follows, but am having the following problem:...
19
by: shanx__=|;- | last post by:
hi i need some help regarding use of very very long integer datatype in 'c'.. i need it to store result of large number's factorial.. if someone can healp it would be a delight..
1
by: Alan | last post by:
We just switched from vs 2003 to vs 2005. We have a large number of projects in the solution. So to make it easier on everyone we use file references instead of project references. The biggest...
6
by: DaTurk | last post by:
Hi, I'm a bit rusty with the wonderful language of c++. I worked with it, maybe 6 or 7 years ago and now I need to knock the dust off. But I was perusing some code when I noticed that this one...
1
by: datapro01 | last post by:
X-No-Archive: Yes I have a questionabout reorging very large tables. Running DB2 8.1 Fixpack 6 on AIX 5.2 supporting Siebel. I've read through the docs I could find and the postings in this...
275
by: Astley Le Jasper | last post by:
Sorry for the numpty question ... How do you find the reference name of an object? So if i have this bob = modulename.objectname() how do i find that the name is 'bob'
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.