Oracle Concept - 读书笔记

1. Transactions are one of the features that sets Oracle Database apart from a file system. If you perform an atomic operation that updates several files, and if the system fails halfway through, then the files will not be consistent. In contrast, a transaction moves an Oracle database from one consistent state to another. The basic principle of a transation is "all or nothing": an atomic operation succeeds or fails as a whole. 

2. heap-organized table & index-organized table & external table

• heap-organized table does not store rows in any particular order. The create table statement creates a heap-organized table by default. By default, a table is organized as a heap, which means that the database places rows where they fit best rather than in a user-specified order. Thus, a heap-organized table is an unordered collection of rows. As users add rows, the database places the rows in the first available free space in the data segment. Rows are not guaranteed to be retrieved in the order in which they were inserted.
• index-organized table（IOT） orders rows according to the primary key values. For some app, index-organized tables enhance performance and use disk space more efficiently.
• external table is a read-only table whose metadata is stored in the database but whose data is stored outside the database.

3. A table cluster is a group of tables that share common columns and store related data in the same blocks. When tables are clustered, a single data block can contain rows from multiple tables.

• The cluster key is the column or columns that the clustered tables have in common.
• The cluster key value is the value of the cluster key columns for a particular set of rows. All data that contains the same cluster key value, such as department_id=20, is physically stored together. Each cluster key value is stored only once in the cluster and the cluster index, no matter how many rows of different tables contain the value.
• You can consider clustering tables when they are primarily queried (but not modified) and records from the tables are frequently queried together or joined. Because table clusters store related rows of different tables in the same data blocks, properly used table clusters offer the following benefits over nonclustered tables:
  • Disk I/O is reduced for joins of clustered tables.
  • Access time improves for joins of clustered tables.
  • Less storage is required to store related table and index data because the cluster key value is not stored repeatedly for each row.
• Typically, clustering tables is not appropriate in the following situations:
  • The tables are frequently updated.
  • The tables frequently require a full table scan.
  • The tables require truncating.