Re: Table Size Limitations

In article <3p8dkq$33j_at_news1.cle.ab.com>

           ajamough_at_cle.ab.com "Raghid Ajamoughli" writes:

: >
: > Hi All,
: >
: > Are there any limitations within Oracle as far as the number of table
: > entries which can occur for a table of let's say 250 bytes and
: > still get adequate retrieval performance? Let's say that disk
: > space and memory are no problem.
: >
: The only limitation that you can;t have more
: that 255 columns in a table.
:
: As far as rows and type of data, i have table
: with 400,000 records and varchar(2000) fields
: and i still get prety good performance.

Thought for the day (for those to whom disk space and memory are no problem):

Take a table with a primary key of about 30 bytes (including Oracle overhead), in a database with block size of 4K: assume that the index blocks are at the nominal 75% usage level (as implied by the balanced B-tree algorithm) This gives you approximately 75 index entries per block (leaf and branch).

At this rate:

	A single layer of index gives you 75 table entries
	A 2-layer index gives you 75 x 75 table entries (5,626)
	A 3-layer index gives you 75 ^ 3 table entries (421,875)
	A 4-layer index gives you 75 ^ 4 table entries (31,640,625)

Since the index root is likely to be in memory all the time, this means that 4 disc accesses (3 for the index layers, one for the table) gets you a row by primary key. Typical speed for fast discs seems to be 20 milliseconds  per access, so this means less than tenth of a second response times on a 30 million row table.

In fact, with even a reasonably small db block buffer, the top two layers of the index could be buffered most of the time, so you could actually traverse a five-deep tree for the same cost: i.e. 2 Billion rows with a response time less than 1/10 sec.

The problem with large tables isn't the number of rows in them: its the number of rows you want to get out of them.

NB: The figures above are a little optimistic because real-life rarely understands the theory; but it is worth remembering that absolute size is not inherently a problem.

-- 
Jonathan Lewis