In-depth understanding of Oracle table (7): analysis of data storage and application scenarios of 10 Table types

In-depth understanding of Oracle table (7): 10 types of data storage and application scenarios analysis in-depth understanding of Oracle Table (6): heap organization table (HOT) and index organization table (IOT) difference http://www.bkjia.com/database/201305/208097.html (I) Heap organization table (1) If a partition is stored as a box, a table is a group of unordered boxes together as long as there is available space, when the data can be scanned in the whole table anywhere, the data will be retrieved in the hit order, rather than the insertion sequence (2) in applicable scenarios, 99% of the data may be used when the create table statement is executed in the heap organization table, the default table type is that the heap organization table stores the table quickly, the following parameters are applicable only to ASSM ① PCTFREE. If the PCTFREE parameter is too small, this will also cause no free space left for the update operation. ② INITRANS indicates the number of transaction slots allocated for block initialization. The default value is 2, this is also the minimum value. Each concurrent transaction requires a transaction slot. If you believe that multiple concurrent operations will be performed on the same block, you should consider increasing this value, but now this parameter can be automatically adjusted, unless there is a related wait, do not need to pay too much attention (ii) index organization table (1) Data Storage Index the data in the organization Table is stored in the Index segment, instead of the Table segment, that is, the Index and data are in the same place, data is stored in an orderly manner (2) there are many factors to consider when to use IOT in applicable scenarios. Even Tom says a lot and doesn't understand it. however, it is clear when IOT is not needed: if you do not use a primary key to access a table in most cases, do not use the IOT (3) related parameter ① OVERFLOW. This option allows you to create another segment (which makes IOT a multi-segment object). If the row data of IOT becomes too large, it can overflow into this segment ② PCTTHRESHOLD if PCTTHREHOLD is 10%, and the block size is 8 K, A part of the rows with a length greater than 800 bytes will be placed in the overflow segment ③ INCLUDING rows from the first column until the column specified by the INCLUDING clause (this column is also included) all columns are stored on the index block, and the remaining columns are stored in the OVERFLOW segment. ④ PCTFREE and PCTUSED are of great significance for IOT when considering the OVERFLOW segment. They are treated as OVERFLOW when dealing with heap tables. Segment (iii) index clustering table (1) Data Storage contains two layers meaning: The related fields of multiple tables are stored in the same block. Multiple tables use one index together. (2) applicable scenarios if data is mainly used for reading, it also needs to be read through the index, and the information will be frequently linked together. At this time, the index clustering table is suitable. (3) The relevant parameter ① SIZE controls how many clustering keys are stored on each block. (4) hash clustering table (1) the data is stored in the same way as the index clustering, however, in terms of indexes, the hash cluster uses the hash function to directly locate the physical address of the data. (2) applicable scenarios are suitable for tables with an estimated size, in addition, most queries use HASH columns to perform equivalent queries. Full table scanning is inefficient and, HASH clustering is also not suitable for a large number of DML operations. (3) related parameter ① HASHKEYS specifies the SIZE of the hash table. The HASH cluster should be pre-allocated enough space for storage (HASHKEYS/trunc (blocksize/SIZE )) for example, if the SIZE is set to 1500 bytes and the block SIZE is 4 kb, Oracle stores data in each block. Store 2 keys. If you plan to have 1000 hashkeys, oracle will allocate 500 blocks (v) ordered hash clustering tables (1) data storage data is stored on the same hash clustering. The only difference is that, data can be stored in an orderly manner according to a field you choose (2) applicable scenarios such as: select * from t where KEY =: x (for example, cust_id =: x) order by sorted_column that is to say, to obtain data by a key, but the data is required to be sorted by another column (3) related parameter ① SORT for example, we specify: create table t (order_dt timestamp SORT) data is physically obtained and sorted by order_dt. (6) nested tables (1) Data Storage hidden columns add raw (16) Storage overhead the parent table creates a unique index nested table model by default, each parent row has a virtual table. In the parent/child table (relational model), each parent row has a row set (2 ). Path: ① used to expand PL/SQL languages, which is a common usage ② as a physical storage mechanism (almost useless !) If you use nested tables as a storage mechanism, you must ensure that the nested tables are built into an IOT platform to avoid the overhead of indexes on nested_table_id and the overhead of nested tables. If IOT is not used, make sure that an index is created on the nested_table_id column to avoid scanning the entire table in the industrial environment for searching the sub-rows, you can use the parent-child table + view solution to replace the (vii) temporary table (1) Data Storage temporary table data is placed in the PGA. If the data inserted into the temporary table is large, if the size of PGA is exceeded, the data will be stored in the temporary tablespace (2). For the applicable scenarios, see the article written before Think: application Problems of temporary tables in Oracle temporary tables External tables (1) data is stored on the OS layer, metadata is defined in the data dictionary. (2) applicable scenarios: a large number of data flat files are queried, not updated, and text is used. logs. query. statistics. analysis of relational object table (1) Data Storage object table is actually a disguised relational table, which is always converted to the original relational row and column pseudo primary key with raw (16) storage overhead creates an additional index (2) Application Scenario ① storage: industrial environment, we generally use views of Relational Tables to replace object tables as a storage mechanism ② encapsulation: object Table reference is to make data more encapsulated. ③ development: PL/SQL is extended to facilitate development of Partitioned Tables. (1) data is stored in one partitioned table and multiple partitions are matched, each partition is a Segment. These segments can be created on the same Tablespace, or a partition corresponds to a Tablespace (2). Use Case ①: partition, divide and conquer, the focus is on high availability (management), while ancillary value is the improvement of performance ② When querying: Specify access to a Segment during the query, So that I/O access is reduced from physical files, so as to optimize the query of ③ data: in my opinion, the benefit of the partition table is best reflected in the process of historical data.