Here is some general information about tables, regardless of their type:

•\ A table can have up to 1000 columns, although I recommend against a design that does contain the maximum number of columns, unless there is some pressing need. Tables are most efficient with far fewer than 1000 columns. Oracle will internally store a row with more than 254 columns in separate row pieces that point to each other and must be reassembled to produce the entire row image.

•\ A table can have a virtually unlimited number of rows, although you will hit other limits that prevent this from happening. For example,typically a tablespace can have at most 1022 files (although there are BIGFILE tablespaces that will get you beyond these file size limits, too). Say you have a typical tablespace and are using files that are 32GB in size—that is to say, 32,704GB (1022 files times 32GB) in total size.This would be 2,143,289,344 blocks, each of which is 16KB in size. You might be able to fit 160 rows of between 80 and 100 bytes per block.This would give you 342,926,295,040 rows. If you partition the table, though, you can easily multiply this number many times. For example,consider a table with 1024 hash partitions—that would be 1024 * 342,926,295,040 rows. There are limits, but you’ll hit other practical limitations before even coming close to having 351,156,526,120,960 rows in a table.

•\ A table can have as many indexes as there are permutations of columns (and permutations of functions on those columns and permutations of any unique expression you can dream of). With the advent of function-based indexes, the true number of indexes you could create theoretically becomes infinite! Once again, however, practical restrictions, such as overall performance (every index you add will add overhead to an INSERT into that table) will limit the actual number of indexes you will create and maintain.

•\ There is no limit to the number of tables you may have, even within a single database. Yet again, practical limits will keep this number within reasonable bounds. You will not have millions of tables (as this many is impractical to create and manage), but you may have thousands of tables.

In the next section, we’ll look at some of the parameters and terminology relevant to tables. After that, we’ll jump into a discussion of the basic heap-organized table and then move on to examine the other types.

Terminology

In this section, we will cover the various storage parameters and terminology associated with tables. Not all parameters are used for every table type. For example, the PCTUSED parameter is not meaningful in the context of an IOT (the reason for this will become obvious in Chapter 11). We’ll cover the relevant parameters as part of the discussion

of each individual table type. The goal is to introduce the terms and define them. As appropriate, more information on using specific parameters is covered in subsequent sections.