Classification of Oracle tables and detailed explanation of related parameters _oracle

There are several types of tables in Oracle:
1, the heap organization table (heap organized tables):Commonly used table types, managed in heaps, use the first free space in the segment to fit the data size when data is added, and when data is deleted, the left space allows subsequent DML operations to be reused.
2, Index organization table:Tables are stored in the index structure, using the row itself to sort the storage. In the heap, the data may be filled in any suitable place, and in the Index organization table, the data is stored in a sort order based on the primary key.
3, Clustered table:Several tables are physically stored in a piece, usually on the same block of data. All data that contains the same cluster code value is physically stored together, and the data is "clustered" around the cluster code, and the cluster code is constructed with a B*tree index.
4, hash cluster table:Similar to the clustered table, but not with the B*tree index clustering code location data, hashing clustering code scattered into the cluster, to reach the data in the database block. In a hash cluster, the data is the index (metaphorically speaking). This is suitable for data that is often read through the code equation.
5. Nested table:A part of the OOP extension that is generated by the system and maintains the child table in a parent-child relationship.
6. Temporary table:Stores temporary data in a transaction or session to compute intermediate results, allocating temporary segments as storage areas.
7, Object table:Created from an object type, has special properties, and is not associated with a non-object table.
A table has a maximum of 1000 columns; Theoretically a table has infinitely many rows; it can have as many indexes as a column, and can use 32 at a time; there is no limit to the number of owning tables.
table-related parameters and terminology
1) High water mark
The high water mark starts on the first block of the newly created table. As the data continues to be placed in the table, more blocks are used, thereby raising the high water mark. If you delete rows from some tables, the high water mark still does not move. That is, COUNT (*) 100000 lines and delete all rows after count (*) takes the same amount of time (full scan). The table needs to be rebuilt.
2 Free list (freelist)
A block object used in Oracle to track the free space below a high water mark. Each object has at least one freelist associated with it. When a block is used, Oracle will place or take freelist as needed. Only one object below the high water mark can be found on the freelist. Blocks that remain above the high water mark can only be used if the freelist is empty. Oracle then raises the high water mark and adds the blocks to the freelist. In this way, Oracle defers raising the high water mark on an object until it has to be raised. An object may not have only one freelist, and if many concurrent users are expected to make a large number of inserts or updates on an object, configuring multiple freelist can improve overall performance (possibly at the cost of increasing storage space).
3) Pctfree and pctused
If the Pctfree is set to 10, the block will use Freelist (all in freelist) before the end of the 90%. Once 90% is reached, it is removed from the freelist until the free space on the block is more than 60% (again into the freelist, when Pctuser is 40 o'clock).
A. When the pctfree is set too small and often updated, row migration is easy to occur
B. High Pctfree, low pctused---used to insert many of the data to be updated, and updates often increase the size of rows, so that after inserting a lot of space on the block (high Pctfree), before the block returns to the free list, the block must be almost empty (low pctused)
C. Low pctfree, high pctused---are used to tend to use inserts or deletes only for tables, or if you want to update,update only make rows smaller.
4) Initial,next and Pctincrease
For example, using a initial panel for the 1mb,next panel for a 2mb,pctincrease of 50, the available panel should be: (1) 1MB (2) 2MB (2) 3MB (2) (4) 4.5MB (3 of 150%). These parameters are generally considered obsolete, and the database should use local management and a table space of the same size as the disk area. In this way, the initial disk area is always equal to the size of the next disk, and no pctincrease is used, and using pctincrease only results in fragmentation of the table space. In the case of no local management of the table space, it is recommended to always set Initial=next and pctincrease equal to zero, so that you can simulate the use of local management table space, in order to avoid fragmentation, all objects in the table space should use the same disk allocation policy.
5) Minextents and maxextents
Sets the number of disk extents that the control object allocates to itself. The number of minextents is the initial allocation disk area.
6) Logging and nologging
Nologging allows certain operations to be performed on these objects without a redo. It affects only certain actions, such as starting the object or using Sqlldr for direct path loading or insert/*+ Append/Select Type statements.
7) Initrans and Maxtrans
Each block in the object has a size that is part of the transaction table, and the entries in the Transaction table describe which row/element is locked on the transaction block. The initial size of the transaction table is determined by the Initrans setting of the object, and for the table, the default is 1 (index defaults to 2), and when needed, the transaction table can be dynamically increased to a maximum size of maxtrans (assuming there is enough free space on the fast). Each allocated transaction entry occupies 23 bytes of storage space on the size of the header.