January 06, 2010

Maximum in Oracle:

Maximum in Oracle:

Database file size Maximum Operating system dependent. Limited by maximum
operating system file size; typically 222 or 4 MB blocks


Bigfile Tablespaces Number of blocks 232 (4 GB) blocks
Smallfile (traditional)
Tablespaces
Number of blocks 222 (4 MB) blocks


GROUP BY clause Maximum length The GROUP BY expression and all of the
nondistinct aggregate functions (for example, SUM,
AVG) must fit within a single database block.

Indexes Maximum per table Unlimited
total size of indexed column 75% of the database block size minus some
overhead

Columns Per table 1000 columns maximum
Restrict the number of columns less than 255 and the performance will be good.

Per index (or clustered index) 32 columns maximum
Per bitmapped index 30 columns maximum
Constraints Maximum per column Unlimited
Subqueries Maximum levels of subqueries
in a SQL statement
Unlimited in the FROM clause of the top-level
query
255 subqueries in the WHERE clause
Partitions Maximum length of linear
partitioning key
4 KB - overhead
Maximum number of columns
in partition key
16 columns
Maximum number of partitions
allowed per table or index
64 KB - 1 partitions
Rows Maximum number per table Unlimited

Tables Maximum per clustered table 32 tables
Maximum per database Unlimited

4 comments:

Anonymous said...

Keep posting stuff like this i really like it

Anonymous said...

You have really great taste on catch article titles, even when you are not interested in this topic you push to read it

Anurag Vidyarthi said...

I guess Maximum number of partitions
allowed per table or index are
1024KB - 1 partitions...

Avishkar Meshram said...

Yes, Anurag Vidyarthi is correct.
Maximum number of partitions
allowed per table or index are
1024KB - 1 partitions...

Maximum number of partitions
allowed per table or index
64 KB - 1 partitions is applicable for Oracle 9i rel2 and Oracle 10g rel1 only.
i.e.

Oracle 9i rel2 --> 64 KB - 1 partitions per table
Oracle 10g rel1 --> 64 KB - 1 partitions per table
Oracle 10g rel2 --> 1024 KB - 1 partitions per table
Oracle 11g rel1 --> 1024 KB - 1 partitions per table


Thanks Anurag!!