Oracle Database Development

Oracle Database Development

In the Oracle database system, tables are the basic objects of databases, and all data in the database exists as tables.
 
Environment-Oracle 11g

I. Table type
Common tables in Oracle Database systems include: heap organization table, index organization table, and cluster Table (index, hash, and ordered hash), nested tables, temporary tables, external tables, partition tables, object tables, etc.
 
1. Heap organization table
In the Oracle system, a standard database table is a heap organization table, where data is managed in heap mode.
Heap meaning: a set of spaces for storing data in a random manner.
For a heap organization table, when writing data, the database uses the first free space in the segment.
When data is deleted, the system allows future insert and update operations to reuse this space.

2. index the Organizational table
Index organized table (ITO) is a table that stores databases according to the index structure. This improves query performance.
Unlike the heap organization table, the index organization table requires that the data row itself has a certain physical order, and the data can only be stored in an orderly manner according to the primary key. In this way, when the query operation is performed in the index organization table,
Using primary key columns will provide better read performance.
 
3. Clustering table
Clustering refers to a group composed of one or more tables. These tables are physically stored on the same data block, and all rows with the same cluster key value are physically stored adjacent to each other.
Clustering tables can be used to store data related to multiple tables in one data block. In addition, all data with the same clustering key value can be pre-stored, if multiple tables are frequently used
For link operations, clustering tables can greatly improve the search performance.
Oracle 11g provides three clustering tables:
A. Index Cluster Table: a Cluster Table created based on the B-Tree Index Cluster key. The cluster key points to an oracle block instead of a row of data.
B. Hash Cluster Table: Use the Hash function to replace the clustered key index of the B-Tree index. The data in the Table is the index.
In a hash clustering table, hash calculation is performed using internal functions or custom functions, and the calculated code value is used to locate data rows in the table.
If you frequently use query clauses with the same conditions to access a table, it is appropriate to use a hash clustering.
C. sorted Hash Cluster Table: This type of Hash Cluster Table is increased by 10 Gb in Oracle. It not only has the properties of Hash clustering, it also integrates some features of IOT.
If you want to obtain data by a key but require that the data be sorted by another column, it is suitable to use an ordered hash clustering table. By using an ordered hashed clustering table,
Oracle can query data without sorting, because the data in the data is stored physically according to the ordered key.
 
4. nested tables
Nested tables are the tables in the table and part of the Oracle object relationship extension. They are child tables in the parent/child relationship generated and maintained by the system.
Differences between a nested table and a sub-table: A sub-table is an independent table, but a nested table is not. A nested table is a set of rows that are represented as one of the columns in the parent table. For each record in the parent table
A nested table can contain multiple rows.
The advantage of nested tables is that they can be indexed and do not need to be connected. The disadvantage is that, despite the cascading deletion form (all slave rows will be deleted when the primary row is deleted ), but it is impossible to reference integrity constraints.
 
5. Temporary table
The temporary table stores temporary data during the transaction processing or session. When the transaction processing is completed or the session ends, the data in the temporary table is deleted. Once a temporary table is created
And the system allocates storage segments from the temporary tablespace of the current user only when data is inserted into the table.
Temporary tables include session-level temporary tables and transaction-level temporary tables.
The data in a session-level temporary table is not automatically deleted before the session is disconnected, and is automatically deleted after the session ends.
The transaction-level temporary table is automatically deleted when the transaction is committed.
 
6. External table
This is the table type added since Oracle 9i. The data in the External table is not stored in the database itself, but in the file system outside the database.
You can use an external table to query files outside the database, load data to the database, and write data. The External table is further improved in Oracle 10 GB and
The unmount function provides a simple way to move data between databases without using database links.
 
7. partitioned table
Partitioning refers to dividing a very large table into several independent small components for storage and management. After the table is partitioned, the records in the table are stored discretely according to the partition conditions.
To different partitions, you can operate the entire table or specific partitions. When the table size exceeds 2 GB, the partition table should be used. In addition, if the table size exceeds 2 GB
Contains historical data, the new data is added to the new partition.
 
8. Object table
An object table is a table created based on the object type, rather than a set of columns. Each row in the Object table is an Object. Each Object has an Identifier (Object Identifier OID)
. There is no primary/foreign key association between object tables. To reflect this relationship, Oracle uses a REF object. Object tables can simplify the use of objects.
Is a convenient way to create a table using the object type as a template. It ensures that multiple tables have the same structure.
 

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Ii. Table features
The features of a table determine how to create a table, how to store the table on the disk, and how to finally execute the application after the table is generated and available.
(1) TABLESPACE clause
By default, the table created by the USER is located in the USER or the default database tablespace (USER tablespace. A single user can specify the tablespace used for table creation when creating a table.
 
(2) STORAGE clause
Most STORAGE clauses are used to set the STORAGE attributes of a tablespace when creating a tablespace. By default, database objects created in the tablespace inherit the storage attributes of the tablespace.
You can use the STORAGE clause to set the STORAGE attributes of a table when creating a table.
Example: create table t_name (...) STORAGE (INITIAL 200 k next 200 k pctincrease 20 MAXEXTENTS 15 );
 
INITIAL: Specifies the size of the first partition allocated to the object.
 
NEXT: indicates the size of the NEXT partition allocated to the object.
 
PCTINCREASE: In the tablespace managed locally, the Oracle database uses the value of this parameter to determine the size of the segment when creating the segment, and the value of this parameter will be ignored in subsequent space allocation.
In the dictionary management tablespace, the value of this parameter indicates the percentage of growth of the third and subsequent partitions over the previous partition.
 
MINEXTENTS: manages the tablespace locally. It determines the size of the Segment (Segment) together with the preceding three parameters.
In the dictionary management tablespace, the value indicates the total number of partitions allocated when the object is created.
 
MAXEXTENTS: this parameter is only used to manage tablespaces in a dictionary. It indicates the total number of partitions (Extent) that Oracle can allocate to objects.
 
(3) LOGGING and NOTLOGGING
Use the LOGGING and NOTLOGGING clauses to specify whether the table is a log record table.
LOGGING clause. The system records all database changes in the database. If a fault occurs, you can obtain these changes from the redo log to prevent data loss and improve database reliability.
 
The NOTLOGGING clause does not record some operation logs on the table, so that only a few logs are generated. Note that the NOTLOGGING clause is not used, and all operations on the table are performed.
No redo logs are generated.
Generally, the NOTLOGGING clause only applies to the following operations: create table as select;
SQL * loader direct path loading;
Direct path insertion;

(4) CACHE and NOCACHE
The CACHE clause is used to read the database into the buffer when you perform a full table search on the table and place it at the most recently used end.
The NOCACHE clause indicates that the database read into the buffer zone is placed at the least recently used end of the list of least recently used.
By default, the created tables are all NOCACHE. For tables that frequently perform full table search, you can set it to CACHE.
 

3. Basic table creation syntax
Create table [schema.] table_name
(
Column_name datatye [default expr] [constraint_name] constraint_def -- column 1
, Column_name datatye [default expr] [constraint_name] constraint_def -- column 2
...
, Column_name datatye [default expr] [constraint_name] constraint_def -- column n
)
[PCTFREE n]
[PCTUSED n]
[INITRANS]
[STORAGE storage]
[TABLESPACE table_space]
[As sub_querry]
;
Note:
Percentage of space used by PCTFREE (0 ~ 99). 0 indicates that the plug-in completely fills up the data block. The default value is 10;
PCTUSED is the minimum percentage of available space reserved by each database in the table (1 ~ 99). The default value is 40;
The combination of PCTFREE and PCTUSED determines whether the inserted data is put into a new data block.
 
INITRANS indicates the number of initial concurrent transaction items allocated to each data block of the database object. The value ranges from 1 ~ 255. The default value is 1;

For more details, please continue to read the highlights on the next page:

1 2 3 4 5 6 7 8 9 10 Next Page

[Content navigation]
Page 1st: Table (concept)	Page 2nd: Table (data integrity constraints)
Page 1: Sequence	Page 1: synonyms
Page 5th: temporary table	Page 1: learn more about rowid
Page 1: partition tables	Page 8th: about hierarchical query connect
Page 1: Learn about Indexes	Page 1: In-depth Index Structure